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.

No comments:

Post a Comment