How to suspend a program for a few seconds in the Pl/sql/sqlplus client

1. How to check procedure exist:

Sql> Conn oper/oper123
Connected.
sql> desc Dbms_lock;
PROCEDURE Allocate_unique
Argument Name Type in/out Default?
------------------------------ ----------------------- ------ --------
Lockname VARCHAR2 in
Lockhandle VARCHAR2 out
Expiration_secs number in DEFAULT
FUNCTION CONVERT RETURNS Number (38)
Argument Name Type in/out Default?
------------------------------ ----------------------- ------ --------
ID number (+) in

2.

in writing PL/SQL, it is sometimes necessary to pause the program for a few seconds before proceeding, checking that Oracle has this feature built-in Dbms_lock.sleep (10), but the Dbms_lock package needs to be installed by the user, as shown below:

[SQL]View Plaincopy print?

2. C:\Documents and Settings\andyleng>sqlplus/nolog
6. Sql*plus:release 11.2.0.1.0 Production on Thu Nov 10:04:56
10. Copyright (c) 1982, Oracle.  All rights reserved.
14. Sql> Conn/as SYSDBA --Login as Sysdba
16. Connected.
18. Sql> @?/rdbms/admin/dbmslock.sql --Install the System Package
22. Package created.
28. Synonym created.
34. Grant succeeded.
38. Sql> Grant execute on dbms_lock to public ; --Authorize public to execute permissions
42. Grant succeeded.
46. sql> Create table test1 (ID number,name VARCHAR2 (+),time date); --Create test1 temporary table
50. Table created.
54. Sql> SELECT * from test1; --No data
58. No rows selected
64. sql> SET TIMING on --open time display
66. Sql> begin --Start execution of test scripts
68. 2 INSERT INTO test1 (ID,name, time) values (1,' Andy ', sysdate);
70. 3 Dbms_lock.  SLEEP (10); --10 seconds for the program to be temporarily
72. 4 INSERT INTO test1 (ID,name, time) values (2,' Shirley ', sysdate);
74. 5 commit;
76. 6 end;
78. 7/
82. PL/SQL procedure successfully completed.
86. elapsed:00:00:10.04 --Program execution time is 10.04 seconds
92. Sql> SELECT ID,Name,to_char (Time,' Yyyy/mm/dd HH24:MI:SS ') as time from TEST1; --Query execution Results
96. ID NAME time
98. ---------- ---------------------------------------- -------------------
100. 1 Andy 2011/11/10 10:09:03 --The insertion time of the first one is 09:03
102. 2 Shirley 2011/11/10 10:10:13 --The insertion time of the second one is 09:13, just 10 seconds later than the first one .
108. elapsed:00:00:00.00
114. sql> drop table test1;
118. Table dropped.

C:\Documents and Settings\andyleng>sqlplus/nologsql*plus:release 11.2.0.1.0 Production on Thu Nov 10:04:56 2011Cop  Yright (c) 1982, Oracle. All rights reserved. Sql> conn/as SYSDBA--Login as Sysdba connected.sql> @?/rdbms/admin/dbmslock.sql--Install the system package created. Synonym created. Grant succeeded.　Sql> Grant execute on dbms_lock to public; --Authorize public to execute permissions Grant succeeded.   Sql> CREATE TABLE test1 (ID number,name varchar2 (+), time date); --Create test1 temporary table created.  Sql> select * from Test1; --No data no rows selectedsql> SET TIMING on--open time display sql> Begin--Start Execution test script 2 insert into test1 (id,name,time) values  (1, ' Andy ', sysdate); 3 Dbms_lock.  SLEEP (10);  --Let the program temporarily 10 seconds 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--Program execution time is 10.04 seconds sql> SELECT id,name,to_char (timing, ' yyyy/mm/dd HH24:MI:SS ') as the from TEST1;                            --Query execution result ID NAME         Time---------------------------------------------------------------------1 Andy 2011/11/10 10:09:03--The insertion time of the first article is 09:03 2 Shirley 2011/11/10 10:10:1 3--The insertion time of the second is 09:13, just 10 seconds later than the first one elapsed:00:00:00.00sql> drop table test1; Table dropped.

How to suspend a program for a few seconds in the Pl/sql/sqlplus client