Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Working with Cursors in PL/SQL

By: FYIcenter.com

Part:   1  2   3  4  5 

(Continued from previous part...)

How To Loop through Data Rows in the Implicit Curosr?

You use the FOR ... IN ... LOOP statement to loop through data rows in the implicit cursor as the following syntax:

  FOR row IN dml_statement LOOP
    (statement block with row.field)
  END LOOP; 

Here "row" is a local RECORD type variable with fields automatically defined to match the fields in the data rows resulted from the DML statement. Here is a good tutorial exercise on loop through data rows with the implicit cursor:

BEGIN                                                  
   FOR row IN (SELECT * FROM employees
       WHERE manager_id = 101) LOOP
     DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name); 
   END LOOP;                                           
END;                                                   
/                                                      
Name = Greenberg
Name = Whalen
Name = Mavris
Name = Baer
Name = Higgins

How To Define an Explicit Cursor?

An explicit cursor must be defined in the declaration part of a procedure or function with the CURSOR ... IS statement as shown in the following sample script:

DECLARE
  CURSOR c_list IS SELECT * FROM countries;
  CURSOR t_list IS SELECT * FROM employees
    WHERE employee_id = 100;
BEGIN
  NULL;
END;
/

How To Open and Close an Explicit Cursor?

An existing cursor can be opened or closed by the OPEN or CLOSE statement as shown in the following sample script:

DECLARE
  CURSOR c_list IS SELECT * FROM countries;
  CURSOR t_list IS SELECT * FROM employees
    WHERE employee_id = 100;
BEGIN
  OPEN c_list;
  OPEN t_list; 
  CLOSE c_list;
  CLOSE t_list; 
END;
/

How To Retrieve Data from an Explicit Cursor?

If you have a cursor opened ready to use, you can use the FETCH ... INTO statement to retrieve data from the cursor into variables. FETCH statement will:

• Retrieve all the fields from the row pointed by the current cursor pointer and assign them to variables listed in the INTO clause.
• Move the cursor pointer to the next row.
• Update cursor attributes like FOUND and NOTFOUND.

Here is a sample script showing you how to use FETCH statement:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR t_list IS SELECT first_name, last_name
    FROM employees;
  f_name VARCHAR2(10);
  l_name VARCHAR2(10);
BEGIN
  OPEN t_list;
  FETCH t_list INTO f_name, l_name;
  DBMS_OUTPUT.PUT_LINE('Name = ' || f_name || ' '
    || l_name); 
  FETCH t_list INTO f_name, l_name;
  DBMS_OUTPUT.PUT_LINE('Name = ' || f_name || ' '
    || l_name); 
  -- FETCH t_list INTO l_name; -- must have two variables
  CLOSE t_list;
END;
/
Name = Ellen Abel
Name = Sundar Ande

(Continued on next part...)

Part:   1  2   3  4  5