oracle - 与多行选择合并

Question

我有一个每次运行的查询，选择与表 (p_table_name_in) 相关的 user_triggers 行。我想每天运行这个过程，我只想插入新行，而不是再次插入所有行。但是当我安装这个 oackage 时，我得到这个错误:

ORA-00932 (130: 21): PL / SQL: ORA-00932: Inconsistent data types:CLOB expected, LONG received (line 31)

当我尝试将 TRIGGER_BODY AS BODY_TRIGGER 更改为 TO_LOB(TRIGGER_BODY) AS BODY_TRIGGER 时，我收到此错误:

ORA-00932 (111: 29): PL / SQL: ORA-00932: Inconsistent data types: -expected, LONG received (line 12)

程序:

PROCEDURE save_trigger_definitions ( p_table_name_in in VARCHAR2 ) IS        
BEGIN                  
        MERGE INTO hot_utils_reload_triggers t1
        USING
        (
        SELECT TRIGGER_NAME ,
                            TABLE_NAME , 
                            STATUS , 
                            DESCRIPTION,
                            TRIGGER_BODY AS BODY_TRIGGER,
                            WHEN_CLAUSE 
                FROM user_triggers
        )t2
        ON(t2.TABLE_NAME like upper(p_table_name_in))
        WHEN MATCHED THEN UPDATE SET
            t1.DESCRIPTION = t2.DESCRIPTION,
            t1.WHEN_CLAUSE = t2.WHEN_CLAUSE
        WHEN NOT MATCHED THEN 
            INSERT (TRIGGER_NAME,
                    TABLE_NAME, 
                    STATUS, 
                    DESCRIPTION,
                    BODY_TRIGGER,
                    WHEN_CLAUSE)
            VALUES (t2.TRIGGER_NAME,
                    t2.TABLE_NAME, 
                    t2.STATUS, 
                    t2.DESCRIPTION, 
                    t2.BODY_TRIGGER, 
                    t2.WHEN_CLAUSE); 
            commit;
END save_trigger_definitions;

Answer 1

令我感兴趣的是，Oracle 不允许在 SELECT 或 MERGE 语句中使用 TO_LOB，而对于 插入。因此，您可以单独使用 INSERT 和 MERGE 仅包含 MATCHED 部分的部分，例如

CREATE OR REPLACE PROCEDURE save_trigger_definitions ( p_table_name_in in VARCHAR2 ) IS    
BEGIN  
    INSERT INTO hot_utils_reload_triggers
        (trigger_name,
         table_name,
         status,
         description,
         body_trigger,
         when_clause)
    SELECT trigger_name,
           table_name,
           status,
           description,
           TO_LOB(trigger_body),
           when_clause
      FROM user_triggers
     WHERE table_name LIKE UPPER(p_table_name_in)
       AND NOT EXISTS ( SELECT 1 
                          FROM hot_utils_reload_triggers 
                         WHERE trigger_name = u.trigger_name
                           AND table_name = u.table_name
                           AND status = u.status );

    UPDATE hot_utils_reload_triggers h
       SET h.description = description, h.when_clause = when_clause
     WHERE table_name LIKE UPPER(p_table_name_in);

    COMMIT;
END;
/

假设您不希望某些列(例如 trigger_name、table_name、status)出现重复的行，我添加了一个子查询它们在 NOT EXISTS 子句之后。

Ref1

Ref2

使用 DBMS_REDEFINITION.START_REDEF_TABLE() 可能是将 LONG 转换为 LOB 的另一种选择。