1 create or replace procedure pr_emp_emphist as 2 cursor emp is select * from emp; 3 cursor emp_PK IS select SYSDATE || empno from emp; 4 v_emp emp%rowtype; 5 v_emp_pk varchar(30); 6 begin 7 open emp; 8 loop 9 fetch emp into v_emp; 10 exit when emp%notfound; 11 OPEN EMP_PK; 12 LOOP 13 FETCH EMP_PK INTO V_EMP_PK; 14 EXIT WHEN EMP_PK%NOTFOUND; 15 --insert into emp_history values(v_emp_pk, v_emp.empno, v_emp.ename, v_emp.job, 16 --v_emp.mgr, v_emp.hiredate, v_emp.sal,v_empm,v_emp.deptno); 17 end loop; 18 close emp_PK; 19 DBMS_OUTPUT.PUT_LINE(v_emp_pk || || v_emp.empno || || v_emp.ename || || v_emp.job 20 || || v_emp.mgr || || v_emp.hiredate || || v_emp.sal || || v_empm || 21 end loop; 22 close emp; 23* end; 24 / Procedure created. SQL> EXEC pr_emp_emphist; 18-AUG-147934 7369 SMITH CLERK 7902 17-DEC-80 800 20 18-AUG-147934 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 18-AUG-147934 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 18-AUG-147934 7566 JONES MANAGER 7839 02-APR-81 2975 20 18-AUG-147934 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 18-AUG-147934 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 18-AUG-147934 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 18-AUG-147934 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 18-AUG-147934 7839 KING PRESIDENT 17-NOV-81 5000 10 18-AUG-147934 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 18-AUG-147934 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 18-AUG-147934 7900 JAMES CLERK 7698 03-DEC-81 950 30 18-AUG-147934 7902 FORD ANALYST 7566 03-DEC-81 3000 20 18-AUG-147934 7934 MILLER CLERK 7782 23-JAN-82 1300 10 PL/SQL procedure successfully completed. i am getting empnos as same for all records, plz help me in resolving this
Posted on: Mon, 18 Aug 2014 16:17:26 +0000
Trending Topics
Recently Viewed Topics
© 2015