Updated on 2025-09-04 GMT+08:00

Cursors

In stored procedures, cursors are important resources. Improper use of cursors may cause the following problems:

  • Unclosed cursors will consume system resources, and a large number of cursors that are not closed promptly will severely impact database memory and performance, especially in high-concurrency or iterative operations.

Therefore, you are advised to close the cursor immediately after it is used in a stored procedure.

gaussdb=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA

gaussdb=# CREATE TABLE best_practices_for_procedure.tb1 (a int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

gaussdb=# INSERT INTO best_practices_for_procedure.tb1 VALUES (1),(2),(3);
INSERT 0 3

-- Create a stored procedure that uses a cursor.
gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro_cursor () AS
    my_cursor CURSOR FOR SELECT *FROM best_practices_for_procedure.tb1;
    a int;
BEGIN
    OPEN my_cursor;
    FETCH my_cursor INTO a;
    CLOSE my_cursor; -- Close the cursor promptly.
END;
/
CREATE PROCEDURE

gaussdb=# CALL best_practices_for_procedure.pro_cursor();
 pro_cursor
------------

(1 row)

gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table best_practices_for_procedure.tb1
drop cascades to function best_practices_for_procedure.pro_cursor()
DROP SCHEMA