Oracle編譯時間警告

Oracle編譯時間警告

Compiler Warnings 編譯器警告

Oracle 10g allows you to enable compile-time warnings that are useful to identify potential run-time problems in your programs. These warnings are not serious enough to raise an exception at compile time, but may cause run-time errors or poor performance.
To enable these warnings globally for your database, the administrator needs to set the database initialization parameter plsql_warnings either in the parameter file or dynamically with an ALTER SYSTEM SET PLSQL_WARNINGS statement. To enable warnings in only your session, use an ALTER SESSION SET PLSQL_WARNINGS statement. The setting string is a comma delimited list of settings.

Oracle 10g開始可以啟用編譯時間警告來發現程式運行時異常。這些警告在編譯時間不足以拋出異常，但是卻會在運行時造成錯誤或低效能。
需要DBA全域開啟編譯時間警告時，可在參數檔案指定參數plsql_warnings或者通過ALTER SYSTEM SET PLSQL_WARNINGS語句動態設定。
如果只是在會話中啟用可使用ALTER SESSION SET PLSQL_WARNINGS語句。
該設定由逗號分隔。

The syntax for each setting is:
文法如下：

'[ENABLE | DISABLE | ERROR]:[ALL | SEVERE | INFORMATIONAL | PERFORMANCE | warning_number]'
To enable all warning messages execute:

例如：

ALTER SYSTEM SET plsql_warnings = 'enable:all';
To enable all severe and performance messages execute:  --啟用所有嚴重和效能警告

ALTER SYSTEM SET plsql_warnings = 'enable:severe'
        ,'enable:performance';
To enable all warning messages except message 06002, execute:  --啟用除了06002以外所有警告資訊

ALTER SYSTEM SET plsql_warnings = 'enable:all'
        ,'disable:06002';
Alternatively, you can use the built-in package dbms_warnings to set or view your warning setting. For example, to see what the current setting is, execute:

可選的，你可以使用內建包dbms_warnings來設定或者查看警告設定。例如：查看當前警告設定
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_WARNING.GET_WARNING_SETTING_STRING());
END;
/

DISABLE:ALL
--預設設定

 

The warning error codes all begin with a ‘PLW-‘. The SEVERE errors are in the range 05000 to 05999. The INFORMATIONAL errors are in the range 06000 to 06999. The PERFORMANCE errors are in the range 07000 to 07249. On UNIX systems, a text file with the all of error codes, together with their cause and action can be found in $ORACLE_HOME/plsql/mesg/plwus.msg or a similar filename (plw??.msg) if the locale is not US.
警告錯誤碼以'PLW-'開頭；
嚴重錯誤碼範圍：05000到05999;
報告錯誤碼範圍：06000到06999;
效能錯誤碼範圍：07000到07249;
UNIX系統中在以下檔案中包含所有錯誤代號以及原因和處理方法。$ORACLE_HOME/plsql/mesg/plwus.msg

An example of compiler warnings appears below:
來看兩個出現編譯警告的例子：

--1 不作用的代碼（dead code）：
SQL> CREATE OR REPLACE PROCEDURE dead_code IS
  2    x NUMBER := 10;
  3  BEGIN
  4    IF x = 10 THEN  -- always TRUE
  5        x := 20;
  6    ELSE
  7        x := 100; -- dead code
  8    END IF;
  9  END dead_code;
 10  /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE DEAD_CODE:

LINE/COL ERROR
-------- -----------------------------------------------
7/7      PLW-06002: Unreachable code

 

--2 如何加固我們的函數傳回值邏輯
說明：結構化編程應始終做到：One way in, one way out. 不要試圖到處挖坑，最後坑的是自己。

考慮以下代碼：
CREATE OR REPLACE FUNCTION status_desc (
  cd_in IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF cd_in = 'C'
      THEN RETURN 'CLOSED';
  ELSIF cd_in = 'O'
      THEN RETURN 'OPEN';
  ELSIF cd_in = 'A'
      THEN RETURN 'ACTIVE';
  ELSIF cd_in = 'I'
      THEN RETURN 'INACTIVE';
  END IF;
END;

編譯是無警示，表面看也沒啥大問題是吧？那我執行以下語句呢？
BEGIN
  DBMS_OUTPUT.PUT_LINE (status_desc ('X'));
END;
/

ORA-06503: PL/SQL: Function returned without value

問題還不小呢！! 下面啟用編譯時間警告來提早發現問題！

ALTER SESSION SET plsql_warnings ='ENABLE:5005'
/

ALTER FUNCTION status_desc COMPILE
/

PLW-05005: subprogram STATUS_DESC returns without value at line 15

警告是有了，但是函數依然可以被執行！那怎麼行，這是有問題的程式！下面啟用更嚴格的警告阻止程式編譯成功！
ALTER SESSION SET plsql_warnings ='ERROR:5005'
/

ALTER FUNCTION status_desc COMPILE
/

PLS-05005: subprogram STATUS_DESC returns without value at line 15

這下OK了，程式編譯返回了嚴重警告代碼PLS-05005，無法編譯通過了！
接下來要乾的就是修複代碼了。That's it!