Explicit Cursors

Contact Us or call 1-877-932-8228
Explicit Cursors

Explicit Cursors

An explicit cursor is used to process a result set that contains zero or more rows.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/employees_cursor.sql
declare
  CURSOR crs is SELECT * FROM employees;       
  rec employees%rowtype;
begin
  if not crs%isopen then
    open crs;
  end if;
  loop
    fetch crs into rec;
    exit when crs%notfound;
    dbms_output.put_line(rec.first_name || ' ' ||
                         rec.last_name);
  end loop;
  if crs%isopen then
    close crs;
  end if;
end;
/

Code Explanation

An explicit cursor is declared in this example. A corresponding variable that has a %ROWTYPE anchored declaration will hold each record returned. The cursor is opened and a loop is used to fetch each row into the record variable. Note that the same cursor attributes that are available for implicit cursors are also available for explicit cursors. The loop is exited when the cursor attribute %NOTFOUND is true. The cursor is closed when processing is complete. This saves database resources and prevents exceptions that can result from having too many cursors open. Note that an exception is thrown if there is an attempt to close a cursor that is not open, or if there is an attempt to open a cursor that is already open. The %ISOPEN boolean cursor attribute is useful for avoiding these exceptions.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/employees_cursor_for_loop.sql
begin
 FOR c IN (SELECT first_name, last_name
           FROM employees)
 LOOP
        dbms_output.put_line(c.first_name || ' ' ||
                             c.last_name);
 END LOOP;
end;

Code Explanation

A cursor for loop provides concise syntax for creating an implicitly declared cursor on the fly without the need to explicitly declare it earlier. Like a FOR loop that iterates over a range of numbers, the CURSOR FOR loop iterates over a range of records (all those contained in the returned result set).

Code Sample:

Use-of-SQL-in-PLSQL/Demos/bulk_collect.sql
declare
  cursor c1 is 
    SELECT * 
    FROM employees
    order by first_name, last_name;
    
  type t_emps is table of employees%ROWTYPE
                 index by pls_integer;
  v_emp_list t_emps;
begin
  open c1;
  fetch c1 bulk collect into v_emp_list;  
  close c1;
  
  if v_emp_list.count > 0  then
    for i in v_emp_list.first .. v_emp_list.last
    loop
      DBMS_OUTPUT.PUT_LINE(v_emp_list(i).phone_number
                           || ' ' || v_emp_list(i).email);
    end loop;
  else
    dbms_output.put_line('No Data Found');
  end if;
end;
/

Code Explanation

Using the BULK COLLECT clause is a very efficient way to retrieve the result set into a collection in a single operation rather than looping through each row. However, there is often a trade off between memory usage and performance when using this option.

An explicit cursor is declared, followed by a declaration of a collection type. The v_emp_list variable is a specific instance of this type. The cursor is opened, the results are fetched into the list in one operation and the cursor is closed. The results are then iterated through and the phone and email addresses are displayed. The use of collections will be described in greater depth in a later chapter.

Next