Postgresql中有关plpgsql异常处理的异常一切(RAISE EXCEPTION)
1 抛出异常
主要列出实例,语法比较简单
语法
现在PL中支持使用RAISE语法抛出异常,处理具体支持下面五种语法:
1 RAISE [ level ] 'format' [,实例 expression [, ... ]] [ USING option = expression [, ... ] ];2 RAISE [ level ] condition_name [ USING option = expression [, ... ] ];3 RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];4 RAISE [ level ] USING option = expression [, ... ];5 RAISE ; -- 特殊,只能在EXCEPTION语法块中使用,异常重新抛出catch的处理异常。
- level
- 可选
DEBUG
,实例LOG
,INFO
,NOTICE
,WARNING
,EXCEPTION
- 其中
DEBUG
到WARNING
都不会产生错误,只会打印日志,异常日志级别按level输出,处理由log_min_messages、实例client_min_messages参数决定是异常否输出。 - 其中
EXCEPTION
会产生错误,处理中断程序执行,实例错误如果不被捕获会被抛到上一层。异常
- 可选
- 抛出指定类型的处理异常
- 方式一:
RAISE SQLSTATE
- 方式二:
RAISE condition_name
- 方式三:
RAISE 'text' USING ERRCODE = 'unique_violation'
- 方式一:
抛出异常的实例展示
文本拼接
do $g$DECLARE v_job_id int := 100;BEGIN RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;END;$g$;
执行结果
postgres=# do $g$postgres$# DECLAREpostgres$# v_job_id int := 100;postgres$# BEGINpostgres$# RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;postgres$# END;postgres$# $g$;NOTICE: Calling cs_create_job(100)DO
使用Hint
级别可任选,这里使用EXCEPTION没人处理,实例所以抛到顶层报错。
do $g$DECLARE user_id int := 100;BEGIN RAISE EXCEPTION 'Nonexistent ID -->%', user_id USING HINT = 'Please check your user ID';END;$g$;
执行结果
postgres=# do $g$postgres$# DECLAREpostgres$# user_id int := 100;postgres$# BEGINpostgres$# RAISE EXCEPTION 'Nonexistent ID -->%', user_idpostgres$# USING HINT = 'Please check your user ID';postgres$# END;postgres$# $g$;ERROR: Nonexistent ID -->100HINT: Please check your user IDCONTEXT: PL/pgSQL function inline_code_block line 5 at RAISE
抛出指定类型异常
1
do $g$DECLARE user_id int := 100;BEGIN RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';END;$g$;-- ERROR: Duplicate user ID: 100-- CONTEXT: PL/pgSQL function inline_code_block line 5 at RAISE
2
do $g$DECLARE user_id int := 100;BEGIN RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';END;$g$;-- ERROR: Duplicate user ID: 100-- CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
3
do $g$BEGIN RAISE division_by_zero;END;$g$;-- ERROR: division_by_zero-- CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE
4
do $g$BEGIN RAISE SQLSTATE '22012';END;$g$;-- ERROR: 22012-- CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE
5
do $g$DECLARE user_id int := 100;BEGIN RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;END;$g$;-- ERROR: Duplicate user ID: 100-- CONTEXT: PL/pgSQL function inline_code_block line 5 at RAISE
2 捕获处理异常
捕获&&处理异常
语法
[ <
- condition:异常名字,有两种使用方式,所有异常列表在PG文档中可以找到《Appendix A. PostgreSQL Error Codes》,下面列举一部分。
- 异常名:
WHEN division_by_zero THEN
,规律:小写下划线连接关键字 - 异常码:
WHEN SQLSTATE '22012' THEN ...
,规律:5位字符,数字和任意字母组成 - OTHERS:匹配一些异常
- 异常名:
- handler_statements:异常处理语法块,如果这里面再产生异常不会被当前的EXCEPTION捕获,会直接抛到上层。
- 特殊变量:SQLSTATE、SQLERRM只在EXCEPTION语法块中生效,可以打印错误码和错误信息。
- 例如:division_by_zero异常处理时,
sqlerrm="division by zero"
,sqlstate=22012
- 例如:division_by_zero异常处理时,
部分condition实例
Class 00 — Successful Completion 00000 successful_completionClass 01 — Warning 01000 warning 0100C dynamic_result_sets_returned 01008 implicit_zero_bit_padding 01003 null_value_eliminated_in_set_function 01007 privilege_not_granted 01006 privilege_not_revoked 01004 string_data_right_truncation 01P01 deprecated_featureClass 22 — Data Exception 22000 data_exception 2202E array_subscript_error 22021 character_not_in_repertoire 22008 datetime_field_overflow 22012 division_by_zero 22005 error_in_assignment 2200B escape_character_conflict 22022 indicator_overflow 22015 interval_field_overflow
异常捕获实例
1 系统异常
do $g$DECLARE user_id int := 100;BEGIN user_id = user_id / 0;EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'caught division_by_zero';END;$g$;-- NOTICE: caught division_by_zero
2 主动产生异常:没给错误码使用SQLSTATE P0001
没显示指定错误码,使用P0001、raise_exception:
do $g$DECLARE user_id int := 100; text_var1 text; text_var2 text; text_var3 text; text_var4 text; text_var5 text;BEGIN RAISE EXCEPTION 'Nonexistent ID -->%', user_id USING HINT = 'Please check your user ID';EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT, text_var4 = PG_EXCEPTION_CONTEXT, text_var5 = RETURNED_SQLSTATE; raise notice '%', text_var1; raise notice '%', text_var2; raise notice '%', text_var3; raise notice '%', text_var4; raise notice '%', text_var5;END;$g$;-- NOTICE: Nonexistent ID -->100-- NOTICE: -- NOTICE: Please check your user ID-- NOTICE: PL/pgSQL function inline_code_block line 10 at RAISE-- NOTICE: P0001
3 主动抛出异常:给定错误码
do $g$DECLARE user_id int := 100; text_var1 text; text_var2 text; text_var3 text; text_var4 text; text_var5 text;BEGIN RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, text_var2 = PG_EXCEPTION_DETAIL, text_var3 = PG_EXCEPTION_HINT, text_var4 = PG_EXCEPTION_CONTEXT, text_var5 = RETURNED_SQLSTATE; raise notice '%', text_var1; raise notice '%', text_var2; raise notice '%', text_var3; raise notice '%', text_var4; raise notice '%', text_var5;END;$g$;-- NOTICE: Duplicate user ID: 100-- NOTICE: -- NOTICE: -- NOTICE: PL/pgSQL function inline_code_block line 10 at RAISE-- NOTICE: 23505
4 特殊变量:注意sqlerrm会被异常文本替换掉,sqlstate永远是预定义好的错误码
do $g$DECLARE user_id int := 100;BEGIN RAISE EXCEPTION 'Nonexistent ID -->%', user_id USING HINT = 'Please check your user ID';EXCEPTION WHEN OTHERS THEN raise notice 'sqlstate: %', sqlstate; raise notice 'sqlerrm: %', sqlerrm;END;$g$;-- NOTICE: sqlstate: P0001-- NOTICE: sqlerrm: Nonexistent ID -->100