oracle调用存储过程和函数返回结果集

浅浅的花香味﹌ 2022-04-02 10:46 408阅读 0赞

# JAVA调用ORACLE的存储过程、函数的返回结果集 #

2017年05月11日 21:03:18 [彖爻之辞][Link 1] 阅读数：1091 标签： [oracle][][存储][Link 2] 更多

个人分类： [\--------1.1 ORACLE][--------1.1 ORACLE]

具体过程如下：
    
    CREATE TABLE STOCK_PRICES(
        RIC VARCHAR(6) PRIMARY KEY,
        PRICE NUMBER(7,2),
        UPDATED DATE );
    
    --对表插入数据
    INSERT INTO stock_prices SELECT '1110',1.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1111',2.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1112',3.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1113',4.0,SYSDATE FROM DUAL;
    
    --建立一个返回游标
    CREATE OR REPLACE PACKAGE ZZH_TEST.PKG_PUB_UTILS IS
        --动态游标
        TYPE REFCURSOR IS REF CURSOR;
    END PKG_PUB_UTILS;
    
    --创建一个测试用的存储过程
    CREATE OR REPLACE PROCEDURE ZZH_TEST.P_GET_PRICE(AN_O_RET_CODE         OUT NUMBER,
                                                     AC_O_RET_MSG          OUT VARCHAR2,
                                                     CUR_RET               OUT PKG_PUB_UTILS.REFCURSOR,
                                                     AN_I_PRICE IN NUMBER
                                                     ) IS
    BEGIN
        AN_O_RET_CODE := 0;
        AC_O_RET_MSG  := '操作成功';
    
        OPEN CUR_RET FOR
            SELECT * FROM STOCK_PRICES WHERE PRICE<AN_I_PRICE;
    EXCEPTION
        WHEN OTHERS THEN
            AN_O_RET_CODE := -1;
            AC_O_RET_MSG  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
    
    END P_GET_PRICE;
    
    --创建一个测试用的函数:
    CREATE OR REPLACE FUNCTION ZZH_TEST.F_GET_PRICE(v_price IN NUMBER)
        RETURN PKG_PUB_UTILS.REFCURSOR
    AS
        stock_cursor PKG_PUB_UTILS.REFCURSOR;
    BEGIN
        OPEN stock_cursor FOR
        SELECT ric,price,updated FROM stock_prices WHERE price < v_price;
    
        RETURN stock_cursor;
    END;
    
    --开发JAVA调用存储过程返回结果集的例子：JDBCoracle10G_INVOKEPROCEDURE 
    --开发JAVA调用函数返回结果集的例子：JDBCoracle10G_INVOKEFUNCTION
    
    --2份JAVA源代码:
    
    -- 开发JAVA调用存储过程返回结果集的例子：JDBCoracle10G_INVOKEPROCEDURE
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /* 本例是通过调用oracle的存储过程来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成：classes12.zip
      */
     public class JDBCoracle10G_INVOKEPROCEDURE {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEPROCEDURE()
      {   
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid：必须为要连接机器的sid名称，否则会包以下错：
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
    
      }
    
      public void init()
      {
    
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
             // conn.setAutoCommit(false);
    
          //输入参数
           in_price="3.0";
    
           //调用函数
           stmt = conn.prepareCall( "call P_GET_PRICE(?,?,?,?)");
           stmt.registerOutParameter(1, java.sql.Types.FLOAT);
           stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(4, in_price);
           stmt.executeUpdate();
           int retCode = stmt.getInt(1);
           String retMsg = stmt.getString(2);
           if (retCode == -1) { //如果出错时，返回错误信息
           System.out.println("报错！");
           } else {
           //取的结果集的方式一：
               rs = ((OracleCallableStatement)stmt).getCursor(3);
            //取的结果集的方式二：
                 //  rs = (ResultSet) stmt.getObject(3);
    
            String ric ;
         String price ;
         String updated ;
            //对结果进行输出
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);      
    
          System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
           }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换［］
      {
       new JDBCoracle10G_INVOKEPROCEDURE();
      }
      }
    
    --=======================================================================
    
    --源代码：
    
    --开发JAVA调用函数返回结果集的例子：JDBCoracle10G_INVOKEFUNCTION
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /*
     /* 本例是通过调用oracle的函数来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成：classes12.zip  
      */
     public class JDBCoracle10G_INVOKEFUNCTION {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEFUNCTION()
      {
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid：必须为要连接机器的sid名称，否则会包以下错：
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
      }
    
      public void init()
      {
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
           //conn.setAutoCommit(false);
    
          //输入参数      
           in_price="5.0";
    
           //调用函数
           stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
          // stmt.registerOutParameter(1, java.sql.Types.FLOAT);
          // stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(2, in_price);
           stmt.executeUpdate();
           //取的结果集的方式一：
                rs = ((OracleCallableStatement)stmt).getCursor(1);
           //取的结果集的方式二：
              //  rs = (ResultSet) stmt.getObject(1);
             String ric ;
         String price ;
         String updated ;
    
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);                 
             System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换［］
      {
       new JDBCoracle10G_INVOKEFUNCTION();
      }
      }

[https://blog.csdn.net/paul50060049/article/details/71698108][https_blog.csdn.net_paul50060049_article_details_71698108]

# oracle调用存储过程和函数返回结果集 #

2010年10月21日 13:56:00 [whaosy][] 阅读数：25787

在程序开发中，常用到返回结果集的存储过程，这个在*mysql*和*sql server *里比较好处理，直接返回查询结果就可以了，  
但在*oracle*里面 要* out *出去，就多了一个步骤，对于不熟悉的兄弟们还得出上一头汗：），这里我简单介绍一下，以供参考，  
*   1  *定义包  
*      oracle *返回的结果集需要自定义一个* CURSOR *（游标变量）性质的变量，这个要在包头定义，所以要建立一个包，如下是包头

***Pl/sql*****代码**

*CREATE OR REPLACE PACKAGE PAK\_rstest*

*  IS*

*    TYPE   retcursor    IS   REF   CURSOR;*

*   PROCEDURE pro\_read*

*     (*

*       outcurse  IN OUT retcursor*

*     );*

*   END; -- Package spec*

上面是建立了一个名称为*PAK\_rstest*的包头，里面定义了一个*CURSOR *类型，类型名为*retcursor *，有了这个定义我们就可以用他来返回结果集了，比如该包里面的* pro\_read *过程就是 一个返回结果集的过程，下面是他的包体，

***Pl/sql*****代码**

*CREATE OR REPLACE PACKAGE BODY PAK\_rstest IS*

*   PROCEDURE pro\_read*

*   (*

*       outcurse  IN OUT retcursor*

*   )*

*   IS*

*   begin*

*        OPEN outcurse FOR*

*         select \* from tbl\_test*

*                where rownum<6;*

*        return;*

*   end;*

*END;*

这样就定义好了一个包，这个包里面有个返回结果集的过程* pro\_read  
  *

* 2 *在程序里面调用，  
     下面就是如果在程序里面调用了，这里用*java*为例子简单介绍一下，  
     假设你现在已经有一个*Connection  conn *对象连接上了数据库*(*如何连接数据库我这里就不详细说了*),   
     *则用下面的代码调用过程，

***Pl/sql*****代码**

*if(conn !=null)\{*

*    String sqlstr = "\{call  PAK\_SMS2\_ROUTE.MO\_ISSUE(?)\}";*

*     CallableStatement cstmt = conn.prepareCall(sqlstr);*

*     cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //outcurse*

*     cstmt.executeUpdate();*

*     ResultSet rs = (ResultSet) cstmt.getObject(1); // *这里吧信息已经读入*rs*结果集里面，剩下的大家都熟悉了吧

*   while (rs.next()) \{*

*        System.out.println(rs.getString("s\_date1")); //tbl\_test *表里的字段名称或是结果集的列名称

*     System.out.println(rs.getString("s\_date2"));*

*      \}   *

*         conn.close();*

*         conn = null;       *

*\}*

好了到这里就可以看到返回的结果集内容了，是不是比较简单啊，：）  
  
*   Oracle *存储过程返回结果集*:*

**过程返回记录集代码**

*CREATE OR REPLACE PACKAGE pkg\_test*

*AS*

*    TYPE myrctype IS REF CURSOR;*

*    PROCEDURE get (p\_id NUMBER, p\_rc OUT myrctype);*

*END pkg\_test;*

*CREATE OR REPLACE PACKAGE BODY pkg\_test*

*AS*

*    PROCEDURE get (p\_id NUMBER, p\_rc OUT myrctype)*

*    IS*

*       sqlstr   VARCHAR2 (500);*

*    BEGIN*

*       IF p\_id = 0 THEN*

*          OPEN p\_rc FOR*

*             SELECT ID, NAME, sex, address, postcode, birthday*

*               FROM student;*

*       ELSE*

*          sqlstr :=*

*             'select id,name,sex,address,postcode,birthday*

*            from student where id=:w\_id';*

*          OPEN p\_rc FOR sqlstr USING p\_id;*

*       END IF;*

*    END get;*

*END pkg\_test;  *

函数返回记录集*:*  
建立带*ref cursor*定义的包和包体及函数：

**函数返回记录集代码**

*CREATE OR REPLACE*

*package pkg\_test as*

*/\* *定义*ref cursor*类型

不加*return*类型，为弱类型，允许动态*sql*查询，

否则为强类型，无法使用动态*sql*查询*;*

*\*/*

*   type myrctype is ref cursor; *

*--*函数申明

*   function get(intID number) return myrctype;*

*end pkg\_test; *

**包体代码**

*CREATE OR REPLACE*

*package body pkg\_test as*

*--*函数体

*    function get(intID number) return myrctype is*

*      rc myrctype;  --*定义*ref cursor*变量

*      sqlstr varchar2(500);*

*    begin*

*      if intID=0 then*

*         --*静态测试，直接用*select*语句直接返回结果

*         open rc for select id,name,sex,address,postcode,birthday from*

*student;*

*      else*

*         --*动态*sql*赋值，用*:w\_id*来申明该变量从外部获得

*         sqlstr := 'select id,name,sex,address,postcode,birthday from*

*student where id=:w\_id';*

*         --*动态测试，用*sqlstr*字符串返回结果，用*using*关键词传递参数

*         open rc for sqlstr using intid;*

*      end if;*

*      return rc;*

*    end get;*

*end pkg\_test;*

***Java*****调用*oracle*函数返回游标处理代码**

*CallableStatement cstmt = null;*

*ResultSet rs = null;*

*try \{*

*String callSql = "\{? = call AAAAA(?)\}";*

*cstmt = conn.prepareCall(callSql);*

*cstmt.setString(2, "userName");*

*cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);*

*cstmt.execute();*

*rs = (ResultSet) cstmt.getObject(1);*

*if (rs != null) \{*

*       System.out.print("usercd");*

*       System.out.print("userName");               *

*       System.out.println("EMAIL");*

*       while (rs.next()) \{       *

*    System.out.print(rs.getString(1)+"   ");*

*    System.out.print(rs.getString(2)+"   ");               *

*    System.out.println(rs.getString(3));*

*       \}   *

*\}*

[https://blog.csdn.net/whaosy/article/details/5956460][https_blog.csdn.net_whaosy_article_details_5956460]

[Link 1]: https://me.csdn.net/paul50060049
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[Link 2]: http://so.csdn.net/so/search/s.do?q=%E5%AD%98%E5%82%A8&t=blog
[--------1.1 ORACLE]: https://blog.csdn.net/paul50060049/article/category/6271435
[https_blog.csdn.net_paul50060049_article_details_71698108]: https://blog.csdn.net/paul50060049/article/details/71698108
[whaosy]: https://me.csdn.net/whaosy
[https_blog.csdn.net_whaosy_article_details_5956460]: https://blog.csdn.net/whaosy/article/details/5956460