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);
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