PL/SQL Cursors
There are a few types of cursors but over here, I'm going to cover 2 main types of cursors:
a) implicit cursors
- oracle creates implicit cursors each time you run a select INTO , delete, insert or update statement. Note that they are all single row operations
- you are using an implicit cursor if the sql you are running is either select, insert or update
- the sql is being run immediately and OPEN,FETCH,CLOSE are not needed (and must not be done)
- Since implicit cursors deal with only exactly one row, error handling is simplifier to two conditions:
a) when there's no rows returned
-- WHEN NO_DATA_FOUND THEN (syntax)
b) when there's more than 1 row returned
-- WHEN TOO_MANY_ROWS THEN (syntax)
-cursor attributes can also be used:
a) %FOUND,
b) %NOTFOUND
c) %ROWCOUNT
d) %ISOPEN
Example:
DECLARE
v_title book.title%TYPE
BEGIN
SELECT title
INTO v_title
FROM book
WHERE id = 1000;
END
b) explicit cursors
- this type of cursor is created by defining the cursor (based on the format below)
DECLARE CURSOR <cursor name> [ (parameters) ]
[ RETURN argument(s) ]
IS
--sql stmt
BEGIN
-- this is when you do your for-loop OR manual open, fetch, check for exceptions, processing and close
END
END
An example of return would be :
CURSOR get_current_employee
RETURN employee%ROWTYPE
IS
SELECT
*
FROM
employee
WHERE
status = 'current'
BEGIN
…
END
Observe that in comparison with implicit cursors, the sql is being defined in the "IS" section and it needs to be managed manually (ie. use for loop OR fetch, open , check and close).
------
Basic way of working with a cursor - declare , open, fetch data and close it.
OPEN <cursor_name>
[( <parameter_value_1, <parameter_value_2>,... <parameter_value_N> )];
LOOP
-- loop until you manually EXIT;
END LOOP;
FETCH <cursor_name> INTO <variable_name_1>, <variable_name_2>,... <variable_name_N>;
CLOSE <cursor_name>;
EXIT;
Better way (optimised) - use a for loop:
DECLARE
CURSOR <cursor name> ( <arg1> IN <existing column>%TYPE )
--put sql here. It can use <arg1>
-- put all variable declarations here
BEGIN
FOR item IN <cursor name> LOOP
<cursor name>(arg1);
END LOOP;
END
With the FOR-LOOP, you will not need to worry about doing the required operations: OPEN, FETCH and CLOSE as it's handled by the for-loop.
It is also good practice to use a loop label which you would sandwich between '<<' and '>>'.
Example:
<< get_student_cohorts >>
Explicit cursor attributes:
1) %NOTFOUND-Within an explicit cursor, we can check if it's returned any data or not by using the %NOTFOUND method.
2) %FOUND - returns true if the current cursor's fetch does bring back a valid row (not empty)
3) %ROWCOUNT - number of rows being fetched to date. This is useful when you're fetching rows out using a for loop and want to find out what iteration it is. After each fetch, the value of this variable will be incremented
4) %ISOPEN - checks if the current cursor is open or not
5) %BULK_ROWCOUNT, %BULK_EXCEPTIONS - for use with FORALL statements
To use it, append it to the cursor name after it's been opened.
Example:
DECLARE CURSOR get_employee_id ( employee_name AS varchar2 )
IS
SELECT id
FROM employee
WHERE name = employee_name
v_employee_name varchar2 := 'Gordon Yeong';
v_employee_id employee.ID%TYPE;
begin
open get_employee_id(v_employee_name);
fetch get_employee_id
into v_employee_id;
if get_employee_id%notfound then
v_employee_id := 92; -- this is how I set a default value in case the cursor does not return anything
end if
close get_employee_id;
exception
when OTHERS then
raise_application_error( -20001, SQLERRM || ' on select of employee id' )
end;
end
