Exception Handling
Using the exception handling mechanism in stored procedures can improve code fault tolerance, but frequently catching and handling exceptions may lead to performance degradation. Each exception handling involves context creation and destruction, which consumes extra memory and resources. In addition, since exceptions are caught, error information will not be logged, making it more difficult to diagnose issues.
You are advised to use the EXCEPTION processing mechanism only when necessary and ensure that sufficient context information is passed to facilitate fault locating and rectification.
gaussdb=# create schema best_practices_for_procedure; CREATE SCHEMA gaussdb=# create table best_practices_for_procedure.tb1(id int, name varchar2(20)); CREATE TABLE gaussdb=# create unique index id1 on best_practices_for_procedure.tb1(id); CREATE INDEX -- Create a stored procedure with an exception. gaussdb=# create or replace procedure best_practices_for_procedure.proc1(oi_flag OUT int, os_msg OUT varchar) as begin oi_flag := 0; os_msg := 'insert into tb1 some data.'; for i in 1..10 loop if i = 5 then insert into best_practices_for_procedure.tb1 values(i - 1, 'name'|| i - 1);-- Intentionally create an error. end if; insert into best_practices_for_procedure.tb1 values(i, 'name'|| i); end loop; exception when others then oi_flag := 1; os_msg := SQLERRM; -- Pass the error message out. end; / CREATE PROCEDURE gaussdb=# declare oi_flag int; os_msg varchar(1000); begin best_practices_for_procedure.proc1(oi_flag, os_msg); if oi_flag = 1 then dbe_output.print_line('Exception for ' || os_msg); end if; end; / Exception for Duplicate key value violates unique constraint "id1". ANONYMOUS BLOCK EXECUTE 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.proc1() 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