Cursors

Overview

A cursor is a pointer used to handle the result set of an SQL query, enabling row-by-row access and manipulation of these results and allowing individual processing of each row of data. Cursors are classified into explicit cursors and implicit cursors.

Cursor Attributes

%FOUND: returns TRUE if a FETCH operation on a cursor retrieves a row.

%NOTFOUND: returns TRUE if a FETCH operation does not retrieve any row (opposite of %FOUND).

%ISOPEN: specifies whether a cursor is opened.

%ROWCOUND: returns the number of rows affected by the cursor.

Explicit Cursors

Explicit cursors are explicitly defined and controlled by users. They are used to process SELECT queries that return multiple rows of data. Users need to explicitly define, open, use, and close cursors in PL/SQL.

• It has the following features:
  • Flexibility: Users can have precise control over query results. They can extract data row by row and process it according to service logic.
  • Scalability: Since explicit cursors are explicitly defined, it is easy to modify or add new processing logic.
  • Complexity: The code is more complex as users need to carefully manage the lifecycle of cursors and ensure that cursors are closed at a proper time to release resources.
  • Resource consumption: Although displaying cursors can improve performance in some cases, more system resources may be consumed in other cases. For example, frequently opening and closing cursors may increase the database overhead and affect the overall system performance.
• Application scenarios:

  This is suitable for scenarios where complex operations on result sets are required, such as row-by-row processing and data conversion.

Implicit Cursors

Implicit cursors are automatically managed by the database system. When SQL statements are executed, the database automatically creates an implicit cursor to process these operations.

Examples

• Explicit cursors

  -- Create a table and insert data.
  gaussdb=# CREATE TABLE tb_t1(c1 int,c2 int);
  CREATE TABLE
  
  gaussdb=# INSERT INTO tb_t1 VALUES (generate_series(1,10),generate_series(1,10));
  INSERT 0 10
  
  -- The following is an example of an explicit cursor:
  gaussdb=# DECLARE
      v_c1 int;
      v_c2 int;
      v_res int;
      -- Define a cursor.
      CURSOR v_cur IS SELECT * FROM tb_t1;
  BEGIN
      -- Open the cursor.
      OPEN v_cur;
      LOOP
          -- Use the cursor.
          FETCH v_cur INTO v_c1,v_c2;
          EXIT WHEN v_cur %notfound;
          v_res = mod(v_c1,2);  
  
          IF v_res = 1 THEN
              UPDATE tb_t1 SET c2 = v_c1+1 WHERE c1 = v_c1;
          END IF;
  
      END LOOP;
      -- Close the cursor.
      CLOSE v_cur; 
  END;
  /
  ANONYMOUS BLOCK EXECUTE
  
  -- View the table result.
  gaussdb=# SELECT * FROM tb_t1;
   c1 | c2 
  ----+----
    1 |  2
    2 |  2
    3 |  4
    4 |  4
    5 |  6
    6 |  6
    7 |  8
    8 |  8
    9 | 10
   10 | 10
  (10 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_t1;
  DROP TABLE

• Implicit cursors

  -- Create tables.
  gaussdb=# CREATE TABLE tb_t1(c1 int,c2 int);
  CREATE TABLE
  gaussdb=# CREATE TABLE tb_t2(c1 int,c2 int);
  CREATE TABLE
  
  -- Use an implicit cursor.
  gaussdb=# BEGIN    
      INSERT INTO tb_t1 VALUES (generate_series(1,4),generate_series(1,4));
      RAISE INFO '% data records are inserted into the tb_t1 table',SQL%ROWCOUNT;
  
      INSERT INTO tb_t2 VALUES (generate_series(1,6),generate_series(1,6));
      RAISE INFO '% data records are inserted into the tb_t2 table',SQL%ROWCOUNT;
  
      UPDATE tb_t1 SET c2 = c2*2 WHERE c1 > 4;
      RAISE INFO '% data records are modified in the tb_t1 table',SQL%ROWCOUNT;
  
      UPDATE tb_t2 SET c2 = c1*2 WHERE c1 > 4;
      RAISE INFO '% data records are modified in the tb_t2 table',SQL%ROWCOUNT;
  END;
  /
  INFO:  4 data records are inserted into the tb_t1 table.
  INFO:  6 data records are inserted into the tb_t2 table.
  INFO:  No record is modified in the tb_t1 table.
  INFO:  2 records are modified in the tb_t2 table.
  ANONYMOUS BLOCK EXECUTE
  
  -- View data in the tb_t1 and tb_t2 tables.
  gaussdb=# SELECT * FROM tb_t1;
   c1 | c2 
  ----+----
    1 |  1
    2 |  2
    3 |  3
    4 |  4
  (4 rows)
  
  gaussdb=# SELECT * FROM tb_t2;
   c1 | c2 
  ----+----
    1 |  1
    2 |  2
    3 |  3
    4 |  4
    5 | 10
    6 | 12
  (6 rows)
  
  -- Drop tables.
  gaussdb=# DROP TABLE tb_t1;
  DROP TABLE
  gaussdb=# DROP TABLE tb_t2;
  DROP TABLE