在PL/SQL中如何讓程式暫停幾秒鐘

在編寫PL/SQL中，有時需要程式中暫停幾秒鐘再繼續執行，查了一下，Oracle內建有這個功能dbms_lock.sleep(10)；不過dbms_lock包需要使用者自己安裝，示範如下：

1. C:\Documents and Settings\andyleng>sqlplus /nolog  
2.   
3. SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 10 10:04:56 2011  
4.   
5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
6.   
7. SQL> conn /as sysdba　　--以SYSDBA身份登陸   
8. Connected.  
9. SQL> @?/rdbms/admin/dbmslock.sql　--安裝系統包   
10.   
11. Package created.  
12.   
13.   
14. Synonym created.  
15.   
16.   
17. Grant succeeded.  
18.   
19. SQL> grant execute on dbms_lock to public;　--授權PUBLIC執行許可權   
20.   
21. Grant succeeded.  
22.   
23. SQL> create table test1(id number,name varchar2(40),time date);   --建立test1暫存資料表   
24.   
25. Table created.  
26.   
27. SQL> select * from test1;  --無資料   
28.   
29. no rows selected  
30.   
31.   
32. SQL> SET TIMING ON  --開啟時間顯示   
33. SQL> begin  --開始執行測試指令碼   
34.   2    insert into test1(id,name,time) values(1,'Andy',sysdate);  
35.   3    DBMS_LOCK.SLEEP(10);  --讓程式暫時10秒鐘   
36.   4    insert into test1(id,name,time) values(2,'Shirley',sysdate);  
37.   5    commit;  
38.   6  end;  
39.   7  /  
40.   
41. PL/SQL procedure successfully completed.  
42.   
43. Elapsed: 00:00:10.04  --程式執行時間為10.04秒   
44.   
45.   
46. SQL> SELECT ID,NAME,TO_CHAR(TIME,'YYYY/MM/DD HH24:MI:SS') AS TIME FROM TEST1;　--查詢執行結果   
47.   
48.         ID NAME                                     TIME  
49. ---------- ---------------------------------------- -------------------   
50.          1 Andy                                     2011/11/10 10:09:03   --第一條的插入時間是09:03   
51.          2 Shirley                                  2011/11/10 10:10:13   --第二條的插入時間是09:13,剛好比第一條晚了10秒鐘   
52.   
53.   
54. Elapsed: 00:00:00.00  
55.   
56.   
57. SQL> drop table test1;  
58.   
59. Table dropped. 

自訂EXCEPTION 和GOTO的用法：

1. -- Create the procedure to loop del users.   
2. CREATE OR REPLACE PROCEDURE QA2_ATGCORE_MF.delBigTab  
3. AS  
4.        PRAGMA AUTONOMOUS_TRANSACTION;  
5.        p_Count   NUMBER := 1000;  
6.        n_delete number:=0;  
7.        start_time date:=sysdate;  
8.        end_time date;  
9.        var_usergroup number;  
10.        e_nowait EXCEPTION;--自訂exception   
11.        PRAGMA EXCEPTION_INIT(e_nowait,-00054);--初使化該exception到“ORA-00054”的錯誤碼   
12. BEGIN  
13.    <<restart>>--程式標籤   
14.    WHILE 1 = 1  
15.    LOOP  
16.            begin  
17.                   var_usergroup:=QA2_ATGCORE_MF.DEL_USERGROUP_SEQ.nextval;  
18.                   EXECUTE IMMEDIATE  
19.                      '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'  
20.                      USING var_usergroup,P_COUNT;  
21.                   IF (SQL%NOTFOUND)  
22.                      OR (TO_CHAR (SYSDATE, 'hh24mi') BETWEEN '0600' AND '1200')  
23.                   THEN  
24.                      EXIT;  
25.                   ELSE  
26.                      n_delete:=n_delete + SQL%ROWCOUNT;  
27.                      QA2_ATGCORE_MF.DEL_USERGROUP(var_usergroup);  
28.                   END IF;  
29.                   COMMIT;             
30.            EXCEPTION  
31.                 WHEN e_nowait THEN  
32.                    DBMS_LOCK.SLEEP(2););--出現ORA-00054的錯誤時先等待2秒鐘   
33.                    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')||SQLCODE||':'||SQLERRM||' I am go to restart' );  
34.                    GOTO restart;--出現ORA-00054的錯誤時跳轉到restart處重新執行該程式   
35.                WHEN OTHERS THEN  
36.                    ROLLBACK;  
37.                    RAISE;  
38.            END;  
39.              
40.    END LOOP;  
41.    COMMIT;  
42.    end_time:=sysdate;  
43.    DBMS_OUTPUT.PUT_LINE('***************************************************************************');  
44.    DBMS_OUTPUT.PUT_LINE('Totally delete '||to_char(n_delete)||' users! Used time:'||numtodsinterval (end_time-start_time,'DAY'));  
45.    DBMS_OUTPUT.PUT_LINE('***************************************************************************');  
46. END;  
47. /