好久没有写任何东西了，今天补充随便写一点，刚好以前同事问到，以资鼓励，年前最后一点记录.....
?Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
--1. use schema scott create temp table,default no datacreate 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 procedurecreate 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;--testbegin sp_proc_second(10);end;--check dataselect * from t_emp;--over
?