• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Stored Procedures
  5. Cursors
  6. Implicit Cursor

Implicit Cursor

Implicit cursors are automatically set by the system for non-query statements such as modify or delete operations, along with their workspace. Implicit cursors are named SQL, which is defined by the system.

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%

NOTE:

INSERTUPDATEDELETE, and SELECT statements do not need defined cursors.

Attribute

An implicit cursor has the following attributes:

  • SQL%FOUND attribute: returns TRUE if the last fetch returns a row.
  • SQL%NOTFOUND attribute: works opposite to the %FOUND attribute.
  • SQL%ROWCOUNT attribute: returns the number of records fetched from the cursor.
  • SQL%ISOPEN attribute: always takes FALSE. Close implicit cursors immediately after an SQL statement is run.

Example

-- Delete all employees in a department from the EMP table. If the department has no member, delete the department from the DEPT table.
CREATE TABLE hr.staffs_t1AS TABLE hr.staffs;
CREATE TABLE hr.sections_t1AS TABLE hr.sections;

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.sections_t1 WHERE section_ID = V_DEPTNO;
        END IF;
    END;
/

CALL proc_cursor3();

-- Delete the stored procedure and the temporary table:
DROP PROCEDURE proc_cursor3;
DROP TABLE hr.staffs_t1;
DROP TABLE hr.sections_t1;