oracle存储过程输出时间,oracle存储过程(游标作为OUT参数输出)

包中带过程      要自己定义一个type [cur_name] is ref cursor游标，返回的时候就直接 procedure AAA(变量名 out [cur_name])如此申明OUT变量      存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了  1、Sql代码  --PL/SQL Code (包中带过程) 过程带游标的OUT参数，返回游标(ref cursor)    create or replace package my_pack as   type my_ref_cursor is ref cursor;    procedure getMyCursor(val out my_ref_cursor);     end my_pack;       create or replace package body my_pack as   procedure getMyCursor(val out my_ref_cursor)    is   begin     open val for select * from student;    end;    end my_pack;       --PL/SQL Code (包中带过程) 过程带游标的OUT参数，返回游标(ref cursor)     create or replace package my_pack as      type my_ref_cursor is ref cursor;      procedure getMyCursor(val out my_ref_cursor);     end my_pack;         create or replace package body my_pack as      procedure getMyCursor(val out my_ref_cursor)      is      begin       open val for select * from student;      end;     end my_pack;  2、Java代码  Class.forName("oracle.jdbc.driver.OracleDriver");    Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());       CallableStatement cs = conn.prepareCall("{ call my_pack.getMyCursor(?) }");    cs.registerOutParameter(1,OracleTypes.CURSOR);    cs.execute();    ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);    while(rs.next())    {        System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));    }          Class.forName("oracle.jdbc.driver.OracleDriver");     Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());       CallableStatement cs = conn.prepareCall("{ call my_pack.getMyCursor(?) }");     cs.registerOutParameter(1,OracleTypes.CURSOR);     cs.execute();     ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);     while(rs.next())     {      System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));     }  3、Sql代码  --PL/SQL Code(存储过程) 带游标的OUT参数，返回游标(ref cursor)    create or replace procedure retCursor(ret_cursor out sys_refcursor)is   ret_cursor_value  sys_refcursor;    begin   open ret_cursor_value for select * from student;    ret_cursor:=ret_cursor_value;    end retCursor;      --PL/SQL Code(存储过程) 带游标的OUT参数，返回游标(ref cursor)     create or replace procedure retCursor(ret_cursor out sys_refcursor)is     ret_cursor_value  sys_refcursor;     begin     open ret_cursor_value for select * from student;      ret_cursor:=ret_cursor_value;     end retCursor;  4、Java代码  Class.forName("oracle.jdbc.driver.OracleDriver");    Connection conn = Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());       CallableStatement cs = conn.prepareCall("{ call retCursor(?) }");    cs.registerOutParameter(1,OracleTypes.CURSOR);    cs.execute();    ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);    while(rs.next())    {        System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));    }      Class.forName("oracle.jdbc.driver.OracleDriver");     Connection conn = Connection conn = DriverManager.getConnection                       ("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());       CallableStatement cs = conn.prepareCall("{ call retCursor(?) }");     cs.registerOutParameter(1,OracleTypes.CURSOR);     cs.execute();     ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);     while(rs.next())     {      System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3)+"  "+ rs.getDate(4)+"  "+rs.getString(5));     }  5、下面是个每个学生求平均值的存储过程。遇到的问题是带参数游标中的变量名字不要和表中的一样，否则会出问题  Sql代码  create or replace procedure AAA    as     --查询学生表的ID    cursor s_sno is select s.sno from student s;     --通过学生ID查询平均成绩    cursor sc_avg(s_no varchar2) is select avg(sc.degree) from score sc where sc.sno=s_no;     s_sno_j student.sno%type;   --变量ID    sc_avg_i score.degree%type; --变量平局成绩    begin   open s_sno;--打开查询ID的游标    loop       fetch s_sno into s_sno_j;       exit when s_sno%notfound;           open sc_avg(s_sno_j); --打开查询平均成绩的游标，参数为学生ID         loop         fetch sc_avg into sc_avg_i;         exit when sc_avg%notfound;         dbms_output.put_line(sc_avg_i);         end loop;         close sc_avg;    end loop;    close s_sno;    end AAA;