PostgreSQL Oracle 相容性之 - DBMS_OUTPUT.PUT_LINE

标簽

PostgreSQL , Oracle , DBMS_OUTPUT.PUT_LINE , raise , notice

https://github.com/digoal/blog/blob/master/201808/20180818_01.md#%E8%83%8C%E6%99%AF 背景

在函數、存儲過程中需要進行一些debug，輸出一些過程變量的值時，PG中使用raise notice可以非常友善的得到。

https://github.com/digoal/blog/blob/master/201808/20180818_01.md#oracle 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.  
           

https://github.com/digoal/blog/blob/master/201808/20180818_01.md#postgresql 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://github.com/digoal/blog/blob/master/201808/20180818_01.md#%E5%8F%82%E8%80%83 參考

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