Oracle PL/SQL Tutorial/Cursor/LOOP

Материал из SQL эксперт
Перейти к: навигация, поиск

A loop is required is to read each row in cursor returned

You can combine the power of a FOR loop to access the rows in a cursor.

When you use a FOR loop, you don"t have to explicitly open and close the cursor-the FOR loop does this automatically for you.



   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2    CURSOR cv_employee_cursor IS
 3      SELECT id, first_name, salary
 4      FROM employee
 5      ORDER BY id;
 6  BEGIN
 7    FOR v_employee IN cv_employee_cursor LOOP
 8      DBMS_OUTPUT.PUT_LINE(
 9        "id = " || v_employee.id ||
10        ", name = " || v_employee.first_name ||
11        ", salary = " || v_employee.salary
12      );
13    END LOOP;
14  END;
15  /

id = 01, name = Jason, salary = 1234.56 id = 02, name = Alison, salary = 6661.78 id = 03, name = James, salary = 6544.78 id = 04, name = Celia, salary = 2344.78 id = 05, name = Robert, salary = 2334.78 id = 06, name = Linda, salary = 4322.78 id = 07, name = David, salary = 7897.78 id = 08, name = James, salary = 1232.78 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Cursor FOR Loop

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> DECLARE

 2    CURSOR c_employee IS
 3      SELECT id, first_name, last_name
 4        FROM employee
 5        WHERE id = "01";
 6  BEGIN
 7    -- Begin the loop. An implicit OPEN of c_employee
 8    -- is done here.
 9    FOR v_employeeData IN c_employee LOOP
10      DBMS_OUTPUT.put_line(v_employeeData.ID);
11
12    END LOOP;
13    -- Commit our work.
14    COMMIT;
15  END;
16  /

01 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL> SQL></source>


Exit a LOOP when cursor%NOTFOUND

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> DECLARE

 2    v_FirstName VARCHAR2(20);
 3    v_LastName  VARCHAR2(20);
 4
 5    CURSOR c_Students IS
 6      SELECT first_name, last_name FROM employee;
 7  BEGIN
 8    -- Begin cursor processing.
 9    OPEN c_Students;
10    LOOP
11      -- Retreive one row.
12      FETCH c_Students INTO v_FirstName, v_LastName;
13      EXIT WHEN c_Students%NOTFOUND;
14    END LOOP;
15    CLOSE c_Students;
16  END;
17  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>


for data in ( select * from tableName )

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE DEPT(

 2      DEPTNO NUMBER(2),
 3      DNAME VARCHAR2(14),
 4      LOC VARCHAR2(13)
 5  );

Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> create or replace procedure implicit

 2  as
 3  begin
 4      for x in ( select * from dept )
 5      loop
 6          null;
 7      end loop;
 8  end;
 9  /

Procedure created. SQL> SQL> SQL> drop table dept; Table dropped. SQL> SQL> SQL></source>


LOOP..END LOOP Cursor Loop

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> DECLARE

 2    v_employeeID   employee.id%TYPE;
 3    v_FirstName   employee.first_name%TYPE;
 4    v_LastName    employee.last_name%TYPE;
 5
 6    CURSOR c_employee IS
 7      SELECT id, first_name, last_name
 8        FROM employee
 9        WHERE id = "01";
10  BEGIN
11    OPEN c_employee;
12    LOOP
13      -- Retrieve information for the next employee
14      FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;
15         DBMS_OUTPUT.put_line(v_employeeID);
16         DBMS_OUTPUT.put_line(v_FirstName);
17         DBMS_OUTPUT.put_line(v_LastName);
18      -- Exit loop when there are no more rows to fetch
19      EXIT WHEN c_employee%NOTFOUND;
20
21      DBMS_OUTPUT.put_line("out of fetch");
22      DBMS_OUTPUT.put_line(v_employeeID);
23      DBMS_OUTPUT.put_line(v_FirstName);
24      DBMS_OUTPUT.put_line(v_LastName);
25
26    END LOOP;
27
28    -- Free resources used by the cursor
29    CLOSE c_employee;
30
31    COMMIT;
32  END;
33  /

01 Jason Martin out of fetch 01 Jason Martin 01 Jason Martin PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


Loop till exit when cursorName%notfound

   <source lang="sql">

SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> CREATE TABLE books (

 2    isbn      CHAR(10) PRIMARY KEY,
 3    category  VARCHAR2(20),
 4    title     VARCHAR2(100),
 5    num_pages NUMBER,
 6    price     NUMBER,
 7    copyright NUMBER(4),
 8    emp1   NUMBER,
 9    emp2   NUMBER,
10    emp3   NUMBER
11  );

Table created. SQL> SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("72121203", "Oracle Basics", "Oracle DBA 101", 563, 39.99, 2009, 1, 2, 3);

1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)

 2             VALUES ("72122048", "Oracle Basics", "Oracle8i: A Beginner""s Guide", 765, 44.99, 2009, 4, 5);

1 row created. SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)

 2             VALUES ("72131454", "Oracle Basics", "Oracle Performance Tuning 101", 404, 39.99, 2001, 6, 7, 8);

1 row created.

SQL> SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> DECLARE

 2
 3     cv_emp SYS_REFCURSOR;
 4     v_title BOOKS.TITLE%TYPE;
 5     v_emp emp%ROWTYPE;
 6     v_counter PLS_INTEGER := 0;
 7
 8     CURSOR book_cur
 9     IS
10        SELECT b.title, CURSOR (SELECT * FROM emp a WHERE a.id = b.emp1 OR a.id = b.emp2 OR a.id = b.emp3)
11        FROM books b;
12
13  BEGIN
14
15     DBMS_OUTPUT.ENABLE(1000000);
16
17     OPEN book_cur;
18
19     LOOP
20        FETCH book_cur INTO v_title, cv_emp;
21        EXIT WHEN book_cur%NOTFOUND;
22
23        v_counter := 0;
24
25        DBMS_OUTPUT.PUT_LINE("Title from the main cursor: "||v_title);
26
27        LOOP
28           FETCH cv_emp INTO v_emp;
29           EXIT WHEN cv_emp%NOTFOUND;
30
31           v_counter := v_counter + 1;
32
33           DBMS_OUTPUT.PUT_LINE("emp"||v_counter||": "||v_emp.fname||" "||v_emp.lname);
34        END LOOP;
35     END LOOP;
36
37     CLOSE book_cur;
38
39  END;
40  /

Title from the main cursor: Oracle DBA 101 emp1: E F emp2: C D emp3: A B Title from the main cursor: Oracle8i: A Beginner"s Guide emp1: G Z emp2: G H Title from the main cursor: Oracle Performance Tuning 101 PL/SQL procedure successfully completed. SQL> drop table books; Table dropped. SQL> drop table emp; Table dropped.</source>


Placing cursors in nested loops

   <source lang="sql">

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.</source>


Use For loop to output data in a PL/SQL table of cursor

   <source lang="sql">

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2      CURSOR all_emps IS
 3          SELECT *
 4          FROM employee
 5          ORDER BY first_name;
 6
 7      TYPE emp_table IS TABLE OF employee%ROWTYPE;
 8
 9      emps emp_table;
10      emps_max PLS_INTEGER;
11      inx1 PLS_INTEGER;
12  BEGIN
13      emps_max := 0;
14
15      emps := emp_table ();
16
17      FOR emp IN all_emps LOOP
18          emps_max := emps_max + 1;
19          emps.extend;
20          emps(emps_max).id := emp.id;
21          emps(emps_max).first_name := emp.first_name;
22          emps(emps_max).salary := emp.salary;
23      END LOOP;
24
25      emps.extend(5,1);
26
27      FOR inx1 IN 1..emps_max+5 LOOP
28          DBMS_OUTPUT.PUT_LINE(emps(inx1).id ||" " || emps(inx1).first_name);
29      END LOOP;
30      emps.trim(5);
31
32      emps.delete(1);
33
34      DBMS_OUTPUT.PUT_LINE(emps.count);
35
36      FOR inx1 IN 1..emps_max+5 LOOP
37          IF emps.exists(inx1) THEN
38              DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name);
39          END IF;
40      END LOOP;
41  END;
42  /

02 Alison 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert 02 Alison 02 Alison 02 Alison 02 Alison 02 Alison 7 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL></source>


while cursorName%found, loop

   <source lang="sql">

SQL> SQL> SQL> create table gift(

 2           gift_id                integer         primary key
 3          ,emp_id            integer
 4          ,register_date          date not null
 5          ,total_price            number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment                varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,message                varchar2(100)
12  );

Table created. SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values

 2                 (1,1,sysdate, 123.12, sysdate+1, "12 noon", "CA",1, null, "Happy Birthday to you");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values

 2                 (2,1,sysdate, 50.98, sysdate+2, "1 pm", "CA",7, "name1", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (3, 2,sysdate, 35.99, sysdate+3, "1 pm", "VS",2, "Tom", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (4, 2,sysdate, 19.95, sysdate+4, "5 pm", "CA",2, "Mary", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (5, 6,sysdate, 10.95, sysdate+5, "4:30 pm", "VS", 2, "Jack", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (6, 9,sysdate-3, 22.95, sysdate+6, "1 pm", "VS", 2, "Mike", "Happy Birthday" );

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (7, 9,sysdate-4, 35.95, sysdate+7, "12 noon", "VS", 2, "Jason", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (8, 12,sysdate-6, 35.95, sysdate+8, "12 noon", "DI",3, "Lite", "Happy New Year");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (9, 12,sysdate-9, 75.95, sysdate+9, "12 noon", "CA",7, "Sara", "Happy New Year" );

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (10, 4, sysdate, 19.95, sysdate, "2:30 pm", "VG",2, "Mary", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (11, 2, sysdate, 30.00, sysdate+2, "1:30 pm", "VG",2, "Mary", "Happy New Year");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (12, 7, sysdate-3, 21.95, sysdate-2, "3:30 pm", "CA",2, "Mary", "Happy New Year");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (13, 7, sysdate, 21.95, sysdate, "3:30 pm", "CA",2, "Mary", "Happy Birthday");

1 row created. SQL> SQL> create table sale(

 2           gift_id                 integer
 3          ,product_id              integer
 4          ,quantity                number(4,0)
 5          ,price              number(7,2)
 6          ,primary key (gift_id ,product_id)
 7  );

Table created. SQL> SQL> insert into sale(gift_id, product_id, quantity, price) values(1, 2, 10, 23.00 ); 1 row created. SQL> insert into sale(gift_id, product_id, quantity, price) values(2, 1, 1, 23.11 ); 1 row created. SQL> insert into sale(gift_id, product_id, quantity, price) values(11, 8, 1, 30.00); 1 row created.

SQL> SQL> SQL> --set termout on SQL> DECLARE

 2    giftIDValue           sale.gift_id%type;
 3    totalValue            sale.price%type;
 4  cursor c1 is
 5    select gift_id, sum(price * quantity) from sale group by gift_id;
 6  begin
 7      open c1;
 8      fetch c1 into giftIDValue, totalValue;
 9      while c1%found loop
10          update gift
11          set total_price = totalValue
12          where gift_id = giftIDValue;
13          fetch c1 into giftIDValue, totalValue;
14      end loop;
15      close c1;
16  end;
17  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table gift; Table dropped. SQL> drop table sale; Table dropped. SQL></source>


WHILE..LOOP Cursor Loop

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> DECLARE

 2    CURSOR c_employee IS
 3      SELECT id, first_name, last_name
 4        FROM employee
 5        WHERE id = "01";
 6
 7    v_employeeData  c_employee%ROWTYPE;
 8  BEGIN
 9    -- Open the cursor and initialize the active set
10    OPEN c_employee;
11
12    -- Retrieve the first row, to set up for the WHILE loop
13    FETCH c_employee INTO v_employeeData;
14
15    -- Continue looping while there are more rows to fetch
16    WHILE c_employee%FOUND LOOP
17      DBMS_OUTPUT.put_line(v_employeeData.ID);
18      FETCH c_employee INTO v_employeeData;
19    END LOOP;
20
21    -- Free resources used by the cursor
22    CLOSE c_employee;
23
24    -- Commit our work
25    COMMIT;
26  END;
27  /

01 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>