ORACLE中的睡眠功能

Question

我需要在 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;

我以这种方式调用，

SELECT TEST_SLEEP(10.5) FROM DUAL

但为了工作，我需要向过程的所有者设置DBMS_LOCK的授予。

如何在不使用 DBMS_LOCK.sleep 函数的情况下重写此函数？

Answer 1

如果没有授予对 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;

Answer 2

创建一个仅执行锁定操作的过程，并将其安装到另一个用户，该用户是 dbms_lock ( USERA )“信任”的用户，授予 USERA 对 dbms_lock 的访问权限。

然后只需授予 USERB 访问此功能的权限即可。然后，他们不需要能够访问 DBMS_LOCK

（在运行此命令之前，请确保您的系统中没有 usera 和 userb）

以具有 dbms_lock 的 grant privs 的用户身份连接，并且可以创建用户

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 */

Answer 3

从 Oracle 18c 开始，您可以使用 DBMS_SESSION.SLEEP过程：

此过程会将会话暂停指定的时间段。

DBMS_SESSION.SLEEP（秒数）

DBMS_SESSION.sleep 可用于所有会话，无需额外授权。
请注意，DBMS_LOCK.sleep 已弃用。

如果您需要简单的查询睡眠，您可以使用WITH FUNCTION：

WITH FUNCTION my_sleep(i NUMBER)
RETURN NUMBER IS
BEGIN
    DBMS_SESSION.sleep(i);
    RETURN i;
END;
SELECT my_sleep(3) FROM dual;

db<>fiddle demo

Answer 4

关于此主题有一篇很好的文章： PL/SQL ：不使用 DBMS_LOCK 就睡觉，这帮助了我。我使用了封装在自定义包中的选项 2。建议的解决方案是：

选项 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;
/

---

选项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;
/

Answer 5

如果在“sqlplus”中执行，您可以执行主机操作系统命令“sleep”：

!sleep 1

或

host sleep 1

Answer 6

过程包装的 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)';

Answer 7

最好实现一个同步机制。最简单的方法是在第一个文件完成后写入一个文件。所以你有一个哨兵文件。

因此外部程序会寻找哨兵文件是否存在。当它这样做时，它知道它可以安全地使用真实文件中的数据。

另一种方法与某些浏览器下载文件时的做法类似，是将文件命名为 base-name_part，直到文件完全下载，然后最后将文件重命名为 base-name。
这样，外部程序在文件完成之前无法“查看”该文件。这种方式不需要重写外部程序。这可能最适合这种情况。

Answer 8

您可以按如下方式使用DBMS_ALERT包：

CREATE OR REPLACE FUNCTION sleep(seconds IN NUMBER) RETURN NUMBER
AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    message VARCHAR2(200);
    status  INTEGER;
BEGIN
    DBMS_ALERT.WAITONE('noname', message, status, seconds);
    ROLLBACK;
    RETURN seconds;
END;

SELECT sleep(3) FROM dual;

Answer 9

如果 Java 安装在您的 11G 上，那么您可以在 java 类中执行此操作并从 PL/SQL 中调用它，但我不确定它是否也不需要特定的授权来调用 java。

Answer 10

您可以将 DBMS_PIPE.SEND_MESSAGE 与对于管道来说太大的消息一起使用，例如，使用 5 秒超时将 XXX 延迟 5 秒写入只能接受一个字节的管道，如下所示

dbms_pipe.pack_message('XXX');<br>
dummy:=dbms_pipe.send_message('TEST_PIPE', 5, 1);

但是那么这需要 DBMS_PIPE 的资助，所以也许没有更好的了。

Answer 11

看来java过程/函数可以工作。但是为什么不在像应用程序架构这样的用户或具有此授权的管理员帐户下编译您的函数，并只授予您的开发人员帐户在其上执行。这样就可以使用定义者权限。

Answer 12

这是我的解决方案：

declare
   vFecIni date;
   vFecFin date;
   vInicio number;
   vFinal  number;

   function milisegundos return number is
      vRes number;
   begin
      select round(
              ( (horas         * 3600000000)
              + (minutos       *   60000000)
              + (segundos      *    1000000)
              + microsegundos ) / 1000 )
         into vRes
         from ( select to_number(substr(tiempo, 1,2)) as horas,
                       to_number(substr(tiempo, 4,2)) as minutos,
                       to_number(substr(tiempo, 7,2)) as segundos,
                       to_number(substr(tiempo,  10)) as microsegundos
                 from ( select to_char(ct, 'HH24:MI:SS.FF') as tiempo
                          from (select current_timestamp as ct from dual) ) );
      
      return vRes;
      
   end milisegundos;
   
   function ms_to_char(pMs number) return varchar2 is
       CMILIS constant number:= 1000;
       CMINSEC constant number:= 60;
       CHORMIN constant number:= 60;
       vHoras number:= 0;
       vMinutos number:= 0;
       vSegundos number:= 0;
       vMilisegundos number:= 0;
       vResto number:= 0;
       vRes varchar2(32767);
    begin
    --
    -- Milisegundos
       vMilisegundos:= pMs;
       
    --
    -- Segundos
       if CMILIS < vMilisegundos then
          vResto:= mod(vMilisegundos, CMILIS);
          vSegundos:= (vMilisegundos - vResto) / CMILIS;
          vMilisegundos:= vResto;
          
       --
       -- Minutos
          if CMINSEC < vSegundos then
             vResto:= mod(vSegundos, CMINSEC);
             vMinutos:= (vSegundos - vResto) / CMINSEC;
             vSegundos:= vResto;
             
          --
          -- Horas
             if CHORMIN < vMinutos then
                vResto:= mod(vMinutos, CHORMIN);
                vHoras:= (vMinutos - vResto) / CHORMIN;
                vMinutos:= vResto;
              
             end if; -- Horas
          
          end if; -- Minutos
          
       end if; -- Segundos
       
    --
    -- Resultado en formato texto: HH24:MI:SS.FF
       vRes:= lpad(vHoras,        2, '0') || ':'
           || lpad(vMinutos,      2, '0') || ':'
           || lpad(vSegundos,     2, '0') || '.'
           || lpad(vMilisegundos, 3, '0');
           
       return vRes;

    end ms_to_char;
   
   procedure parada_de(de number) is
      CSEGUNDO constant number:= 1000;
      vFec date:= sysdate;
      vIni number:= milisegundos;
      vFin number;
      vMax number:= de * CSEGUNDO;
      vTot number;
   begin
      vFecIni:= vFec;
      
      loop
         vFin:= milisegundos;
         vTot:= vFin - vIni;
         exit when vTot > vMax;
      end loop;
      
      vFecFin:= sysdate;
      
   end parada_de;
   
begin
   vInicio:= milisegundos;
   parada_de(2);
   vFinal:= milisegundos;
   dbms_output.put_line(  'Inicia en '      || to_char(vFecIni,'hh24:mi:ss') || ' (' || vInicio
                       || '), finaliza en ' || to_char(vFecFin,'hh24:mi:ss') || ' (' || vFinal
                       || '), duró '        || ms_to_char(vFinal - vInicio)
                      );
end;

Answer 13

您可以使用 DBMS_SESSION.SLEEP 替换 Oracle Database 18c 中的 DBMS_LOCK.SLEEP
https://oracle-base.com/articles/18c/dbms_session- sleep-18c#dbms_session