There are basically two types of cursors in PL/SQL:
Types of Cursors
1) Explicit Cursors
2) Implicit Cursors
Explicit Cursors
Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification, Use OPEN, FETCH, and CLOSE in the execution or exception sections of programs.
Cursors are controlled via four command statements. They are:
| Command | Description |
| DECLARE | Defines the name and structure of the cursor together with the SELECT statement that will populate the cursor with data. The query is validated but not executed. |
| OPEN | Executes the query that populates the cursor with rows. |
| FETCH | Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch. |
| CLOSE | Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data. |
1) Declaring Explicit Cursors: To use an explicit cursor, one must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:
i) A cursor without parameters, such as:
CURSOR company_cur
IS
SELECT company_id FROM company;
ii) A cursor that accepts arguments through a parameter list:
CURSOR company_cur (id_in IN NUMBER) IS
SELECT name FROM company
WHERE company_id = id_in;
iii) A cursor header that contains a RETURN clause in place of the
SELECT statement:
CURSOR company_cur (id_in IN NUMBER)
RETURN company%ROWTYPE IS
SELECT * FROM company;
Implicit Cursors
Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors.
These statements include INSI-flR.T, UP ATE, DELETE, and SELECT 0 statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENesi FETC} ed, or CLOSEd.
SELECT statements handle the %FOUND and %NQTFOUND differently from explicit cursors. When an implicit SELECT statement not return any row s, PUS QL immediately raises the exception and control passes to the exception section. When an SELECT returns more than one row, PUS L immediately raises the TOO_MANY_ROWS exception and control passes to the exception
Implicit cursor attributes are referenced via the SQL cursor.
For example,
BEGIN
UPDATE activity SET last_accessed
:= SYSDATE
WHERE UID = us er_id;
IF SQL%NOTFOUND THEN
INSERT INTO activity_log
(uid,last_accessed)
VALUES (user_id,SYSDATE);
END IF
END;
