Using Explicit Cursors  «Prev 

Fetching the records From a Cursor using PL/SQL

Syntax and example for fetching the records 
FETCH <cursor_name> INTO <record or variable-list>;
Syntax and example for fetching the records

cursor_name Name of the cursor
record or variable list This is a PL/SQL data structure into which the next record of the active set of records is copied.

pets Name of the cursor
pet_name1, pet_count1 This is a PL/SQL data structure into which the next record of the active set of records is copied.

Oracle PL/SQL Programming

Fetching the Same Cursor Into Different Variables

DECLARE
CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
name1 employees.last_name%TYPE;
name2 employees.last_name%TYPE;
name3 employees.last_name%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;
END;

If you fetch past the last row in the result set, the values of the target variables are undefined. Eventually, the FETCH statement fails to return a row. When that happens, no exception is raised. To detect the failure, use the cursor attribute %FOUND or %NOTFOUND.

%FOUND Attribute

%FOUND Attribute: Has a DML Statement Changed Rows?
Until a SQL data manipulation statement is executed, %FOUND yields NULL.
Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows, or a SELECT INTO statement returned one or more rows.
Otherwise, %FOUND yields FALSE.
In Example 6–6, a developer uses %FOUND to insert a row if a delete succeeds.

Example 6–6 Using SQL%FOUND

CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
dept_no NUMBER(4) := 270;
BEGIN
DELETE FROM dept_temp WHERE department_id = dept_no;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
END IF;
END;
/