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:
cursor lc_countries is select * from countries; -- 1.
lr_country countries%rowtype; -- 2.
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('The first fetched country is ' || lr_country.country_name);
when invalid_cursor then -- 7.
dbms_output.put_line('Cursor has not been opened yet');
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.