How to pause a program for several seconds in PL/SQL

In compiling PL/SQL, it may take several seconds to pause the program and continue execution. Check that dbms_lock.sleep (10) is provided in Oracle. However, you must install the dbms_lock package on your own, the demo is as follows:

1. C: \ Documents entsAndSettings \ andyleng> sqlplus/nolog
3. SQL * Plus: Release 11.2.0.1.0 ProductionOnThu Nov 10 10:04:56 2011
5. Copyright (c) 1982,201 0, Oracle.AllRights reserved.
7. SQL> conn/AsSysdba-- Log in as SYSDBA
8. Connected.
9. SQL> @? /Rdbms/admin/dbmslock. SQL-- Install the system package
11. Package created.
14. Synonym created.
17. GrantSucceeded.
19. SQL>Grant Execute OnDbms_lockTo Public;-- Grant PUBLIC execution permission
21. GrantSucceeded.
23. SQL>Create TableTest1 (id number,NameVarchar2 (40 ),Time Date);-- Create a temporary table named test1
25. TableCreated.
27. SQL>Select*FromTest1;-- No data
29. No RowsSelected
32. SQL>SETTIMINGON-- Enable time display
33. SQL>Begin-- Start to execute the test script
34. 2Insert IntoTest1 (id,Name,Time)Values(1,'And', Sysdate );
35. 3 DBMS_LOCK.SLEEP (10 );-- Temporarily enable the program to take 10 seconds
36. 4Insert IntoTest1 (id,Name,Time)Values(2,'Shirley', Sysdate );
37. 5Commit;
38. 6End;
39. 7/
41. PL/SQLProcedureSuccessfully completed.
43. Elapsed: 00:00:10. 04-- The program execution time is 10.04 seconds.
46. SQL>SELECTID,NAME, TO_CHAR (TIME,'Yyyy/MM/DD HH24: MI: ss')AS TIME FROMTEST1;-- Query execution results
48. IDNAMETIME
49. ---------------------------------------------------------------------
50. 1 Andy 2011/11/10 10:09:03-- The First insert time is.
51. 2 Shirley 10:10:13-- The insertion time of the second entry is, which is 10 seconds later than that of the first entry.
54. Elapsed: 00:00:00. 00
57. SQL>Drop TableTest1;
59. TableDropped. 

Custom EXCEPTION and GOTO usage:

1. -- Create the procedure to loop del users.
2. CREATE OR REPLACE PROCEDUREQA2_ATGCORE_MF.delBigTab
3. AS
4. PRAGMA AUTONOMOUS_TRANSACTION;
5. P_Count NUMBER: = 1000;
6. N_delete number: = 0;
7. Start_timeDate: = Sysdate;
8. End_timeDate;
9. Var_usergroup number;
10. E_nowait EXCEPTION;-- Custom exception
11. PRAGMA EXCEPTION_INIT (e_nowait,-00054 );-- Error code that first caused the exception to "ORA-00054"
12. BEGIN
13. <Restart>-- Program tag
14. WHILE 1 = 1
15. LOOP
16. Begin
17. Var_usergroup: = QA2_ATGCORE_MF.DEL_USERGROUP_SEQ.nextval;
18. EXECUTEIMMEDIATE
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 '123' AND '123')
23. THEN
24. EXIT;
25. ELSE
26. N_delete: = n_delete + SQL % ROWCOUNT;
27. QA2_ATGCORE_MF.DEL_USERGROUP (var_usergroup );
28. ENDIF;
29. COMMIT;
30. EXCEPTION
31. WHENE_nowaitTHEN
32. DBMS_LOCK.SLEEP (2 ););-- Wait 2 seconds before a ORA-00054 error occurs
33. DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE,'Yyyy/MM/DD HH24: MI: ss') | SQLCODE |':'| SQLERRM |'I am go to restart');
34. GOTORestart;-- Jump to restart and re-execute the program when a ORA-00054 error occurs
35. WHENOTHERSTHEN
36. ROLLBACK;
37. RAISE;
38. END;
40. ENDLOOP;
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. /