标签

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

捕获状态变量信息

GET STACKED DIAGNOSTICS ....    GET DIAGNOSTICS ...

参考