PL/SQL中监控执行进度两种方法 --转载

这是我常用的两种PL/SQL监控运行状况的方法：

1. 使用dbms_application_info.SET_CLIENT_INFO

举例如下:
declare
cursor cr is select rowid from test;
delete_count number;
total_count number;
begin
delete_count :=0;
total_count :=0;
for i in cr loop
delete from test where rowid=i.rowid;
delete_count :=delete_count+1;
total_count :=total_count+1;
if (delete_count>100) then
dbms_application_info.SET_CLIENT_INFO(' So far '||total_count||' rows has been deleted');
delete_count :=0;
commit;
end if;
end loop;
end;
/

另开一session, select client_info from v$session where client_info like 'So far%';

注意info的长度有限制，超过64字符会被截断

2. 使用dbms_system.ksdwrt， 这个可以写到300个字符

KSDWRT Procedure

This procedure prints the message to the target file (alert log and/or trace file).

Syntax

DBMS_SYSTEM.KSDWRT (
dest IN BINARY_INTEGER,
tst IN VARCHAR2);

Parameters

Table KSDWRT Procedure Parameters

Parameter	Description
dest	Destination is indicated by the following values: 1 - Write to trace file. 2 - Write to alertlog. 3 - Write to both.
tst	Message (not tested for max length, but output with 300 chars was successful)

举例如下:

declare
cursor cr is select rowid from test;
delete_count number;
total_count number;
begin
delete_count :=0;
total_count :=0;
for i in cr loop
delete from test where rowid=i.rowid;
delete_count :=delete_count+1;
total_count :=total_count+1;
if (delete_count>100) then
dbms_system.ksdwrt (1,' So far '||total_count||' rows has been deleted');
delete_count :=0;
commit;
end if;
end loop;
end;
/