sql - 如何抽象出子查询？

Question

我有一个查询，需要检查所有字段的值是否都在有效代码列表中。现在我一遍又一遍地调用相同的子查询。我想将子查询抽象出来，以便它更快并且代码不会重复。这是有问题的查询:

select count(*)
into cnt
from pdv_validcodes c
where c.code_type = 'YNNA'
 and (upper(:new.spec_1) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_1 is null)
 and (upper(:new.spec_2) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_2 is null)
 and (upper(:new.spec_3) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_3 is null)
 and (upper(:new.spec_4) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_4 is null)
 and (upper(:new.spec_5) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_5 is null)
 and (upper(:new.spec_6) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_6 is null)
 and (upper(:new.spec_7) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_7 is null)
 and (upper(:new.spec_8) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_8 is null)
 and (upper(:new.spec_9) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_9 is null)
 and (upper(:new.spec_10) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_10 is null)
 and (upper(:new.add_spec_1) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_1 is null)
 and (upper(:new.add_spec_2) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_2 is null)
 and (upper(:new.add_spec_3) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_3 is null)
 and (upper(:new.add_spec_4) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_4 is null)
 and (upper(:new.add_spec_5) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_5 is null);

Answer 1

迈克尔，

我没有机会对此进行测试，但由于它是触发代码，因此是 PL/SQL，因此可能会起作用:

CREATE OR REPLACE TYPE "strarray" AS TABLE OF VARCHAR2 (255)
/


DECLARE
   validcodes strarray;
BEGIN
   SELECT code 
     BULK COLLECT INTO validcodes
     FROM pdv_validcodes
    WHERE code_type = 'YNNA'
   UNION
   SELECT 'NULL'
     FROM dual;

   IF  NVL(upper(:new.spec_1), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_2), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_3), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_4), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_5), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_6), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_7), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_8), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_9), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_10), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_1), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_2), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_3), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_4), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_5), 'NULL') MEMBER OF validcodes
   THEN
      -- Business logic
   ELSE
      -- Business logic
   END IF;
END;