Oracle 存储过程处理存储过程游标集

好久没有写任何东西了，今天补充随便写一点，刚好以前同事问到，以资鼓励，年前最后一点记录.....

 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

--1. use schema scott create temp table,default no data
create table t_emp as select * from scott.emp where 1=2;
--1.1 check data 
select * from t_emp;
--2. create procedure(first)
CREATE OR REPLACE PROCEDURE sp_proc_first(in_deptno number, o_cur OUT SYS_REFCURSOR) 
IS  
sqlstr VARCHAR2(200); -- define variable,
BEGIN  sqlstr := 'SELECT EMPNO, ENAME, job,mgr, hiredate,sal,comm,deptno FROM scott.emp WHERE deptno = :in_deptno';   
OPEN o_cur FOR sqlstr USING in_deptno; -- get cursor value 
END;
--3 create procedure(second) using first procedure
create or replace procedure sp_proc_second(in_deptno number)
is
  type emp_rec_type is record (empno number(4),ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2));
  type emp_tab_type is table of emp_rec_type;
  emp_tab emp_tab_type;
  type uif_tab_type is table of t_emp%rowtype;
  uif_tab uif_tab_type:=uif_tab_type();
  rs sys_refcursor;
begin
  sp_proc_first(in_deptno,rs);-- call first proc
  delete from t_emp;-- delete from all data ,firstly
  fetch rs bulk collect into emp_tab;
  for i in 1..emp_tab.count loop
    uif_tab.extend;
    uif_tab(i).empno := emp_tab(i).empno;
    uif_tab(i).ename := emp_tab(i).ename;
    uif_tab(i).job := emp_tab(i).job;
    uif_tab(i).mgr := emp_tab(i).mgr;
    uif_tab(i).HIREDATE := emp_tab(i).HIREDATE;
    uif_tab(i).SAL := emp_tab(i).SAL;
    uif_tab(i).comm := emp_tab(i).comm;
    uif_tab(i).deptno := emp_tab(i).deptno;
  end loop;
  --insert into all data
  forall i in 1..uif_tab.count
  insert into t_emp values uif_tab(i);
  close rs;
  commit;
end;
--test
begin
  sp_proc_second(10);
end;
--check data
select * from t_emp;

--over