文章目录
1.DBMS_OUTPUT包
1.1 包DBMS_OUTPUT包含的过程
SQL> desc dbms_output;
PROCEDURE DISABLE
PROCEDURE ENABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE GET_LINE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
LINES TABLE OF VARCHAR2(32767) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE GET_LINES
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
LINES DBMSOUTPUT_LINESARRAY OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT_LINE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
1.1.1 ENABLE和DISABLE过程
- ENABLE过程:启动文本消息打印功能
- DISABLE禁止打印文本消息,且清空缓存
实例1:
首先禁止打印输出后,再启用,查看输出情况:
SQL> declare
2 i number := #
3 begin
4 dbms_output.disable; #禁止打印文本消息
5 dbms_output.put_line(i);
6 dbms_output.put_line(i+1);
7 dbms_output.enable; #启用打印文本消息功能
8 dbms_output.put_line(i+3);
9 end;
10 /
输入 num 的值: 5
原值 2: i number := #
新值 2: i number := 5;
8
PL/SQL 过程已成功完成。
可以看到禁用后无法输出;启用后则可以。
实例2:
测试dbms_output.disable
的清除缓冲区情况
SQL> l
1 declare
2 i number := #
3 begin
4 dbms_output.disable;#禁用
5 dbms_output.put_line(i);
6 dbms_output.enable;#启用
7 dbms_output.put_line(i+1);
8 dbms_output.disable;#再禁用时,清空了缓冲区。
9 dbms_output.put_line(i+3);
10* end;
#执行语句要等到end才能执行缓冲区语句,
#但此时已清空,
#故无输出
SQL> /
输入 num 的值: 9
原值 2: i number := #
新值 2: i number := 9;
PL/SQL 过程已成功完成。
1.2 DBMS_UTILITY包
- 返回当前调用栈的格式化文本字符
- 可精确找到代码执行位置
- 从而反映程序间调用关系
实例:
创建三个过程,依次调用,将调用结果打印输出
- 首先创建
过程I
SQL> create or replace 2 procedure I as 3 begin 4 dbms_output.put_line(dbms_utility.format_call_stack); 5 end I; 6 / 过程已创建。
- 创建
过程YOU
,该过程调用过程I
SQL> l 1 create or replace 2 procedure YOU as 3 begin 4 I; 5* end YOU; SQL> / 过程已创建。
- 创建
过程HE
,该过程调用过程YOU
SQL> create or replace 2 procedure HE as 3 begin 4 YOU; 5 end HE; 6 / 过程已创建。
- 依次执行
过程I
、过程YOU
、过程HE
,查看执行顺序情况(输出打印结果从下往上执行)
SQL> begin
2 I;
3 YOU;
4 HE;
5 end;
6 /
----- PL/SQL Call Stack -----
object line object
handle number
name
B894E944 3 procedure SCOTT.I
B891D380 2 anonymous block
----- PL/SQL Call Stack -----
object line object
handle number
name
B894E944 3 procedure SCOTT.I
B8935F04 3 procedure SCOTT.YOU
B891D380 3 anonymous block
----- PL/SQL Call Stack -----
object line object
handle number
name
B894E944 3 procedure SCOTT.I
B8935F04 3 procedure SCOTT.YOU
B892ABB0 3 procedure SCOTT.HE
B891D380 4 anonymous
block
PL/SQL 过程已成功完成。
1.3 自治事务
具体可参考《pragma autonomous_transaction详解》
要创建一个自治事务,您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中,使用PL/SQL中的
PRAGMAAUTONOMOUS_TRANSACTION
语句 。
在这样的模块或过程中执行的SQL语句都是自治的。
- 可将需要的调试信息记录在数据库表中
- 事务回滚依然可记录异常信息
实例:
将向一个测试表插入数据时发生的调试信息,记录在另一张信息表里,具体过程定义在一个包中。
- 创建测试表auto_log_t和记录信息表auto_test_t
SQL> create table auto_log_t (
2 auto_date date,
3 auto_mesg clob);
表已创建。
SQL> create table auto_test_t
2 (auto_val varchar2(20));
表已创建。
- 再创建一个包auto_log ,该包中的过程可用来记录错误消息
SQL> create or replace
2 package auto_log as
3 procedure record_errors(err_mesg varchar2);
4 end auto_log;
5 /
程序包已创建。
SQL> create or replace
2 package body auto_log as
3 procedure record_errors(err_mesg varchar2) is
4 pragma autonomous_transaction;
5 begin
6 insert into auto_log_t
7 values(sysdate,err_mesg);
8 commit;
9 end record_errors;
10 end auto_log;
11 /
程序包体已创建。
- 创建一个过程test_log 用来测试:
. 向表auto_test_t 插入数据时若没有异常则提交事务;
. 有异常时调用包body auto_log
中的过程record_errors
,将错误信息记录并回滚事务。
SQL> create or replace
2 procedure test_log (v_mesg varchar2) as
3 begin
4 auto_log.record_errors('Starting ....');
5 insert into auto_test_t values(v_mesg);
6 auto_log.record_errors('Ending....');
7 commit; #无异常时提交事务
8 exception
9 when others then #异常发生时记录信息
10 auto_log.record_errors('当插入数据'||v_mesg||'时发生异常');
11 auto_log.record_errors(SQLCODE||'AND'||SQLERRM);
12 rollback;#异常发生时回滚事务
13 end test_log;
14 /
过程已创建。
- 调用
过程test_log
并查看表test_log是否记录数据
SQL> exec test_log('hello PL/SQL');
PL/SQL 过程已成功完成。
SQL> select * from auto_test_t;
AUTO_VAL
--------------------
hello PL/SQL
SQL> select to_char(auto_date,'yyyy-mm-dd hh24:mi:ss'),auto_mesg from auto_log_t;
TO_CHAR(AUTO_DATE,' AUTO_MESG
------------------- ---------------------------------
2020-03-25 15:03:16 Starting ....
2020-03-25 15:03:16 Ending....
忘记date语法了,做个记录:
保存的日期格式为 yyyy-mm-dd hh24:mi:ss
默认输出格式为 yyyy-mm-dd
- 构建异常语句进行测试,并查看是否记录错误信息
SQL> exec test_log('Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production');
PL/SQL 过程已成功完成。
SQL> select concat (to_char(auto_date,'yyyy-mm-dd hh24:mi:ss') ,auto_mesg)as mesg from auto_log_t;
MESG
-------------------------------------------------------
2020-03-25 15:03:16Starting ....
2020-03-25 15:03:16Ending....
2020-03-25 16:51:29Starting ....
2020-03-25 16:51:29当插入数据Oracle Database 11g Enterp
rise Edition Release 11.2
2020-03-25 16:51:29-12899ANDORA-12899: 列 "SCOTT"."AUTO
_TEST_T"."AUTO_VAL" 的值
这里的错误显示信息有点过长,所以显示不完整,不过设置set line 666;
和col colname for a666
也没用,因为我用了concat
函数,
参考《SqlPlus 中行显示不全的问题(linesize和long的区别)》,和官方文档concat
文档表示concat中包含clob类型的数据(auto_mesg)返回的是CLOB类型,所以这里应该这么设置:
SQL> set long 555;
SQL> select concat (to_char(auto_date,'yyyy-mm-dd hh24:mi:ss') ,auto_mesg)as mesg from auto_log_t;
2020-03-25 15:03:16Starting ....
2020-03-25 15:03:16Ending....
2020-03-25 16:51:29Starting ....
2020-03-25 16:51:29当插入数据Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production时发生异常
2020-03-25 16:51:29-12899ANDORA-12899: 列 "SCOTT"."AUTO_TEST_T"."AUTO_VAL" 的值太大 (实
际值: 70, 最大值: 20)
1.4 UTL_FILE包
- 可通过该包打开操作系统的文件,并调用相关函数向文件写入数据
- 也可将调试信息写入文件,从而分析程序错误
- 使用该包前序创建DIRECTORY目录,并赋予用户相应权限