在PL/SQL中如何让程序暂停几秒钟

在编写PL/SQL中，有时需要程序中暂停几秒钟再继续执行，查了一下，oracle内置有这个功能dbms_lock.sleep(10)；不过dbms_lock包需要用户自己安装，演示如下：

C:\Documents and Settings\andyleng>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 10 10:04:56 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn /as sysdba　　--以SYSDBA身份登陆
Connected.
SQL> @?/rdbms/admin/dbmslock.sql　--安装系统包
Package created.
Synonym created.
Grant succeeded.
SQL> grant execute on dbms_lock to public;　--授权PUBLIC执行权限
Grant succeeded.
SQL> create table test1(id number,name varchar2(40),time date); --创建test1临时表
Table created.
SQL> select * from test1; --无数据
no rows selected
SQL> SET TIMING ON --打开时间显示
SQL> begin --开始执行测试脚本
2 insert into test1(id,name,time) values(1,'Andy',sysdate);
3 DBMS_LOCK.SLEEP(10); --让程序暂时10秒钟
4 insert into test1(id,name,time) values(2,'Shirley',sysdate);
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.04 --程序执行时间为10.04秒
SQL> SELECT ID,NAME,TO_CHAR(TIME,'YYYY/MM/DD HH24:MI:SS') AS TIME FROM TEST1;　--查询执行结果
ID NAME TIME
---------- ---------------------------------------- -------------------
1 Andy 2011/11/10 10:09:03 --第一条的插入时间是09:03
2 Shirley 2011/11/10 10:10:13 --第二条的插入时间是09:13,刚好比第一条晚了10秒钟
Elapsed: 00:00:00.00
SQL> drop table test1;
Table dropped.

自定义EXCEPTION 和GOTO的用法：

-- Create the procedure to loop del users.
CREATE OR REPLACE PROCEDURE QA2_ATGCORE_MF.delBigTab
AS
PRAGMA AUTONOMOUS_TRANSACTION;
p_Count NUMBER := 1000;
n_delete number:=0;
start_time date:=sysdate;
end_time date;
var_usergroup number;
e_nowait EXCEPTION;--自定义exception
PRAGMA EXCEPTION_INIT(e_nowait,-00054);--初使化该exception到“ORA-00054”的错误代码
BEGIN
<<restart>>--程序标签
WHILE 1 = 1
LOOP
begin
var_usergroup:=QA2_ATGCORE_MF.DEL_USERGROUP_SEQ.nextval;
EXECUTE IMMEDIATE
'update QA2_ATGCORE_MF.DPS_USER_DEL_TEMP set usergroup_id=:usergroup,del_flag=''1'',del_time=sysdate where del_flag=''0'' and rownum <=:rn'
USING var_usergroup,P_COUNT;
IF (SQL%NOTFOUND)
OR (TO_CHAR (SYSDATE, 'hh24mi') BETWEEN '0600' AND '1200')
THEN
EXIT;
ELSE
n_delete:=n_delete + SQL%ROWCOUNT;
QA2_ATGCORE_MF.DEL_USERGROUP(var_usergroup);
END IF;
COMMIT;
EXCEPTION
WHEN e_nowait THEN
DBMS_LOCK.SLEEP(2););--出现ORA-00054的错误时先等待2秒钟
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||SQLCODE||':'||SQLERRM||' I am go to restart' );
GOTO restart;--出现ORA-00054的错误时跳转到restart处重新执行该程序
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
END LOOP;
COMMIT;
end_time:=sysdate;
DBMS_OUTPUT.PUT_LINE('***************************************************************************');
DBMS_OUTPUT.PUT_LINE('Totally delete '||to_char(n_delete)||' users! Used time:'||numtodsinterval (end_time-start_time,'DAY'));
DBMS_OUTPUT.PUT_LINE('***************************************************************************');
END;
/

秒客网

在PL/SQL中如何让程序暂停几秒钟

相关文章