我可以在Oracle PL / SQL中动态访问:NEW和:OLD吗?

Question

我正在尝试以某种方式动态访问Oracle SQL中的：NEW和：OLD。我知道这是不可能的，但我正在寻找一种更简单（且更不混乱）的方法，而不仅仅是创建用于创建触发器并执行它的脚本。到目前为止我的代码：

--Creating a new type called COLUMN_ARRAY to save multiple column names 
--(up to 50) dynamically in an array.
CREATE OR REPLACE TYPE COLUMN_ARRAY AS VARRAY(50) OF VARCHAR2(30);
/
--Creating a procedure which is called in the trigger. 
--It inserts the given values into a changelog table.
CREATE OR REPLACE PROCEDURE PRO_CL(
    var_changelogTable VARCHAR2,
    var_table          VARCHAR2,
    var_column         VARCHAR2,
    var_oldValue       VARCHAR2,
    var_newValue       VARCHAR2)
IS
BEGIN
  EXECUTE IMMEDIATE '    
INSERT    
INTO '||var_changelogTable||' VALUES      
(        
'''',        
SYSTIMESTAMP,        
'''||var_table||''',        
'''||var_column||''',
'''||var_oldValue||''',        
'''||var_newValue||'''      
)';
END;
/

这将创建触发器。现在，我遍历我的数组并使用表名动态初始化传递给过程的变量，这不起作用。

CREATE OR REPLACE TRIGGER TR_CL BEFORE
  INSERT OR
  UPDATE OR
  DELETE ON TESTTABLE FOR EACH ROW DECLARE
  ------------------------------------------------------------------------------
  -- These values have to be put in by a user
  var_changelogTable VARCHAR2(30) := 'CHANGELOGTABLE';
  var_table            VARCHAR2(30) := 'TESTTABLE';
  var_columns COLUMN_ARRAY          := COLUMN_ARRAY('TEST1', 'TEST2');
  ------------------------------------------------------------------------------
  -- These values are dynamic and are produced by the trigger
  var_column   VARCHAR2(30);
  var_oldValue VARCHAR2(4000);
  var_newValue VARCHAR2(4000);
  ------------------------------------------------------------------------------
  BEGIN
    FOR i IN 1..var_columns.count
    LOOP
      -- Dynamic variables are initialized
      var_column := var_columns(i);
      EXECUTE IMMEDIATE 'var_oldValue := :OLD.'||var_column;
      EXECUTE IMMEDIATE 'var_newValue := :NEW.'||var_column;
      -- The Procedure only is called when the old and new values are different
      IF var_oldValue != var_newValue THEN
        PRO_CL(var_changelogTable, var_table, var_column, var_oldValue, var_newValue);
      END IF;
    END LOOP;
  END;
  /

所以我的问题是：有没有办法动态创建这个触发器，或者我是否必须使用混乱的方式动态创建一个脚本，然后创建触发器？

Answer 1

1. 您可以创建触发器本身，动态循环user_tab_columns（每列将有 1 行调用 PRO_CL_BESTNR）
2. oldValue ！= newValue 不会在不更改的情况下覆盖 NULL 值，您应该将这两个值传递给日志过程，这将比较它们