Cursors Declared with Parameters

Contact Us or call 1-877-932-8228
Cursors Declared with Parameters

Cursors Declared with Parameters

Cursors used up to this point were related to simple unchanging SQL queries. Cursor parameters allow you to modify the query results.

Code Sample:

Data-Retrieval-Techniques/Demos/employees_cursor_parameter.sql
declare
 
  CURSOR crs (fname VARCHAR2) IS
  
    SELECT * 
    FROM employees
    WHERE first_name = fname
    
    ORDER BY first_name;
 
  rec employees%rowtype;
begin

  open crs ('David');

  loop
    fetch crs into rec;
    exit when crs%notfound;
    dbms_output.put_line(rec.first_name
                         || ' '
                         || rec.last_name);
  end loop;

  close crs;
end;
/

Code Explanation

In this example, a cursor named crs is declared that takes a parameter named fname. When the cursor is opened, a value is supplied to the parameter which limits the results of the query.

Next