JAVA调用ORACLE PACKAGE 的FUNCTION&PROCEDURE

CREATE OR REPLACE PACKAGE SCOTT.pkg_test as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor; 

--函数申明
function get(intID number) return myrctype;
Procedure getstudent( 
        intID number,
       return_list out myrctype 
  );
end pkg_test;

 

CREATE OR REPLACE PACKAGE BODY SCOTT.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;
    
  Procedure getstudent( 
         intID number,
       return_list out myrctype 
  )as 
   
   begin 
       open return_list
       for 
        select id,name,sex,address,postcode,birthday from student;
  end getstudent; 


end pkg_test;
/

 

  try {
            conn.setAutoCommit(false);
            CallableStatement proc = conn
                    .prepareCall("{call  pkg_test.getstudent(?,?)}");
            proc.setInt(1, 1);
            proc.registerOutParameter(2, OracleTypes.CURSOR);
            proc.execute();
            ResultSet rs = (ResultSet) proc.getObject(2);
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
        } catch (SQLException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        }
        try {
            conn.setAutoCommit(false);
            CallableStatement proc = conn
                    .prepareCall("{call ? := pkg_test.get(?)}");
            proc.setInt(2, 0);
            proc.registerOutParameter(1, OracleTypes.CURSOR);
            proc.execute();
            ResultSet rs = (ResultSet) proc.getObject(1);
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
        } catch (SQLException e) {
            // TODO 自动生成 catch 块
            e.printStackTrace();
        }