Thursday, February 3, 2011

Nested Cursor

DECLARE
     -- local variable declaration
     v_deptno dept.deptno%TYPE;  
    
    -- department cursor declaration
     CURSOR c_dept IS
            SELECT deptno FROM dept;
     -- employee cursor declaration
     CURSOR c_emp IS
            SELECT empno, ename,sal
            FROM emp
            WHERE deptno = v_deptno;
         
BEGIN
     OPEN c_dept;
     LOOP
          FETCH c_dept INTO v_deptno;
          EXIT WHEN c_dept%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Department No is '|| v_deptno);
          FOR r_emp IN c_emp LOOP
              DBMS_OUTPUT.PUT_LINE('Original record '|| r_emp.empno||' - '||
              RPAD(r_emp.ename,6)||' - '||TO_CHAR(r_emp.sal,99999.99) );
              UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
          END LOOP;
          FOR r_emp1 IN c_emp LOOP
                  DBMS_OUTPUT.PUT_LINE('Updated record  '|| r_emp1.empno||' - '||
                  RPAD(r_emp1.ename,6)||' - '||TO_CHAR(r_emp1.sal,99999.99) );
          END LOOP;
     END LOOP;
     CLOSE c_dept;
     ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF C_DEPT%ISOPEN THEN
           CLOSE c_dept;
        END IF;
        RAISE_APPLICATION_ERROR(-20001,'An error occured  -' ||
        SQLCODE ||'- Error - '||SQLERRM);
END;
/

Department No is 10
Original record 7782 - CLARK - 2450.00
Original record 7839 - KING - 5000.00
Original record 7934 - MILLER - 1300.00
Updated record- 7782 - CLARK - 2572.50
Updated record- 7839 - KING - 5250.00
Updated record- 7934 - MILLER - 1365.00
Department No is 20
Original record 7369 - SMITH - 800.00
Original record 7566 - JONES - 2975.00
Original record 7788 - SCOTT - 3000.00
Original record 7876 - ADAMS - 1100.00
Original record 7902 - FORD - 3000.00
Updated record- 7369 - SMITH - 840.00
Updated record- 7566 - JONES - 3123.75
Updated record- 7788 - SCOTT - 3150.00
Updated record- 7876 - ADAMS - 1155.00
Updated record- 7902 - FORD - 3150.00
Department No is 30
Original record 7499 - ALLEN - 1600.00
Original record 7521 - WARD - 1250.00
Original record 7654 - MARTIN - 1250.00
Original record 7698 - BLAKE - 2850.00
Original record 7844 - TURNER - 1500.00
Original record 7900 - JAMES - 950.00
Updated record- 7499 - ALLEN - 1680.00
Updated record- 7521 - WARD - 1312.50
Updated record- 7654 - MARTIN - 1312.50
Updated record- 7698 - BLAKE - 2992.50
Updated record- 7844 - TURNER - 1575.00
Updated record- 7900 - JAMES - 997.50
Department No is 40

       
  

No comments:

Post a Comment

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...