Passing parameters to cursor

A PL/SQL cursor could be thought like a sort of function that perform a query to the database and makes available the resulting recordset.

Being like a function, there should be a way of passing parameters to it.

Let's change the code we wrote in the previous post. There we have a loop on an explicit cursor that selected all the rows in the countries table.

Here we do something a bit smarter. We want to select just the countries in a specific region. And we want to be able to pass the region id to the cursor from the calling code.

Here is how we can do that:

declare
  cursor lc_countries(p_region number) is -- 1.
    select * from countries where region_id = p_region; -- 2.
  lr_country countries%rowtype;
begin
  open lc_countries(4); -- 3.

  loop
    fetch lc_countries into lr_country;
  
    if lc_countries%notfound then
      dbms_output.put_line('---');
      exit;
    end if;
  
    dbms_output.put_line(lr_country.country_name);
  end loop;
  
  close lc_countries;
exception
  when others then
    dbms_output.put_line('Something went wrong ' || sqlerrm);
    if lc_countries%isopen then -- 4.
      close lc_countries;
    end if;
end;

1. p_region is a parameter to the cursor.
2. We use the parameter passed to the cursor in the SQL statement.
3. With the open call to the cursor, we pass the requested parameter.
4. Another little change in the code about robustness: we ensure the cursor is open before closing it.

We can also specify a default value for the cursor parameter. If we rewrite the line (1) in this way:

  cursor lc_countries(p_region number := 1) is

We can accordingly rewrite line (3) to use the default value for the cursor instead of providing an explicit one:

open lc_countries();

Where the round brackets could be omitted.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.