An explicit cursor is used to process a result set that contains zero or more rows.
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; /
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.
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;
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).
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; /
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.