Implicit Cursor

Overview

Implicit cursor operations, such as definition, open, value-grant, and close operations, are automatically performed by the system and do not need users to process. Users can use only attributes related to implicit cursors to complete operations. In workspace of implicit cursors, the data of the latest SQL statement is stored and is not related to explicit cursors defined by users.

Format call: SQL%

• INSERT, UPDATE, DELETE, and SELECT statements do not need defined cursors.
• Implicit cursor attributes are not affected by the commit or rollback operation.

Attributes

An implicit cursor has the following attributes:

• SQL%FOUND: Boolean attribute, which returns TRUE if the last fetch returns a row.
• SQL%NOTFOUND: Boolean attribute, which returns TRUE if the last fetch fails to return a row.
• SQL%ROWCOUNT: numeric attribute, which returns the number of records fetched from the cursor.
• SQL%ISOPEN: Boolean attribute, whose value is always FALSE. Close implicit cursors immediately after an SQL statement is run.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- Delete all employees in a department from the hr.staffs table. If the department has no employees, delete the department from the hr.department table.

gaussdb=# CREATE OR REPLACE PROCEDURE proc_cursor3() 
AS 
    DECLARE
    V_DEPTNO NUMBER(4) := 100;
    BEGIN
        DELETE FROM hr.staffs WHERE section_id = V_DEPTNO;
        -- Proceed based on cursor status.
        IF SQL%NOTFOUND THEN
        DELETE FROM hr.department WHERE section_id = V_DEPTNO;
        END IF;
    END;
/

gaussdb=# CALL proc_cursor3();
 proc_cursor3 
--------------

(1 row)

-- Drop the stored procedure and the temporary table.
gaussdb=# DROP PROCEDURE proc_cursor3;