在PL/SQL/sqlplus客户端 中如何让程序暂停几秒钟

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(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN

 

2.

在编写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.

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.