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!