终止事务时,给出错误和提示信息:
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
719
CREATE OR REPLACE PROCEDURE proc_raise1(user_id in integer)
AS
BEGIN
RAISE EXCEPTION 'Noexistence ID --> %',user_id USING HINT = 'Please check your
user ID';
END;
/
call proc_raise1(300011);
--执行结果
ERROR:
Noexistence ID --> 300011
HINT:
Please check your user ID
两种设置SQLSTATE 的方式:
CREATE OR REPLACE PROCEDURE proc_raise2(user_id in integer)
AS
BEGIN
RAISE 'Duplicate user ID: %',user_id USING ERRCODE = 'unique_violation';
END;
/
\set VERBOSITY verbose
call proc_raise2(300011);
--执行结果
ERROR:
Duplicate user ID: 300011
SQLSTATE: 23505
如果主要的参数是条件名或者是SQLSTATE,可以使用:
RAISE division_by_zero;
RAISE SQLSTATE '22012';
例如:
CREATE OR REPLACE PROCEDURE division(div in integer, dividend in integer)
AS
DECLARE
res int;
BEGIN
IF dividend=0 THEN
RAISE division_by_zero;
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
720
RETURN;
ELSE
res := div/dividend;
RAISE INFO 'division result: %', res;
RETURN;
END IF;
END;
/
call division(3,0);
--执行结果
ERROR:
division_by_zero
或者另一种方式:
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
兼容O 模式下,支持使用语法EXCEPTION_INIT 自定义错误码SQLCODE:
declare
deadlock_detected exception;
pragma exception_init(deadlock_detected, -1);
begin
if 1 > 0 then
raise deadlock_detected;
end if;
exception
when deadlock_detected then
raise notice
'sqlcode:%,sqlstate:%,sqlerrm:%',sqlcode,sqlstate,sqlerrm;
end;
/
--执行结果
NOTICE:
sqlcode:-1,sqlstate:-1,sqlerrm: 1: non-GaussDB Exception