Help Center/ GaussDB/ Centralized_8.x/ Stored Procedure/ Cursors/ Implicit Cursor
Updated on 2024-06-03 GMT+08:00
View PDF

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%

  • INSERT, UPDATE, DELETE, and SELECT statements do not need defined cursors.
  • In A-compatible mode, if the GUC parameter behavior_compat_options is set to compat_cursor, implicit cursors are valid across stored procedures.
  • After SMP-related GUC parameters are enabled (the GUC parameter query_dop is set to a value greater than 1 and plsql_beta_feature is set to enable_plsql_smp), query statements that do not involve INSERT, UPDATE, and DELETE in cursors can be executed on SMP.
  • Implicit cursor attributes are not affected by the commit\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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
-- 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;
/
CREATE PROCEDURE
gaussdb=# CALL proc_cursor3();
 proc_cursor3 
--------------

(1 row)

-- Delete the stored procedure and the temporary table.
gaussdb=# DROP PROCEDURE proc_cursor3;
DROP PROCEDURE
-- If SELECT implicit cursors need to be executed concurrently, enable the following GUC parameters:
gaussdb=# SET query_dop=4;
SET
gaussdb=# SET sql_beta_feature= 'enable_plsql_smp';

-- Select the name of the employee whose ID is 1 from the employee table hr.staffs.
gaussdb=# CREATE OR REPLACE PROCEDURE prc_cursor_smp()
AS
    name varchar(20);
BEGIN
   SELECT first_name FROM hr.staffs WHERE staff_id = 1 INTO name;
   dbe_output.print_line('result is: '|| name);
END;
/
CREATE PROCEDURE
-- Execute the stored procedure.
gaussdb=# CALL prc_cursor_smp();
result is: Tom
 prc_cursor_smp 
----------------

(1 row)
Parent topic: Cursors