Use 'for loop' to loop through the cursor : CURSOR FOR Loop « Cursor « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Cursor » CURSOR FOR Loop 




Use 'for loop' to loop through the cursor
 


SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(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  /

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  /

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  /

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  /

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  /

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  /

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  /

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  /

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


ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
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


ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
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


rows selected.

SQL>
SQL>
SQL> -- cursor for loop
SQL>
SQL> set serverout on
SQL>
SQL> declare
  2   begin
  3     for my_emp_rec in (select id, first_name
  4                            from employee)
  5     loop
  6       dbms_output.put('ID #' || my_emp_rec.id);
  7       dbms_output.put_line(' is named ' || my_emp_rec.first_name);
  8     end loop;
  9  end;
 10  /
ID #01 is named Jason
ID #02 is named Alison
ID #03 is named James
ID #04 is named Celia
ID #05 is named Robert
ID #06 is named Linda
ID #07 is named David
ID #08 is named James

PL/SQL procedure successfully completed.

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

Table dropped.

SQL>
SQL>
SQL>
           
         
  














Related examples in the same category
1.Create a cursor in for statement
2.The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to guide a loop
3.A function that uses a CURSOR FOR Loop
4.A subquery in the FROM clause of a cursor within a cursor FOR loop
5.Assign value from cursor to a table collection by index
6.Calculation based on cursor value
7.For each row in a cursor
8.Output value in cursor one by one
9.Simple Loop and fetch cursor
10.Use pl/sql, cursor and for loop to count table
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.