CREATE OR REPLACE PROCEDURE proc_emp(mgr in number)
is
  sqlstr varchar2(800);
  v_mgr emp.mgr%type;
  type cur_test is ref cursor;
  emp_cur cur_test;
  v_empno number(4);
  v_ename varchar2(10);
  --temp_tbl table%type (EMPNO number(4),ENAME VARCHAR2(10));
  --tbl_temp table(empno emp.empno%type,ename emp.ename%type);
BEGIN
  --create table tbl_temp(empno emp.empno%type,ename emp.ename%type); 
  --create table  temp_tbl(EMPNO number(4),ENAME VARCHAR2(10));
  --select distinct mgr from emp where mgr like '7%';
  v_mgr :=mgr;
  sqlstr:='select empno,ename From emp where mgr='|| v_mgr|| '';
  OPEN emp_cur FOR sqlstr;
 
 FETCH emp_cur INTO v_empno,v_ename;
 insert into temp_tbl(empno,ename) values(v_empno,v_ename);
--If (emp_cur%NOTFOUND) Then
--EXIT;
--End if;
--Result := Result || name_temp || ',';
close emp_cur;
END;

posted on 2007-08-11 13:42  东方新秀  阅读(216)  评论()    收藏  举报