~SingleForum~无废话记录~ Topic: 存储过程返回结果集 http://www.ll19.com/bb/ ~19~记录点有用的好吗？少说废话少装b~ en Tue, 07 Feb 2012 08:58:26 +0000 admin on "存储过程返回结果集" http://www.ll19.com/bb/topic/93#post-93 Sun, 03 Jan 2010 15:50:08 +0000 admin 93@http://www.ll19.com/bb/ <p>首先要明确一个概念<br /> PROCEDURE TRIGGER PACKAGE 的区别<br /> 简单的说，PROCEDURE, TRIGGER都是一段程序<br /> 但PROCEDURE需要手工调用，TRIGGER满足你设定的条件会自动执行<br /> PACKAGE是 把几个程序封装在一起，和JAVA里的PACKAGE的概念差不多。</p> <p>得到返回的结果集需要建立PACKAGE，并且返回列表需要通过返回游标实现</p> <p>由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分。</p> <p>1、在SQL*PLUS中建一个程序包（在包中定义了一个类型游标）</p> <p>CREATE OR REPLACE PACKAGE TESTPACKAGE AS<br /> TYPE Test_CURSOR IS REF CURSOR;<br /> procedure TESTC(cur_ref out Test_CURSOR);<br /> end TESTPACKAGE;</p> <p>2、建立包之后建立存储过程 </p> <p>create or replace package body TESTPACKAGE as<br /> procedure TESTC(cur_ref out Test_CURSOR) is<br /> begin<br /> OPEN cur_ref FOR select * from test;<br /> end TESTC;<br /> END TESTPACKAGE;</p> <p>可以看到，它是把游标(cursor)（可以理解为一个指针），作为一个out 参数来返回值的。</p> <p>JAVA调用程序如下：</p> <p>
PLAIN TEXT
JAVA:
  1. &#60;br /&#62;
  2. package com.rizon.tree.excelApi;&#60;/p&#62;
  3. &#60;p&#62;import java.sql.Connection;&#60;br /&#62;
  4. import java.sql.DriverManager;&#60;br /&#62;
  5. import java.sql.ResultSet;&#60;br /&#62;
  6. import java.sql.SQLException;&#60;/p&#62;
  7. &#60;p&#62;public class CursorTest {&#60;/p&#62;
  8. &#60;p&#62; public static void main(String[] args) throws ClassNotFoundException,&#60;br /&#62;
  9.             SQLException {&#60;br /&#62;
  10.         Connection conn = null;&#60;br /&#62;
  11.         ResultSet rs = null;&#60;br /&#62;
  12.         try {&#60;/p&#62;
  13. &#60;p&#62;   Class.forName(&#34;oracle.jdbc.driver.OracleDriver&#34;);&#60;br /&#62;
  14.             conn = DriverManager.getConnection(&#60;br /&#62;
  15.                     &#34;jdbc:oracle:thin:@10.0.41.100:1521:orcl&#34;, &#34;forestrytree&#34;,&#60;br /&#62;
  16.                     &#34;forestrytree&#34;);&#60;/p&#62;
  17. &#60;p&#62;   oracle.jdbc.OracleCallableStatement stmt = (oracle.jdbc.OracleCallableStatement) conn&#60;br /&#62;
  18.                     .prepareCall(&#34;{call TESTPACKAGE.TESTC(?)}&#34;);&#60;br /&#62;
  19.             // stmt.setString(1, &#34;1&#34;);&#60;br /&#62;
  20.             stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);&#60;br /&#62;
  21.             stmt.execute();&#60;br /&#62;
  22.             rs = stmt.getCursor(1);&#60;br /&#62;
  23.             for (int i = 0; rs.next();) {&#60;br /&#62;
  24.                 System.out.println(&#34; ----[&#34; + i + &#34;]name:&#34; + rs.getString(1)&#60;br /&#62;
  25.                         + &#34;telephone:&#34; + rs.getString(2));&#60;br /&#62;
  26.             }&#60;br /&#62;
  27.         } catch (Exception ex) {&#60;br /&#62;
  28.             ex.printStackTrace();&#60;br /&#62;
  29.         } finally {&#60;/p&#62;
  30. &#60;p&#62;   if (conn != null) {&#60;br /&#62;
  31.                 conn.close();&#60;br /&#62;
  32.                 conn = null;&#60;br /&#62;
  33.             }&#60;/p&#62;
  34. &#60;p&#62;   if (rs != null) {&#60;br /&#62;
  35.                 rs.close();&#60;br /&#62;
  36.                 rs = null;&#60;br /&#62;
  37.             }&#60;br /&#62;
  38.         }&#60;/p&#62;
  39. &#60;p&#62; }&#60;/p&#62;
  40. &#60;p&#62;}&#60;br /&#62;

</p>