Placing cursors in nested loops : LOOP « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. LOOP

SQL> --Cursors in Nested Loops
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE),
  8    MANAGER_ID    NUMBER(3)
  9  )
 10  /

Table created.

SQL>
SQL> create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (5,'Director');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (6,'Mediator');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (7,'Proffessor');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (1,      'Jason', to_date('19960725','YYYYMMDD'), 1234,              8767,         'E',    2)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (2,      'John',  to_date('19970715','YYYYMMDD'), 2341,              3456,         'W',    3)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (3,      'Joe',   to_date('19860125','YYYYMMDD'), 4321,              5654,         'E',    3)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (4,      'Tom',   to_date('20060913','YYYYMMDD'), 2413,              6787,         'W',    4)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (5,      'Jane',  to_date('20050417','YYYYMMDD'), 7654,              4345,         'E',    4)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (6,      'James', to_date('20040718','YYYYMMDD'), 5679,              6546,         'W',    5)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION, MANAGER_ID)
  2               values (7,      'Jodd',  to_date('20030720','YYYYMMDD'), 5438,              7658,         'E',    6)
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (8,      'Joke',  to_date('20020101','YYYYMMDD'), 8765,              4543,         'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (9,      'Jack',  to_date('20010829','YYYYMMDD'), 7896,              1232,         'E')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
         1 Jason           25-JUL-96        1234        8767 E          2
         2 John            15-JUL-97        2341        3456 W          3
         3 Joe             25-JAN-86        4321        5654 E          3
         4 Tom             13-SEP-06        2413        6787 W          4
         5 Jane            17-APR-05        7654        4345 E          4
         6 James           18-JUL-04        5679        6546 W          5
         7 Jodd            20-JUL-03        5438        7658 E          6
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E

9 rows selected.

SQL> select * from job
  2  /

     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder
         5 Director
         6 Mediator
         7 Proffessor
         8 Programmer
         9 Developer

9 rows selected.

SQL>
SQL> declare
  2      cursor c_job is select * from job;
  3      r_job c_job%ROWTYPE;
  4      cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;
  5
  6      r_emp c_empInjob%ROWTYPE;
  7  begin
  8      open c_job;
  9      loop
 10          fetch c_job into r_job;
 11          exit when c_job%NOTFOUND;
 12          open c_empInjob (r_job.empno);
 13          loop
 14              fetch c_empInjob into r_emp;
 15              exit when c_empInjob%NOTFOUND;
 16          end loop;
 17          close c_empInjob;
 18
 19      end loop;
 20      close c_job;
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL> drop table job
  2  /

Table dropped.