Oracle存储过程详解(三)-集合

1. 使用包头、包体的形式

--包头 
create or replace package mypackage as
 type empcursor is ref cursor; --声明一个光标类型
 procedure queryEmpList(dno in number,empList out empcursor);
end;

--创建包体 
create or replace package body mypackage as
 procedure queryEmpList(dno in number,empList out empcursor) as
   begin
     --打开光标
     open empList for select * from emp where deptno=dno;
   end;
end;

2. 使用存储过程，返回游标的形式

--定义一个返回程序集的引用游标 
CREATE OR REPLACE PACKAGE BAF_QUERY_TABLE AS
  TYPE P_CURSOR IS ref CURSOR;
END BAF_QUERY_TABLE;

--创建存储过程，并返回游标的形式返回程序集 
create or replace procedure getList(p_eno number, p_out_cursor out BAF_QUERY_TABLE.P_CURSOR) is
begin
  --没有给定员工ID则返回所有员工信息
  if p_eno is null then 
      open p_out_cursor for select * from emp;
  else
    --返回指定ID的员工信息
      open p_out_cursor for select * from emp where empno = p_eno;
  end if;

end getList;


--以上创建的包还可以给存储函数使用
create or replace function sp_ListEmp return BAF_QUERY_TABLE.P_CURSOR 
as 
    l_cursor    BAF_QUERY_TABLE.P_CURSOR; 
begin 
    open l_cursor for select ename, empno from emp order by ename; 
    return l_cursor; 
end;

3. 使用sys_refcursor类型

sys_refcursor是oracle9i以后系统定义的一个refcursor，主要作用是用于存储过程返回结果集。

create or replace procedure rsCursor(p_eno number,rs_cursor out SYS_REFCURSOR)
AS
BEGIN
 --没有给定员工ID则返回所有员工信息
  if p_eno is null then 
      OPEN rs_cursor for select * from emp;
  else
    --返回指定ID的员工信息
      OPEN rs_cursor for select * from emp where deptno = p_eno ;
  end if;

END;

java中调用

Connection conn = null;
        //sql语句 (一定要写上包名)
        String sql = "{call mypackage.queryEmpList(?,?)}";

        try {
            //获取数据库的连接
            conn = JDBCUtil.getConnection();
            //通过连接创建statment
            CallableStatement call = conn.prepareCall(sql);

            //对于IN参数需要赋值
            call.setInt(1,10);

            //对于OUT参数需要先申明
            call.registerOutParameter(2,OracleTypes.CURSOR);

            //执行调用
            call.execute();

            //取出该部门中所有员工信息(注意这里)
            ResultSet rs = ((OracleCallableStatement)call).getCursor(2);

            while(rs.next()){
                //可以取出sql语句中查询的所有字段(这里只取几个演示下)
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println("==================================================");
                System.out.println("empno:"+empno+"\t ename:"+ename+"\t sal:"+sal);
                System.out.println("==================================================");
            }