Simple cursor

A simple PL/SQL cursor could be thought as a pointer to the result of a query.

The cursor declaration specifies how the query should be run. The we have to OPEN it, to actually perform the query; FETCH a single record from the cursor, to be able working on the data, and finally CLOSE it.

Once the cursor is open, we can check its status showed by a few attributes - in case of implicit cursor we check the attributes of SQL. Trying to read the status of a cursor not already initialized leads to a INVALID_CURSOR exception.

Here is a simple usage example on the countries table from the test HR Oracle schema:

declare
  cursor lc_countries is select * from countries; -- 1.
  lr_country countries%rowtype; -- 2.
begin
  open lc_countries; -- 3.
  dbms_output.put_line('Row fetched from cursor: '
    || lc_countries%rowcount); -- 4.

  fetch lc_countries into lr_country; -- 5.
  
  if lc_countries%found then -- 6.
    dbms_output.put_line('Row fetched');
  end if;
  
  dbms_output.put_line('The first fetched country is ' || lr_country.country_name);
  
  close lc_countries;
exception
  when invalid_cursor then -- 7.
    dbms_output.put_line('Cursor has not been opened yet');
end;

1. Cursor declaration.
2. Record used by the cursor.
3. First step to do is opening the cursor.
4. Attribute rowcount: it returns the number of rows already fetched from the cursor. In this case zero.
5. Then we fetch the cursor, to access a row by the record.
6. Attribute found: true if the fetch operation has been accomplished correctly.
7. The exception we could get when we try to access a cursor attribute.

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