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 Retrieve Data from an Cursor to a RECORD?

If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below:

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

How To Use FETCH Statement in a Loop?

If you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when the cursor pointer reaches the end. The script below gives you a good example:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur IS SELECT * FROM employees
    WHERE manager_id = 101;
  emp_rec employees%ROWTYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Name = ' || 
      emp_rec.first_name || ' ' || emp_rec.last_name); 
  END LOOP; 
  CLOSE emp_cur;
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins

How To Use an Explicit Cursor without OPEN Statements?

If you want to open a cursor and loop through its data rows in quick way, you can use the FOR ... IN ... LOOP statement in the same way as the implicit cursor. The following tutorial exercise gives you a good example:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur IS SELECT * FROM employees
    WHERE manager_id = 101;
BEGIN
  FOR row IN emp_cur LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || 
      row.first_name || ' ' || row.last_name); 
  END LOOP; 
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins

Can Multiple Cursors Being Opened at the Same Time?

Yes, multiple cursors can be opened at the same time. See the following example:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  CURSOR emp_cur IS SELECT * FROM employees;
  emp_rec employees%ROWTYPE;
  CURSOR dpt_cur IS SELECT * FROM departments;
  dpt_rec departments%ROWTYPE;
BEGIN
  OPEN emp_cur;
  OPEN dpt_cur;
  FETCH emp_cur INTO emp_rec;
  FETCH dpt_cur INTO dpt_rec;
  DBMS_OUTPUT.PUT_LINE('Department name = ' || 
    dpt_rec.department_name); 
  DBMS_OUTPUT.PUT_LINE('Employee name = ' || 
    emp_rec.first_name || ' ' || emp_rec.last_name); 
  CLOSE emp_cur;
  CLOSE dpt_cur;
END;
/
Department name = Administration
Employee name = Steven King

(Continued on next part...)

Part:   1  2  3   4  5