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

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));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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