时间戳

首页 > 解决方案 > 使用 SQL Developer 执行 SQL

oracle - 使用 SQL Developer 执行 SQL

问题描述

我有一组 SQL 语句，我想执行这些语句来运行测试

create table t2 (x varchar2 (1), y int, z date, constraint pk_t2 primary key (y, z) );
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 1: ');

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 2: ');

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 3: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Final: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));


drop table t2;

如果我在 SQL Developer 中选择这组语句并单击“运行”，我会收到如下错误：

Error starting at line : 8 in command -
sys.DBMS_SESSION.sleep(1)
Error report -
Unknown Command

我是否对如何在 SQL Developer Studio 中运行 SQL 命令有语法问题或基本误解？

标签： oracleoracle-sqldeveloper

解决方案

您不能在PL/SQL 块之外运行 PL/SQL 代码 (DBMS_OUTPUT.PUT_LINE和)。DBMS_SESSION.SLEEP您需要将所有代码包装在BEGIN/中END并运行 DML 并将语句作为EXECUTE IMMEDIATE语句插入。

BEGIN
    EXECUTE IMMEDIATE 'create table t2 (x varchar2 (1), y int, z date, constraint pk_t2 primary key (y, z) )';
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 1: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 2: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 3: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Final: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    EXECUTE IMMEDIATE 'drop table t2';
END;

推荐阅读