Cursors

Cursor is the name given to SQL statements that are declared within a PLSQL code bloke. Like when you declare a function or procedure you can pass parameters into a cursor when opening it from the executable section of the bloke.

Cursors can be opened in a number or ways in PLSQL to retrieve the data :

CURSOR FOR LOOP
OPEN FETCH
SELECT INTO
EXECUTE IMMEDIATE

Example

We can show all of these examples in one block of code.

DECLARE
CURSOR c1 ( cp_emp_id NUMBER ) IS
SELECT first_name, surname
FROM employee
WHERE employee_id = cp_emp_id;
l_emp_id NUMBER;
l_first_name VARCHAR2(50);
l_surname VARCHAR2(50);
BEGIN
-- SELECT INTO example
SELECT employee_id
INTO l_emp_id
FROM employee
WHERE rownum < 2;
-- EXECUTE IMMEDIATE example
EXECUTE IMMEDIATE ‘SELECT employee_id
FROM employee
WHERE rownum < 2’
INTO l_emp_id;
-- CURSOR FOR LOOP example
FOR r1 IN c1 ( l_emp_id )
LOOP
l_first_name := r1.first_name;
l_surname := r1.surname;
END LOOP;
-- OPEN FETCH example
OPEN c1 ( l_emp_id );
FETCH c1 INTO l_first_name, l_surname;
CLOSE c1;
END;

The other main item to understand with cursors are the attributes – these will return a Boolean answer to the state of a given cursor:

%ISOPEN – TRUE if cursor is open, FALSE if closed.
%FOUND – TRUE if data has been found, FALSE if no row returned.
%NOTFOUND – TRUE is no row returned, FALSE if data has been found.
%ROWCOUNT – Returns number of rows fetched in cursor.

Leave a Reply

Your email address will not be published. Required fields are marked *