作者
digoal
日期
2018-08-18
标签
PostgreSQL , Oracle , DBMS_OUTPUT.PUT_LINE , raise , notice
背景
在函数、存储过程中需要进行一些debug，输出一些过程变量的值时，PG中使用raise notice可以非常方便的得到。
Oracle
put_line在存储过程、函数中通常被用于调试，输出一些变量，时间值。
```
CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
CURSOR emp_cursor IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11, 2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal
+ emp_record.comm;
DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
'; Wages = '|| TO_CHAR(total_wages)); / Debug line /
counter := counter + 1; / Increment debug counter /
END LOOP;
/ Debug line /
DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
TO_CHAR(total_wages));
RETURN total_wages;
END dept_salary;
```
Assume the EMP table contains the following rows:
EMPNO SAL COMM DEPT
----- ------- -------- -------
1002 1500 500 20
1203 1000 30
1289 1000 10
1347 1000 250 20
Assume the user executes the following statements in SQL*Plus:
SET SERVEROUTPUT ON
VARIABLE salary NUMBER;
EXECUTE :salary := dept_salary(20);
The user would then see the following information displayed in the output pane:
```
Loop number = 1; Wages = 2000
Loop number = 2; Wages = 3250
Total wages = 3250
PL/SQL procedure successfully executed.
```
PostgreSQL
PostgreSQL plpgsql存储过程，其他存储过程语言同样有类似的用法（plr, plpython, plperl, pltcl, pljava, pllua等）
以plpgsql为例，使用raise notice即可用于输出调试信息，例如
```
postgres=# do language plpgsql $$
declare
begin
raise notice 'now: %, next time: %', now(), now()+interval '1 day';
end;
$$;
输出
NOTICE: now: 2018-08-18 16:15:30.209386+08, next time: 2018-08-19 16:15:30.209386+08
DO
Time: 0.335 ms
```
捕获状态变量信息
https://www.postgresql.org/docs/devel/static/plpgsql-statements.html
https://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
```
GET STACKED DIAGNOSTICS ....
GET DIAGNOSTICS ...
```
参考
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#BABEHIEG
https://www.postgresql.org/docs/11/static/plpgsql-errors-and-messages.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
