Oracle使用REF存储员工上级时，如何校验属性并规避变异表错误？

针对你遇到的REF类型校验问题，同时要避免变异表错误，以下是几种可行方案：

1. 使用复合触发器（推荐） #

复合触发器允许在不同触发阶段（行前、行后、语句前、语句后）执行逻辑，能绕过行级触发器无法读取变异表的限制。我们可以在行级阶段收集需要校验的新数据，在语句级阶段统一查询校验：

CREATE OR REPLACE TRIGGER at_employee_trig
FOR INSERT OR UPDATE ON at_employee_table
COMPOUND TRIGGER

  -- 定义集合存储需要校验的员工数据
  TYPE emp_check_rec IS RECORD(
    supervisor_ref REF at_employee_type,
    emp_branch_ref REF at_branch_type
  );
  TYPE emp_check_tab IS TABLE OF emp_check_rec;
  g_emp_check emp_check_tab := emp_check_tab();

BEFORE EACH ROW IS
BEGIN
  -- 收集当前行的supervisor和分支REF
  IF :NEW.supervisor_r IS NOT NULL THEN
    g_emp_check.EXTEND;
    g_emp_check(g_emp_check.LAST).supervisor_ref := :NEW.supervisor_r;
    g_emp_check(g_emp_check.LAST).emp_branch_ref := :NEW.branch_r;
  END IF;
END BEFORE EACH ROW;

AFTER STATEMENT IS
  v_supervisor_job_ref REF at_job_type;
  v_supervisor_branch_ref REF at_branch_type;
  v_job_position VARCHAR2(20);
  v_supervisor_branch_code NUMBER;
  v_emp_branch_code NUMBER;
BEGIN
  -- 遍历收集的数据，逐个校验
  FOR i IN g_emp_check.FIRST .. g_emp_check.LAST LOOP
    -- 解析supervisor的job和branch REF
    SELECT DEREF(g_emp_check(i).supervisor_ref).job_r,
           DEREF(g_emp_check(i).supervisor_ref).branch_r
    INTO v_supervisor_job_ref, v_supervisor_branch_ref
    FROM DUAL;

    -- 获取job的position
    SELECT DEREF(v_supervisor_job_ref).position
    INTO v_job_position
    FROM DUAL;

    -- 获取supervisor和当前员工的分支代码
    SELECT DEREF(v_supervisor_branch_ref).branch_code,
           DEREF(g_emp_check(i).emp_branch_ref).branch_code
    INTO v_supervisor_branch_code, v_emp_branch_code
    FROM DUAL;

    -- 执行校验逻辑
    IF v_job_position NOT IN ('head','manager','team leader') 
       OR v_supervisor_branch_code != v_emp_branch_code THEN
      RAISE_APPLICATION_ERROR(-20001, 'Supervisor does not meet requirements: must be head/manager/team leader and in same branch');
    END IF;
  END LOOP;
END AFTER STATEMENT;

END at_employee_trig;
/

2. 通过存储过程封装DML操作 #

强制所有员工的插入/更新都通过存储过程执行，在存储过程中先完成校验逻辑，再执行DML，避免触发器的变异表问题：

CREATE OR REPLACE PROCEDURE insert_employee(
  p_address IN at_address_type,
  p_name IN at_name_type,
  p_phones IN at_nested_phone,
  p_ni_num IN VARCHAR2,
  p_emp_id IN NUMBER,
  p_supervisor_ref IN REF at_employee_type,
  p_job_ref IN REF at_job_type,
  p_branch_ref IN REF at_branch_type,
  p_join_date IN DATE
) IS
  v_supervisor_job_ref REF at_job_type;
  v_supervisor_branch_ref REF at_branch_type;
  v_job_position VARCHAR2(20);
  v_supervisor_branch_code NUMBER;
  v_emp_branch_code NUMBER;
BEGIN
  -- 校验supervisor条件
  IF p_supervisor_ref IS NOT NULL THEN
    SELECT DEREF(p_supervisor_ref).job_r,
           DEREF(p_supervisor_ref).branch_r
    INTO v_supervisor_job_ref, v_supervisor_branch_ref
    FROM DUAL;

    SELECT DEREF(v_supervisor_job_ref).position
    INTO v_job_position
    FROM DUAL;

    SELECT DEREF(v_supervisor_branch_ref).branch_code,
           DEREF(p_branch_ref).branch_code
    INTO v_supervisor_branch_code, v_emp_branch_code
    FROM DUAL;

    IF v_job_position NOT IN ('head','manager','team leader') 
       OR v_supervisor_branch_code != v_emp_branch_code THEN
      RAISE_APPLICATION_ERROR(-20001, 'Supervisor does not meet requirements: must be head/manager/team leader and in same branch');
    END IF;
  END IF;

  -- 执行插入
  INSERT INTO at_employee_table
  VALUES(at_employee_type(p_address, p_name, p_phones, p_ni_num, p_emp_id, p_supervisor_ref, p_job_ref, p_branch_ref, p_join_date));
END insert_employee;
/

调用示例：

DECLARE
  v_address at_address_type := at_address_type('Adam', 'Edinburgh', 'EH1 6EA');
  v_name at_name_type := at_name_type('Mr', 'Jack', 'Smith');
  v_phones at_nested_phone := at_nested_phone(
    at_phone_type('home', '01311112223'),
    at_phone_type('mobile', '0781209890')
  );
  v_supervisor_ref REF at_employee_type;
  v_job_ref REF at_job_type;
  v_branch_ref REF at_branch_type;
BEGIN
  -- 获取supervisor、job、branch的REF
  SELECT REF(s) INTO v_supervisor_ref FROM at_employee_table s WHERE s.emp_id = 101;
  SELECT REF(j) INTO v_job_ref FROM at_job_table j WHERE j.position = 'team leader';
  SELECT REF(b) INTO v_branch_ref FROM at_branch_table b WHERE b.branch_code = 908;

  -- 调用存储过程插入
  insert_employee(v_address, v_name, v_phones, 'NI810', 804, v_supervisor_ref, v_job_ref, v_branch_ref, TO_DATE('05-Feb-18','DD-Mon-YY'));
  COMMIT;
END;
/

3. 冗余存储校验字段（可选） #

如果可以接受冗余数据，可以给at_employee_table添加两个字段：supervisor_job_position和supervisor_branch_code，然后用CHECK约束直接校验，同时用触发器维护这些字段的同步：

第一步：修改表结构添加冗余字段 #

ALTER TABLE at_employee_table ADD (
  supervisor_job_position VARCHAR2(20),
  supervisor_branch_code NUMBER
);

第二步：添加CHECK约束 #

ALTER TABLE at_employee_table ADD CONSTRAINT check_supervisor_valid
CHECK (
  (supervisor_r IS NULL) OR (
    supervisor_job_position IN ('head','manager','team leader') 
    AND supervisor_branch_code = DEREF(branch_r).branch_code
  )
);

第三步：创建触发器维护冗余字段 #

CREATE OR REPLACE TRIGGER at_employee_sync_supervisor
BEFORE INSERT OR UPDATE OF supervisor_r ON at_employee_table
FOR EACH ROW
BEGIN
  IF :NEW.supervisor_r IS NOT NULL THEN
    SELECT DEREF(:NEW.supervisor_r).job_r.position,
           DEREF(:NEW.supervisor_r).branch_r.branch_code
    INTO :NEW.supervisor_job_position, :NEW.supervisor_branch_code
    FROM DUAL;
  ELSE
    :NEW.supervisor_job_position := NULL;
    :NEW.supervisor_branch_code := NULL;
  END IF;
END;
/

注意：这个方案需要额外维护冗余字段，当supervisor的职位或分支变化时，需要额外的触发器更新所有下属的冗余字段，否则会出现数据不一致。

内容的提问来源于stack exchange，提问作者Andrew Taison