[转载]Oracle外部程序的触发小结
<p>信息来源: it168</p><p>在论坛里曾经看过一个帖子，有位用户问了这么一个问题：oracle 里一个表插入一条数据，提交后，如何让外部程序收到这个消息？ </p><p /><p /><div id="3"> <font face="Verdana">当时没有留意，现在想想好像应该可以总结一下了。举例：</font></div><p /><p /><div id="4"><font face="Verdana"> 1、过程中：<br /></font></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5"><div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">declare</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> myexcept exception;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> inserted_count1 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">number</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> inserted_count2 </span><span style="FONT-WEIGHT: bold; COLOR: #000000">number</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">begin</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">into</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">values</span><span style="COLOR: #000000">(...);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> inserted_count1 :</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"> sql</span><span style="COLOR: #808080">%</span><span style="COLOR: #0000ff">rowcount</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ... </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">可以判断inserted_count1是否大于0来确定是否插入了数据</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">commit</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> inserted_count2 :</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"> sql</span><span style="COLOR: #808080">%</span><span style="COLOR: #0000ff">rowcount</span><span style="COLOR: #000000">; </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">这里inserted_count2为0</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> ... </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">事实上，这里一定是提交成功才会执行，否则会跳到exception段</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> yourprocess(yourpara);</span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">调用你自己的过程或语句跟别的过程通信</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> exception
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">when</span><span style="COLOR: #000000"> others </span><span style="COLOR: #0000ff">then</span><span style="COLOR: #000000"></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">-你可以根据sqlcode自己去查具体的提交失败原因</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> yourprocess_for_fail(yourpara) </span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">调用你自己的过程跟别的过程通信</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000"> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">end</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><div id="6"></div><p /><p /><div id="7"> <font face="Verdana">2、直接外部操作，建触发器：</font></div><p /><div id="8"><div id="14"><font face="Verdana"></font></div></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="9">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">trigger</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">table</span><span style="COLOR: #000000"> after </span><span style="COLOR: #0000ff">insert</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">for</span><span style="COLOR: #000000"> each row
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">begin</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> yourprocess(yourprara);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> ...
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">end</span><span style="COLOR: #000000">;</span></div>
</div></pre></div><div id="6"></div><p /><div id="11"><font face="Verdana"> 当然，2的方法这只能更总到提交前，如果要监视提交后状态，你可以在操作表建立标志位或专门建个表，操作表发生变化就通过trigger更新变化信息到状态表，扫描状态表就知道是否commit;成功了</font></div><p /><p /><div id="12"> <font face="Verdana">我想这位用户应该通过trigger来调用java source来实现插入数据后来通知外部程序来做一些事情：</font></div><p /><p /><div id="13"> <font face="Verdana">使用java source的例子如下：</font></div><p /><div id="8"><div id="14"><font face="Verdana"></font></div></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="15">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">and</span><span style="COLOR: #000000"> compile java source named HelloWorld
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">public</span><span style="COLOR: #000000"> class HelloWorld {
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">4</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">public</span><span style="COLOR: #000000"> static void db_run (){
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">5</span><span style="COLOR: #000000"> System.out.println("Hello World");
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">6</span><span style="COLOR: #000000"> }
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">7</span><span style="COLOR: #000000"> }
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">8</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Java created.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">create</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">or</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">replace</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">procedure</span><span style="COLOR: #000000"> run_helloworld
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">as</span><span style="COLOR: #000000"> language java
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">3</span><span style="COLOR: #000000"> name </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">HelloWorld.db_run()</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="FONT-WEIGHT: bold; COLOR: #800000">4</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">Procedure</span><span style="COLOR: #000000"> created.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> serveroutput </span><span style="COLOR: #0000ff">on</span><span style="COLOR: #000000"> size </span><span style="FONT-WEIGHT: bold; COLOR: #800000">5000</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"> call dbms_java.set_output(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">5000</span><span style="COLOR: #000000">) ;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Call completed.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">exec</span><span style="COLOR: #000000"> run_helloworld ;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> Hello World
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> PL</span><span style="COLOR: #808080">/</span><span style="COLOR: #000000">SQL </span><span style="COLOR: #0000ff">procedure</span><span style="COLOR: #000000"> successfully completed.
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="16"> 上面是调用HelloWorld的例子，你可以修改HelloWorld方法，然后在触发器里面调用以实现这位用户的要求！</div><p /><div id="6"></div>
页:
[1]