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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot