A cursor acts logically as a pointer into a result set. You can move the cursor through the result set, processing each row, until you determine you are at the end of the result set. There are three types of syntax associated with cursors: creating the cursor, fetching with the cursor, and closing the cursor. In addition, there are a number of attributes of a cursor you can use in your logical comparisons. The following are the types of Cursors in Oracle:
Explicit Cursors
Explicit Cursors are cursors that you declare and use.
Implicit Cursors
PL/SQL allows you to include SQL statements, including SELECT statements, as a part of your code without declaring a cursor, that is called an implicit cursor.
Ref Cursors
A cursor references a result set. The REF CURSOR allows you to pass a cursor reference from one PL/SQL program unit to another. In other words, it allows you to create a variable that will receive a cursor and enable access to its result set, but in this blog I am giving examples for only Explicit and Implicit Cursors, I will give example for Ref Cursors and Dynamic Cursor in another blog.
An example of Explicit Cursor:
DECLARE
nemployeeid NUMBER;
dstartdate DATE;
denddate DATE;
sjobid VARCHAR2 (20);
-- declare cursor
CURSOR curjob
IS
SELECT employee_id,
start_date,
end_date,
job_id
FROM hr.job_history;
BEGIN
OPEN curjob;
LOOP
FETCH curjob
INTO nemployeeid, dstartdate, denddate, sjobid;
EXIT WHEN curjob%NOTFOUND;
DBMS_OUTPUT.put_line( 'Employee '
|| nemployeeid
|| 'had job '
|| sjobid
|| ' for '
|| (denddate - dstartdate)
|| ' days.');
END LOOP;
CLOSE curjob;
END;
/
Same example is given below for explicit cursor but with For Loop, the For Loop cursors are more smart as there is no need to declare variables to fetch values in them and no need to open or close or to check whether the pointer is at end of the cursor. Here is the example:
DECLARE
CURSOR curjob
IS
SELECT employee_id,
start_date,
end_date,
job_id
FROM hr.job_history;
BEGIN
FOR jh_rec IN curjob
LOOP
DBMS_OUTPUT.put_line( '‘Employee '
|| jh_rec.employee_id
|| ' had job '
|| jh_rec.job_id
|| ' for '
|| ( jh_rec.end_date
- jh_rec.start_date
|| ' days.'));
END LOOP;
END;
/
An Implicit Cursor example:
DECLARE
nempno NUMBER;
CURSOR curjob
IS
SELECT employee_id,
start_date,
end_date,
job_id
FROM hr.job_history;
BEGIN
-- below sql query is the type of Implicit Cursor
SELECT COUNT ( * ) INTO nempno FROM hr.job_history;
DBMS_OUTPUT.put_line (
'There are ' || nempno || ' employee history records.');
FOR jh_rec IN curjob
LOOP
DBMS_OUTPUT.put_line( '‘Employee '
|| jh_rec.employee_id
|| ' had job '
|| jh_rec.job_id
|| ' for '
|| ( jh_rec.end_date
- jh_rec.start_date
|| ' days.'));
END LOOP;
END;
/