Oracle plsql如果elseif其他不起作用

Question

我有一个带有两个列的电子邮件的表，这是遗产，但是我想创建一个带有完整电子邮件地址的列，以方便查询并创建索引等等。

我有以下plsql：

create or replace TRIGGER SET_EMAIL_ADDRESS 
BEFORE INSERT OR UPDATE ON EMAIL 
FOR EACH ROW  
DECLARE
 IS_PREFIX_UPDATED BOOLEAN;
 IS_PROVIDER_UPDATED BOOLEAN;
 IS_ADDRESS_UPDATED BOOLEAN;
 IS_PREFIX_UPDATED_STR VARCHAR2(5);
 IS_PROVIDER_UPDATED_STR VARCHAR2(5);
 IS_ADDRESS_UPDATED_STR VARCHAR2(5);
BEGIN
  --COLUNA PARA DEBUG CASO NECESSARIO
  --ALTER TABLE EMAIL ADD (LOG_TRIGGER VARCHAR2(4000));
 IS_PREFIX_UPDATED := :NEW.EMAIL_PREFIX != :OLD.EMAIL_PREFIX;
 IS_PROVIDER_UPDATED := :NEW.EMAIL_PROVIDER != :OLD.EMAIL_PROVIDER;
 IS_ADDRESS_UPDATED := :NEW.EMAIL_ADDRESS != :OLD.EMAIL_ADDRESS;
 IS_PREFIX_UPDATED_STR := CASE WHEN IS_PREFIX_UPDATED THEN 'TRUE' ELSE 'FALSE' END;
 IS_PROVIDER_UPDATED_STR := CASE WHEN IS_PROVIDER_UPDATED THEN 'TRUE' ELSE 'FALSE' END;
 IS_ADDRESS_UPDATED_STR := CASE WHEN IS_ADDRESS_UPDATED THEN 'TRUE' ELSE 'FALSE' END;

  -- CASO 1 , insert ou update apenas com EMAIL_ADDRESS
  IF (NOT IS_PREFIX_UPDATED OR NOT IS_PROVIDER_UPDATED ) AND IS_ADDRESS_UPDATED THEN
      :NEW.LOG_TRIGGER := 'CASE 1 :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
      :NEW.EMAIL_PREFIX := substr(:NEW.EMAIL_ADDRESS,1,INSTR(:NEW.EMAIL_ADDRESS,'@')-1);
      :NEW.EMAIL_PROVIDER :=  substr(:NEW.EMAIL_ADDRESS,INSTR(:NEW.EMAIL_ADDRESS,'@')+1,LENGTH(:NEW.EMAIL_ADDRESS));
  -- CASO 2 , insert ou update apenas com PREFIX E PROVIDER
  ELSIF (IS_PREFIX_UPDATED OR IS_PROVIDER_UPDATED) AND NOT IS_ADDRESS_UPDATED THEN
     :NEW.LOG_TRIGGER := 'CASE 2 :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
     :NEW.EMAIL_ADDRESS := :NEW.EMAIL_PREFIX || '@' || :NEW.EMAIL_PROVIDER;
  ELSE 
     :NEW.LOG_TRIGGER := 'ERROR :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
  END IF;
END;

注意：我创建了一个名为" log_trigger"的列。

考虑：旧值是：

EMAILL_ID | EMAIL_PREFIX | EMAIL_PROVIDER | EMAIL_ADDRESS | LOG_TRIGGER | 
763997    | NULL         | NULL           | [e-mail] | NULL        |

我正在尝试执行以下更新语句：

UPDATE EMAIL SET EMAIL_ADDRESS ='[e-mail]' WHERE EMAIL_ID = 763997;

所以，结果是：

EMAILL_ID | EMAIL_PREFIX | EMAIL_PROVIDER | EMAIL_ADDRESS |
763997    | NULL         | NULL           | [e-mail] |
| LOG_TRIGGER | 
| ERRO :: IS_PREFIX_UPDATED:FALSE, IS_PROVIDER_UPDATED:FALSE, IS_ADDRESS_UPDATED:TRUE |

我不明白为什么它不起作用= p

Answer 1

BOOLEAN数据类型可以具有3个值： TRUE， FALSE或 NULL。

您正在比较 NULL值，因此布尔值设置为 NULL而不是 TRUE或 FALSE。

SET SERVEROUTPUT ON;
SET DEFINE OFF;
DECLARE
  a BOOLEAN := NULL;
  b BOOLEAN := NULL;
  c BOOLEAN := TRUE;
  PROCEDURE printState( id IN VARCHAR2, b IN BOOLEAN )
  IS BEGIN
    IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE( id || ' IS NULL' );
    ELSIF b      THEN DBMS_OUTPUT.PUT_LINE( id || ' IS TRUE' );
                 ELSE DBMS_OUTPUT.PUT_LINE( id || ' IS FALSE' );
    END IF;
  END;
BEGIN
  printState( 'a', a );
  printState( 'b', b );
  printState( 'c', c );
  printState( '(!a|!b)&c', ( NOT a OR NOT b ) AND c );
END;
/

输出：

a IS NULL
b IS NULL
c IS TRUE
(!a|!b)&c IS NULL

但更改为：

  a BOOLEAN := FALSE;
  b BOOLEAN := FALSE;
  c BOOLEAN := TRUE;

输出：

a IS FALSE
b IS FALSE
c IS TRUE
(!a|!b)&c IS TRUE

您想做：

IS_PREFIX_UPDATED :=
     ( :NEW.EMAIL_PREFIX IS NULL AND :OLD.EMAIL_PREFIX IS NOT NULL )
  OR ( :NEW.EMAIL_PREFIX IS NOT NULL AND :OLD.EMAIL_PREFIX IS NULL )
  OR ( :NEW.EMAIL_PREFIX IS NOT NULL AND :OLD.EMAIL_PREFIX IS NOT NULL AND :NEW.EMAIL_PREFIX != :OLD.EMAIL_PREFIX ) ;