俘獲錯誤
更新時間 2025-02-14 10:22:24
最近更新時間: 2025-02-14 10:22:24
分享文章
本頁介紹天翼云TeleDB數據庫存儲過程開發的錯誤俘獲處理和獲取錯誤相關信息的語法。
錯誤俘獲處理
teledb=# CREATE OR REPLACE PROCEDURE p_exception(a_id integer,a_nc text) AS
$$
BEGIN
INSERT INTO t_exception VALUES(a_id,a_nc);
RETURN ;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE '執行出錯';
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=#
teledb=# CALL p_exception(1,'Teledb');
CALL
teledb=# CALL p_exception(1,'Teledb');
NOTICE: 執行出錯
CALL獲取錯誤相關信息
teledb=# CREATE OR REPLACE PROCEDURE p_exception_error(a_id integer,a_nc text) AS
$$
DECLARE
v_sqlstate text;
v_context text;
v_message_text text;
BEGIN
INSERT INTO t_exception VALUES(a_id,a_nc);
RETURN ;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
v_message_text = MESSAGE_TEXT,
v_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '錯誤代碼 : %',v_sqlstate;
RAISE NOTICE '出錯信息 : %',v_message_text;
RAISE NOTICE '發生異常語句 : %',v_context;
raise notice '錯誤代碼 : % \n出錯信息 : % 發生異常語句 : %',v_sqlstate ,v_message_text,v_context;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_exception_error(2,'Teledb');
CALL
teledb=# CALL p_exception_error(2,'Teledb');
NOTICE: 錯誤代碼 : 23505
NOTICE: 出錯信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx"
NOTICE: 發生異常語句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
NOTICE: 錯誤代碼 : 23505 \n出錯信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx" 發生異常語句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
CALL
teledb=#
