When a program runs the same query with different values, such as routes from each airport or staff of each department, one explicit cursor with parameters serves them all. The parameters are used in the query like bind variables, can have defaults, and can be passed by position or by name.
Code for This Guide
The main examples are in the examples/cursors folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
cursor name (param type [default value] [, ...]) is select ... where column = param ...; for r in name(value [, ...]) loop ... end loop; open name(value [, ...]);
Parameter types have no size: write CHAR or NUMBER, not CHAR(3).
Defaults and Named Parameters
The cursor takes an origin and a minimum distance, which defaults to 0. The first loop passes both by position; the second passes only the origin, by name.
Example:
declare
cursor c_routes (p_origin char, p_min_km number default 0) is
select destination, distance_km from routes
where origin = p_origin and distance_km >= p_min_km
order by distance_km desc;
begin
for r in c_routes('DXB', 12000) loop
dbms_output.put_line('DXB to ' || r.destination || ': ' || r.distance_km || ' km');
end loop;
for r in c_routes(p_origin => 'SYD') loop
dbms_output.put_line('SYD to ' || r.destination);
end loop;
end;
/Output:
DXB to AKL: 14200 km DXB to LAX: 13400 km DXB to GRU: 12217 km DXB to SYD: 12043 km SYD to DXB SYD to SIN SYD to AKL PL/SQL procedure successfully completed.
Open, Fetch, and Close Explicitly
With explicit OPEN, FETCH, and CLOSE, the same cursor can be opened again with other values once it is closed.
Example:
declare
cursor c_staff (p_dept number) is
select last_name from employees where department_id = p_dept order by last_name;
v_name employees.last_name%type;
begin
for d in 50 .. 60 by 10 loop
open c_staff(d);
loop
fetch c_staff into v_name;
exit when c_staff%notfound;
dbms_output.put_line(d || ': ' || v_name);
end loop;
dbms_output.put_line(d || ' fetched ' || c_staff%rowcount);
close c_staff;
end loop;
end;
/Output:
50: Mehta 50: Reddy 50: Schmidt 50: Williams 50 fetched 4 60: Costa 60: Mensah 60: Okafor 60 fetched 3 PL/SQL procedure successfully completed.
%ROWCOUNT on the cursor counts the rows fetched so far, read here just before closing it.
Things to Know
- Opening a cursor that is already open raises CURSOR_ALREADY_OPEN; close it first.
- The cursor FOR loop opens, fetches, and closes by itself, and is the simplest form.
- For a cursor whose query text changes, use a cursor variable with OPEN FOR instead.
Related Guides
Conclusion
Cursor parameters make one explicit cursor serve many similar queries, with defaults and named notation. Pass them in a cursor FOR loop or with OPEN, and reopen a closed cursor with new values.
