俘獲錯誤
更新時間 2025-02-05 09:36:46
最近更新時間: 2025-02-05 09:36:46
分享文章
本頁介紹天翼云TeleDB數據庫PL/pgsql控制結構中的錯誤俘獲處理和相關信息。
錯誤俘獲處理
teledb=# CREATE TABLE t_exception (id integer not null,nc text);
CREATE TABLE
teledb=# create unique index t_exception_id_uidx on t_exception using btree(id);
CREATE INDEX
teledb=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT AS
teledb-# $$
teledb$# BEGIN
teledb$# INSERT INTO t_exception VALUES(a_id,a_nc);
teledb$# RETURN '';
teledb$# EXCEPTION WHEN OTHERS THEN
teledb$# RETURN '執行出錯';
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27(1,'teledb_pg');
f27
-----
(1 row)
teledb=# SELECT f27(1,'teledb_pg');
f27
----------
執行出錯
(1 row)
獲取錯誤相關信息
teledb=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT AS
teledb-# $$
teledb$# DECLARE
teledb$# v_sqlstate text;
teledb$# v_context text;
teledb$# v_message_text text;
teledb$# BEGIN
teledb$# INSERT INTO t_exception VALUES(a_id,a_nc);
teledb$# RETURN '';
teledb$# EXCEPTION WHEN OTHERS THEN
teledb$# GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
teledb$# v_message_text = MESSAGE_TEXT,
teledb$# v_context = PG_EXCEPTION_CONTEXT;
teledb$# RAISE NOTICE '錯誤代碼 : %',v_sqlstate;
teledb$# RAISE NOTICE '出錯信息 : %',v_message_text;
teledb$# RAISE NOTICE '發生異常語句 : %',v_context;
teledb$# RETURN '錯誤代碼 : '||v_sqlstate || '\n出錯信息 : '||v_message_text|| '發生異常語句 : '||v_context;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27(1,'teledb_pg');
NOTICE: 錯誤代碼 : 23505
NOTICE: 出錯信息 : node:16385, error 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 f27(integer,text) line 7 at SQL statement
f27
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
錯誤代碼 : 23505\n出錯信息 : node:16385, error duplicate key value violates unique constraint "t_exception_id_uidx"發生異常語句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"+
PL/pgSQL function f27(integer,text) line 7 at SQL statement
(1 row)
