SQL触发器开发求助：如何根据工单编号获取CONTAINERS_NB值

我正在开发SQL触发器，需要根据工单编号（WO_NB）与物料编号（ITEM_NB）匹配，从对应的METADATASET中获取CONTAINERS_NB的值（示例中该值为2），并将其赋值给sCont_Total_Number用于后续插入操作。目前已编写了如下游标代码：

CURSOR C1 (insSpoolID IN CHAR) IS
select substr(data_valuestring,8) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!WO_NB!%%' ESCAPE '!';

CURSOR C2 (insSpoolID IN CHAR) IS
select substr(data_valuestring,14) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!PRODUCTCODE!%%' ESCAPE '!';

CURSOR C3 (insSpoolID IN CHAR) IS
select substr(data_valuestring,9) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!CONTNO!%%' ESCAPE '!';

CURSOR C4 (insSpoolID IN CHAR) IS
select substr(data_valuestring,8) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!BAGNO!%%' ESCAPE '!';

CURSOR C5 (insSpoolID IN CHAR) IS
select substr(data_valuestring,12) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!CONTAINER!%%' ESCAPE '!';

CURSOR C6 (insSpoolID IN CHAR) IS
select substr(data_valuestring,8) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!BATCH!%%' ESCAPE '!';

CURSOR C7 (insSpoolID IN CHAR) IS
select substr(data_valuestring,7) from E2S_SPOOLERDATA
where SPOOL_ID = insSpoolID AND DATA_VALUESTRING LIKE '!ITEM!%%' ESCAPE '!';

BEGIN
If :new.SPOOL_STATUS = 'S' then

   sSpoolID:= :new.spool_id;
   sDCMTCode:= :new.DCMT_CODE;

If sDCMTCode like '%VARPESEE%' then
     OPEN C1(sSpoolID);
     OPEN C2(sSpoolID);
     OPEN C3(sSpoolID);
     OPEN C4(sSpoolID);
     FETCH C1 INTO sWONb;
     FETCH C2 INTO sProductCode;
     FETCH C3 INTO sContNo;
     Fetch C7 INTO sItem;

-- 核心需求：获取CONTAINERS_NB值，赋值给sCont_Total_Number

Insert into SPOOLERDATA (SPOOL_ID,SPOOL_INDEX,SPOOL_STATUS,DATA_TABLE,DATA_ROW,DATA_COLUMN,DATA_TYPE,DATA_LENGTH,DATA_VALUENUMBER,DATA_VALUEDATE,DATA_VALUESTRING) values (sSpoolID,'999','S','ROW','999','VALUE','VARCHAR2','250','0',null,'%CONT_PRINT_NUMBER_TOTAL%' || sCont_Total_Number);
     end if;

END IF;

此前尝试的查询（不确定正确性） #

SELECT TO_CHAR(VALUE) FROM E2S_METADATAVALUE_MAN WHERE NAME='WO_NB' 
      AND FK_METADATASET=
             (SELECT FK_METADATASET from E2S_METADATAVALUE_MAN 
                    where NAME='FORMULA_CATEGORY' 
                               AND FK_METADATASET IN 
             (SELECT FK_METADATASET FROM E2S_METADATAVALUE_MAN 
                    where dbms_lob.instr (Value,insC1WO) >0));  

正确的查询实现 #

要实现根据WO_NB和ITEM_NB匹配获取CONTAINERS_NB的值，需先定位同时满足两个条件的FK_METADATASET，再通过该关联ID查询目标值。以下是两种可靠的实现方式：

方式1：使用INTERSECT关联条件

SELECT TO_CHAR(VALUE) INTO sCont_Total_Number
FROM E2S_METADATAVALUE_MAN
WHERE NAME = 'CONTAINERS_NB'
  AND FK_METADATASET IN (
    -- 获取匹配WO_NB的METADATASET
    SELECT FK_METADATASET
    FROM E2S_METADATAVALUE_MAN
    WHERE NAME = 'WO_NB' AND VALUE = sWONb
    -- 交集：同时匹配ITEM_NB的METADATASET
    INTERSECT
    SELECT FK_METADATASET
    FROM E2S_METADATAVALUE_MAN
    WHERE NAME = 'ITEM_NB' AND VALUE = sItem
  );

方式2：使用JOIN关联（性能更优）

SELECT TO_CHAR(mv_cont.VALUE) INTO sCont_Total_Number
FROM E2S_METADATAVALUE_MAN mv_wo
JOIN E2S_METADATAVALUE_MAN mv_item 
  ON mv_wo.FK_METADATASET = mv_item.FK_METADATASET
JOIN E2S_METADATAVALUE_MAN mv_cont 
  ON mv_wo.FK_METADATASET = mv_cont.FK_METADATASET
WHERE mv_wo.NAME = 'WO_NB' AND mv_wo.VALUE = sWONb
  AND mv_item.NAME = 'ITEM_NB' AND mv_item.VALUE = sItem
  AND mv_cont.NAME = 'CONTAINERS_NB';

关键说明 #

• 确保sWONb和sItem已通过游标C1、C7正确获取到值
• 如果数据中可能存在多个匹配的CONTAINERS_NB记录，建议添加ROWNUM = 1或确保业务数据唯一性，避免触发多行返回错误
• 若VALUE字段是LOB类型，可保留dbms_lob.instr或直接用VALUE = sWONb（需根据实际字段类型调整）

内容的提问来源于stack exchange，提问作者Steven Schorn