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
Thursday, February 3, 2011
Subscribe to:
Post Comments (Atom)
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...
-
Table clustering is an optional technique of storing data. In table clustering, rows from one or more tables those are associated with ea...
-
Oracle PL/SQL block accepts user input information with the help of substitution variable. Substitute variable can not be used to output va...
-
Finally continue statement added to oracle 11g PL/SQL language. It signals an immediate end to a loop iteration and return to first statemen...
No comments:
Post a Comment