Home > Scripts > Sample PLSQL block.

Sample PLSQL block.

October 28, 2013

SQL> l
1* select * from emp_new
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 900 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1700 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1350 500 30
7566 JONES MANAGER 7839 2-Apr-81 3075 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1350 1400 30
7698 BLAKE MANAGER 7839 1-May-81 2950 30
7782 CLARK MANAGER 7839 9-Jun-81 2550 10
7788 SCOTT ANALYST 7566 19-Apr-87 3100 20
7839 KING PRESIDENT 17-Nov-81 5100 10
7844 TURNER SALESMAN 7698 8-Sep-81 1600 0 30
7876 ADAMS CLERK 7788 23-May-87 1200 20
7900 JAMES CLERK 7698 3-Dec-81 1050 30
7902 FORD ANALYST 7566 3-Dec-81 3100 20
7934 MILLER CLERK 7782 23-Jan-82 1400 10

14 rows selected.

SQL> DECLARE
2 CURSOR cl IS SELECT * FROM EMP_NEW WHERE empno > 0 ;
3 REC_COUNT NUMBER := 1;
4
5 BEGIN
6 FOR c IN cl
7 LOOP
8 UPDATE emp_new SET SAL = SAL + 200 WHERE empno = c.EMPNO;
9
10 REC_COUNT := REC_COUNT +1;
11 IF MOD(REC_COUNT,2)=0 THEN
12 COMMIT;
13 REC_COUNT := 1;
14 END IF;
15 END LOOP;
16 COMMIT;
17 END;
18 /

PL/SQL procedure successfully completed.

SQL> select * from emp_new
2 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 1100 20
7499 ALLEN SALESMAN 7698 20-Feb-81 1900 300 30
7521 WARD SALESMAN 7698 22-Feb-81 1550 500 30
7566 JONES MANAGER 7839 2-Apr-81 3275 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1550 1400 30
7698 BLAKE MANAGER 7839 1-May-81 3150 30
7782 CLARK MANAGER 7839 9-Jun-81 2750 10
7788 SCOTT ANALYST 7566 19-Apr-87 3300 20
7839 KING PRESIDENT 0 17-Nov-81 5300 10
7844 TURNER SALESMAN 7698 8-Sep-81 1800 0 30
7876 ADAMS CLERK 7788 23-May-87 1400 20
7900 JAMES CLERK 7698 3-Dec-81 1250 30
7902 FORD ANALYST 7566 3-Dec-81 3300 20
7934 MILLER CLERK 7782 23-Jan-82 1600 10
Advertisements
Categories: Scripts
%d bloggers like this: