存储过程调存储过程

create or replace procedure pro_test1( processinstid in number,
                                   resultInfo        OUT sys_refcursor) is

begin

  open resultInfo for select *  from inst t where t.currentstate=100;

end pro_test1;

pro_test2调用pro_test1：

create or replace procedure pro_test2instid in number,
                                   resultInfo        OUT sys_refcursor)
                                   
is

type emp_procs_type is record (
                       instid inst.instid%type,
                      defid inst.defid%type
                      );
emp_proces emp_procs_type;
curr_pros sys_refcursor;
begin
   pro_test1(1,curr_pros);
   loop
       fetch curr_pros into emp_proces;
       exit when curr_pros%notfound;
       dbms_output.put_line(emp_proces.instid);
  end loop;
  close curr_pros;

end pro_test2;

pro_test2调用pro_test1：

create or replace procedure pro_test2(instid in number,
                                   resultInfo        OUT sys_refcursor)
                                   
is
type un_record_type is table ofinst%rowtype;
un_record un_record_type;

curr_pros sys_refcursor;
cursor end_pros is select * from inst where currentstate=200;

begin
   pro_test1(1,curr_pros);
   fetch curr_pros bulk collect into un_record;
  close curr_pros;
 
  open end_pros;
   fetch end_pros bulk collect into un_record;
  close end_pros;
 
  for i in 1.un_record.count loop
 

      dbms_output.put_line('字段1：'||un_record(i).v1 ||'   字段2：' ||un_record(i).v2);
    end loop;

end pro_test2;