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!
