PL/SQL：ORA-01744：不適當的INTO(2)

我有以下 SQL 陳述句：

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 
    T_ENTITY             T_CUSTOMER_GRNT_TEMPLATE_BASE.ENTITY%TYPE;
    C_CUSTOMER_ID        T_CUSTOMER_PROFILE_BASE.CUSTOMER_ID%TYPE;
    T_TEMPLATE_ID        T_CUSTOMER_GRNT_TEMPLATE_BASE.TEMPLATE_ID%TYPE;
    T_AUTHORIZATIONS     T_CUSTOMER_GRNT_TEMPLATE_BASE.AUTHORIZATIONS%TYPE;

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    MERGE INTO T_CUSTOMER_GRNT_TEMPLATE_BASE TGT
    USING   (
            SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
            INTO    T_ENTITY, C_CUSTOMER_ID, T_TEMPLATE_ID, T_AUTHORIZATIONS
            FROM    T_CUSTOMER_PROFILE_BASE C
            JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
            WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID
            ) SRC
    ON  (   TGT.ENTITY          = SRC.ENTITY
        AND TGT.CUSTOMER_ID     = SRC.CUSTOMER_ID
        AND TGT.TEMPLATE_ID     = SRC.TEMPLATE_ID
        )
    WHEN    MATCHED THEN UPDATE SET SRC.AUTHORIZATIONS = TGT.AUTHORIZATIONS
    WHEN    NOT MATCHED THEN INSERT VALUES (SRC.ENTITY, NEW_CUSTOMER_ID, SRC.TEMPLATE_ID, SRC.AUTHORIZATIONS)
    WHERE   TGT.CUSTOMER_ID = NEW_CUSTOMER_ID;
COMMIT;

END;    

運行此陳述句時，我收到此錯誤：

Error report -
ORA-06550: line 16, column 13:
PL/SQL: ORA-01744: inappropriate INTO
ORA-06550: line 13, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

我在這里做錯了什么？（我在這里嘗試按照說明https://www.tutorialspoint.com/plsql/plsql_variable_types.htm）

uj5u.com熱心網友回復：

該SELECT ... INTO語法用于將查詢結果放入 PL/SQL 變數中；所以你可以這樣做：

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 
    T_ENTITY             T_CUSTOMER_GRNT_TEMPLATE_BASE.ENTITY%TYPE;
    C_CUSTOMER_ID        T_CUSTOMER_PROFILE_BASE.CUSTOMER_ID%TYPE;
    T_TEMPLATE_ID        T_CUSTOMER_GRNT_TEMPLATE_BASE.TEMPLATE_ID%TYPE;
    T_AUTHORIZATIONS     T_CUSTOMER_GRNT_TEMPLATE_BASE.AUTHORIZATIONS%TYPE;

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
    INTO    T_ENTITY, C_CUSTOMER_ID, T_TEMPLATE_ID, T_AUTHORIZATIONS
    FROM    T_CUSTOMER_PROFILE_BASE C
    JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
    WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID;

    -- do something with the variables
END;    

但它是SELECT陳述句的 PL/SQL 擴展。您沒有使用SELECT宣告；您正在使用MERGE具有子查詢的陳述句 - 這意味著它SELECT位于 SQL 背景關系中，而不是 PL/SQL 背景關系中，并且 PL/SQL 擴展不合適或沒有意義。

所以你可以洗掉額外的INTO子句，以及你不需要的變數：

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    MERGE INTO T_CUSTOMER_GRNT_TEMPLATE_BASE TGT
    USING   (
            SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
            FROM    T_CUSTOMER_PROFILE_BASE C
            JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
            WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID
            ) SRC
    ON  (   TGT.ENTITY          = SRC.ENTITY
        AND TGT.CUSTOMER_ID     = SRC.CUSTOMER_ID
        AND TGT.TEMPLATE_ID     = SRC.TEMPLATE_ID
        )
    WHEN    MATCHED THEN UPDATE SET AUTHORIZATIONS = SRC.AUTHORIZATIONS
    WHEN    NOT MATCHED THEN INSERT VALUES (SRC.ENTITY, NEW_CUSTOMER_ID, SRC.TEMPLATE_ID, SRC.AUTHORIZATIONS)
    WHERE   TGT.CUSTOMER_ID = NEW_CUSTOMER_ID;
COMMIT;

END;    

（不是很相關，但您應該使用VARCHAR2而不是VARCHAR用于您的變數資料型別；或T_CUSTOMER_GRNT_TEMPLATE_BASE.CUSTOMER_ID%TYPE。）

標籤：甲骨文plsql

上一篇：獲取此[錯誤]執行(25:14):ORA-00984:嘗試插入時，此處不允許列

下一篇：Oracle-允許空值的唯一約束