Oracle编译时警告
Oracle 10g开始可以启用编译时警告来发现程序运行时异常。这些警告在编译时不足以抛出异常,但是却会在运行时造成错误或低性能。
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!
上一篇: PHP图片自动裁切应付不同尺寸的显示,_PHP教程
下一篇: 水平居中、垂直居中
推荐阅读
-
解决Python源码编译时提示:ModuleNotFoundError: No module named ‘distutils.command‘
-
如何关闭打开文件时提示的安全警告?打开文件安全警告取消四大方法详解
-
多种方法去掉打开文件时的安全警告对话框
-
当页面引入百度地图时,谷歌浏览器的控制台会弹出一个警告信息
-
Oracle用户密码含特殊字符时登陆失败问题
-
thinkphp中连接oracle时封装方法无法用的解决办法
-
vs2005编译时出现C2859错误该怎么办?
-
Oracle数据库根据时间范围查询时间范围内的年,月,日以及一天的24小时(实例)
-
检测硬盘时重新分配事件计数出现警告该怎么办?
-
MLSQL编译时权限控制示例详解