SQL ORACLE 中的睡眠功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2561671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sleep function in ORACLE
提问by Salvador
I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function:
我需要在 ORACLE 中执行 SQL 查询需要一定的时间。所以我写了这个函数：
CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP
(
TIME_ IN NUMBER
)
RETURN INTEGER IS
BEGIN
DBMS_LOCK.sleep(seconds => TIME_);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE;
RETURN 1;
END TEST_SLEEP;
and I call in this way
我以这种方式打电话
SELECT TEST_SLEEP(10.5) FROM DUAL
but to work I need set grant of DBMS_LOCKto the owner of the procedure.
但为了工作，我需要DBMS_LOCK向程序所有者设置授权。
How I can rewrite this function without using the DBMS_LOCK.sleepfunction?
如何在不使用该函数的情况下重写该DBMS_LOCK.sleep函数？
回答by OMG Ponies
Short of granting access to DBMS_LOCK.sleep, this will work but it's a horrible hack:
如果没有授予对 的访问权限DBMS_LOCK.sleep，这将起作用，但这是一个可怕的黑客：
IN_TIME INT; --num seconds
v_now DATE;
-- 1) Get the date & time
SELECT SYSDATE
INTO v_now
FROM DUAL;
-- 2) Loop until the original timestamp plus the amount of seconds <= current date
LOOP
EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;
回答by Matthew Watson
Create a procedure which just does your lock and install it into a different user, who is "trusted" with dbms_lock ( USERA ), grant USERA access to dbms_lock.
创建一个过程，该过程只执行您的锁定并将其安装到不同的用户中，该用户受 dbms_lock ( USERA )“信任”，授予 USERA 对 dbms_lock 的访问权限。
Then just grant USERB access to this function. They then wont need to be able to access DBMS_LOCK
然后只需授予 USERB 访问此功能的权限。然后他们就不需要能够访问 DBMS_LOCK
( make sure you don't have usera and userb in your system before running this )
（在运行之前确保您的系统中没有 usera 和 userb ）
Connect as a user with grant privs for dbms_lock, and can create users
以具有 dbms_lock 权限的用户身份连接，并可以创建用户
drop user usera cascade;
drop user userb cascade;
create user usera default tablespace users identified by abc123;
grant create session to usera;
grant resource to usera;
grant execute on dbms_lock to usera;
create user userb default tablespace users identified by abc123;
grant create session to userb;
grant resource to useb
connect usera/abc123;
create or replace function usera.f_sleep( in_time number ) return number is
begin
dbms_lock.sleep(in_time);
return 1;
end;
/
grant execute on usera.f_sleep to userb;
connect userb/abc123;
/* About to sleep as userb */
select usera.f_sleep(5) from dual;
/* Finished sleeping as userb */
/* Attempt to access dbms_lock as userb.. Should fail */
begin
dbms_lock.sleep(5);
end;
/
/* Finished */
回答by Lukasz Szozda
From Oracle 18c you could use DBMS_SESSION.SLEEPprocedure:
从 Oracle 18c 开始，您可以使用DBMS_SESSION.SLEEP过程：
This procedure suspends the session for a specified period of time.
DBMS_SESSION.SLEEP (seconds IN NUMBER)
此过程将会话暂停指定的时间段。
DBMS_SESSION.SLEEP (seconds IN NUMBER)
DBMS_SESSION.sleepis available to all sessions with no additional grants needed.
Please note that DBMS_LOCK.sleepis deprecated.
DBMS_SESSION.sleep可用于所有会话，无需额外授权。请注意，DBMS_LOCK.sleep已弃用。
If you need simple query sleep you could use WITH FUNCTION:
如果您需要简单的查询睡眠，您可以使用WITH FUNCTION：
WITH FUNCTION my_sleep(i NUMBER)
RETURN NUMBER
BEGIN
DBMS_SESSION.sleep(i);
RETURN i;
END;
SELECT my_sleep(3) FROM dual;
回答by metatechbe
If executed within "sqlplus", you can execute a host operating system command "sleep" :
如果在“sqlplus”中执行，则可以执行主机操作系统命令“sleep”：
!sleep 1
or
或者
host sleep 1
回答by dominik
There is a good article on this topic: PL/SQL: Sleep without using DBMS_LOCKthat helped me out. I used Option 2 wrapped in a custom package. Proposed solutions are:
有一篇关于这个主题的好文章：PL/SQL: Sleep without using DBMS_LOCK帮助了我。我使用了封装在自定义包中的选项 2。建议的解决方案是：
Option 1: APEX_UTIL.sleep
If APEX is installed you can use the procedure “PAUSE” from the publicly available package APEX_UTIL.
Example – “Wait 5 seconds”:
选项 1：APEX_UTIL.sleep
如果安装了 APEX，您可以使用公开可用的 APEX_UTIL 包中的“PAUSE”过程。
示例 – “等待 5 秒”：
SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
APEX_UTIL.PAUSE(5);
DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Option 2: java.lang.Thread.sleep
An other option is the use of the method “sleep” from the Java class “Thread”, which you can easily use through providing a simple PL/SQL wrapper procedure:
Note: Please remember, that “Thread.sleep” uses milliseconds!
选项 2：java.lang.Thread.sleep
另一种选择是使用 Java 类“Thread”中的方法“sleep”，您可以通过提供一个简单的 PL/SQL 包装过程轻松使用该方法：
注意：请记住，“Thread.sleep”使用毫秒！
--- create ---
CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER)
AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
--- use ---
SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
SLEEP(5 * 1000);
DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
回答by MacTouch
What's about Java code wrapped by a procedure? Simple and works fine.
由过程包装的 Java 代码是什么？简单，工作正常。
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED SNOOZE AS
public final class Snooze {
private Snooze() {
}
public static void snooze(Long milliseconds) throws InterruptedException {
Thread.sleep(milliseconds);
}
}
CREATE OR REPLACE PROCEDURE SNOOZE(p_Milliseconds IN NUMBER) AS
LANGUAGE JAVA NAME 'Snooze.snooze(java.lang.Long)';
回答by Bozon
It would be better to implement a synchronization mechanism. The easiest is to write a file after the first file is complete. So you have a sentinel file.
最好能实现同步机制。最简单的是在第一个文件完成后写入文件。所以你有一个哨兵文件。
So the external programs looks for the sentinel file to exist. When it does it knows that it can safely use the data in the real file.
所以外部程序会寻找存在的哨兵文件。当它这样做时，它知道它可以安全地使用真实文件中的数据。
Another way to do this, which is similar to how some browsers do it when downloading files, is to have the file named base-name_part until the file is completely downloaded and then at the end rename the file to base-name. This way the external program can't "see" the file until it is complete. This way wouldn't require rewrite of the external program. Which might make it best for this situation.
执行此操作的另一种方法类似于某些浏览器在下载文件时的操作方式，是将文件命名为 base-name_part，直到文件完全下载，然后在最后将文件重命名为 base-name。这样外部程序在文件完成之前无法“看到”文件。这种方式不需要重写外部程序。这可能最适合这种情况。
回答by The love dada
You can use DBMS_PIPE.SEND_MESSAGEwith a message that is too large for the pipe, for example for a 5 second delay write XXX to a pipe that can only accept one byte using a 5 second timeout as below
您可以使用DBMS_PIPE.SEND_MESSAGE对于管道来说太大的消息，例如 5 秒延迟将 XXX 写入管道，该管道只能使用 5 秒超时接受一个字节，如下所示
dbms_pipe.pack_message('XXX');<br>
dummy:=dbms_pipe.send_message('TEST_PIPE', 5, 1);
But then that requires a grant for DBMS_PIPEso perhaps no better.
但这需要一笔赠款，DBMS_PIPE所以也许没有更好的。
回答by Matthieu BROUILLARD
If Java is installed on your 11G then you can do it in a java class and call it from your PL/SQL, but I am not sure that it does not require also a specific grant to call java.
如果您的 11G 上安装了 Java，那么您可以在 Java 类中执行它并从您的 PL/SQL 中调用它，但我不确定它是否也不需要特定的授权来调用 java。
回答by Kuberchaun
Seems the java procedure/function could work. But why don't you compile your function under a user like the application schema or a admin account that has this grant and just grant your developer account execute on it. That way the definer rights are used.
似乎 java 过程/函数可以工作。但是，您为什么不在具有此授权的应用程序架构或管理员帐户之类的用户下编译您的函数，而只授予您的开发人员帐户在其上执行。这样就使用了定义者权限。
