Ad Code



PL/SQL - Types of Cursors : Explicit cursors, Implicit Cursors


Types of Cursors

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 
        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, 


UPDATE activity SET last_accessed := SYSDATE

WHERE UID = us er_id;


INSERT INTO activity_log (uid,last_accessed)