©
This document uses PHP Chinese website manual Release
利用RAISE语句报告信息以及抛出错误。
RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ]; RAISE [ level ] condition_name [ USING option = expression [, ... ] ]; RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ]; RAISE [ level ] USING option = expression [, ... ]; RAISE ;
level选项声明了错误的严重性等级。 可能的级别有DEBUG(向服务器日志写信息), LOG(向服务器日志写信息,优先级更高), INFO, NOTICE, WARNING(把信息写到服务器日志以及转发到客户端应用, 优先级逐步升高), EXCEPTION(抛出一个错误,通常退出当前事务),默认的是EXCEPTION。 EXCEPTION会抛出一个错误(强制关闭当天事务),而其他级别仅仅是产生不同的优先级信息。 无论是将优先级别的信息是报告给客户端,还是写到服务器日志,亦或是二者都是, 都是由log_min_messages和client_min_messages配置变量控制的。 参阅Chapter 18获取更多细节
After level if any, you can write a format (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument's value. Write %% to emit a literal %. 在格式字符串里,%被下一个可选参数的外部表现形式代替。 要表示%字符必须双写(%%)。 参数可以是简单的变量或者表达式,而格式必须是一个简单的字符串文本。
在这个例子里,v_job_id的值将代替字符串中的%:
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
You can attach additional information to the error report by writing USING followed by option = expression items. The allowed option keywords are MESSAGE, DETAIL, HINT, and ERRCODE, while each expression can be any string-valued expression. MESSAGE sets the error message text (this option can't be used in the form of RAISE that includes a format string before USING). DETAIL supplies an error detail message, while HINT supplies a hint message. ERRCODE specifies the error code (SQLSTATE) to report, either by condition name as shown in Appendix A, or directly as a five-character SQLSTATE code.
该例子会强制退出事务,并返回如下提示:
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id';
下面两个例子在设置SQLSTATE方面具有相同的作用:
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:
RAISE division_by_zero; RAISE SQLSTATE '22012';
在这个语法中,USING可以来提供一个通用的错误信息,另一个例子是:
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
另一个变形是写RAISE USING或RAISE level USING, 然后将其他的所有东西都放在USING列中。
最后一个RAISE变形中没有任何参数。这种形式只能在BEGIN块的EXCEPTION字句中使用。 它的作用是将正在处理的错误放到下一个封闭的块中。
如果RAISE EXCEPTION中没有声明SQLSTATE的情形名称,那么缺省使用RAISE_EXCEPTION (P0001)。 如果没有声明信息文本,那么缺省将情形名称或SQLSTATE作为信息文本。
Note: When specifying an error code by SQLSTATE code, you are not limited to the predefined error codes, but can select any error code consisting of five digits and/or upper-case ASCII letters, other than 00000. It is recommended that you avoid throwing error codes that end in three zeroes, because these are category codes and can only be trapped by trapping the whole category.