Implicit cursor

The simple cursor showed in the previous post is an explicit one: we declare it with its associated SQL statement in the DECLARE section of our PL/SQL block, then we explicitely OPEN, FETCH and CLOSE it.

When we write an implicit cursor, we rely on the PL/SQL compiler to do all the under the curtain job and we just get the result in a local variable.

In this example we use an implicit cursor to fetch a row in a local record:

declare
  lr_country countries%rowtype; -- 1.
begin
  select *
  into lr_country -- 2.
  from countries
  where country_id = 'BE';
  
  dbms_output.put_line(lr_country.country_name || ' ' || lr_country.region_id);
exception
  when no_data_found then -- 3.
    dbms_output.put_line('no data found');
  when too_many_rows then -- 4.
    dbms_output.put_line('more than a row found');
end;

1. Declare the record to be used by the implicit cursor.
2. The SQL statement is used by the PL/SQL compiler to generate an implicit cursor that would fetch its first row in our local record variable.
3. If there is not such a row in the table, a exception is raised.
4. Changing the WHERE clause in the SELECT statement to a "like 'B%'" we are going to get more than one row, and so a exception will be raised.

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