首页 文章

错误：此SELECT语句中需要INTO子句

提问于
浏览 次
0

我正在尝试使用if语句基于db实例运行不同的代码，但我不断收到此错误：

错误报告 - ORA-06550：第9行，第8列：PLS-00428：此SELECT语句中预计会出现INTO子句06550. 00000 - “行％s，列％s：\ n％s”*原因：通常是PL / SQL编译错误 . *行动：

我正在尝试使用系统变量来确定我是否在prod，qa，dev中，然后根据我的位置运行各种语句 .

这就是我尝试过的：

DECLARE 
        ENV VARCHAR(256);
    BEGIN
        SELECT sys_context('USERENV','DB_NAME') AS Instance
        INTO ENV
        FROM dual;

        IF ENV = 'CSTMP' THEN
            CREATE MV_TEST_CSTMP ...;
        ELSIF ENV = 'PROD' THEN
            CREATE MV_TEST_PROD ...;
        ELSE
            CREATE MV_DEFAULT ...;
        END IF;
    END;

但我明白了： PLS-00428: an INTO clause is expected in this SELECT statement

编辑：我也试过这个

VAR ENV VARCHAR(255);

SELECT sys_context('USERENV','DB_NAME') AS Instance
INTO ENV
FROM DUAL;

IF ENV = 'CSTMP' THEN
    execute immediate 'CREATE MATERIALIZED VIEW TEST_CSTMP
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 2';

ELSIF ENV = 'PROD' THEN
    execute immediate 'CREATE MATERIALIZED VIEW PROD
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 3';

ELSE
    execute immediate 'CREATE MATERIALIZED VIEW TEST_ELSE
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 4';
END IF;

： ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

sql oracle plsql

1 回答

  • 0

    得到它像这样工作：

    DECLARE 
 ENV VARCHAR(255);

BEGIN

    SELECT sys_context('USERENV','DB_NAME') AS Instance
    INTO ENV
    FROM DUAL;

    IF ENV = 'CSTMP' THEN
        execute immediate 'CREATE MATERIALIZED VIEW TEST_CSTMP
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 2';

    ELSIF ENV = 'PROD' THEN
        execute immediate 'CREATE MATERIALIZED VIEW PROD
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 3';

    ELSE
        execute immediate 'CREATE MATERIALIZED VIEW TEST_ELSE
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 4';
    END IF;
END;
    回复于

相关问题