oracle - 将 SP 中的有效记录插入到有效表中，将无效记录插入到 Invalid SP 中

Question

我有两个存储过程，它们过滤表上的记录并给出结果。下面是存储过程。

SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID
               , TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
from  APP_FTTX.span@SAT sp
INNER JOIN APP_FTTX.transmedia@SAT tm  -- transmedia added
on sp.RJ_SPAN_ID = tm.RJ_SPAN_ID
WHERE length(sp.RJ_SPAN_ID) = length(tm.RJ_SPAN_ID)
and sp.RJ_MAINTENANCE_ZONE_CODE = tm.RJ_MAINTENANCE_ZONE_CODE
and sp.INVENTORY_STATUS_CODE = tm.INVENTORY_STATUS_CODE
AND LENGTH(sp.RJ_SPAN_ID) = 21
AND (sp.RJ_SPAN_ID LIKE ('%SPN%') 
     OR sp.RJ_SPAN_ID LIKE ('%SPQ%') 
     OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
     OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
AND (sp.RJ_SPAN_ID LIKE ('%_BU') 
     OR sp.RJ_SPAN_ID LIKE ('%_MP'))
AND sp.INVENTORY_STATUS_CODE = 'IPL'
AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE ('%\_9%') ESCAPE '\' 
     OR sp.RJ_INTRACITY_LINK_ID IS NULL);

上面的 SP 返回 4 条记录，这些是我想要插入到表中的有效记录。

现在有另一个 SP 低于它返回 7 条记录。

SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID
               , TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
from  APP_FTTX.span@SAT sp
-- INNER JOIN APP_FTTX.transmedia@SAT tm  -- transmedia added
--  on sp.RJ_SPAN_ID = tm.RJ_SPAN_ID
-- WHERE length(sp.RJ_SPAN_ID) = length(tm.RJ_SPAN_ID)
--  and sp.RJ_MAINTENANCE_ZONE_CODE = tm.RJ_MAINTENANCE_ZONE_CODE
--   and sp.INVENTORY_STATUS_CODE = tm.INVENTORY_STATUS_CODE
WHERE LENGTH(sp.RJ_SPAN_ID) = 21
AND (sp.RJ_SPAN_ID LIKE ('%SPN%') 
     OR sp.RJ_SPAN_ID LIKE ('%SPQ%') 
     OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
     OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
AND (sp.RJ_SPAN_ID LIKE ('%_BU') 
     OR sp.RJ_SPAN_ID LIKE ('%_MP'))
AND sp.INVENTORY_STATUS_CODE = 'IPL'
AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE ('%\_9%') ESCAPE '\' 
     OR sp.RJ_INTRACITY_LINK_ID IS NULL);

SP 是类似的。第二个 SP 中有 3 条记录无效。所以我想要的是：我想将其他 3 条无效记录插入到另一个表中。

请建议如何实现这一目标。

更新

根据 Alex 的评论，我在下面的代码中实现了减号。

SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID, TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
                                    from  APP_FTTX.span@SAT sp
                                   -- INNER JOIN APP_FTTX.transmedia@SAT tm  -- transmedia added
                                  --  on sp.RJ_SPAN_ID = tm.RJ_SPAN_ID
                                   -- WHERE length(sp.RJ_SPAN_ID) = length(tm.RJ_SPAN_ID)
                                  --  and sp.RJ_MAINTENANCE_ZONE_CODE = tm.RJ_MAINTENANCE_ZONE_CODE
                                 --   and sp.INVENTORY_STATUS_CODE = tm.INVENTORY_STATUS_CODE
                                    WHERE LENGTH(sp.RJ_SPAN_ID) = 21
                                    AND (sp.RJ_SPAN_ID LIKE ('%SPN%') OR sp.RJ_SPAN_ID LIKE ('%SPQ%') OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
                                    OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
                                    AND (sp.RJ_SPAN_ID LIKE ('%_BU') OR sp.RJ_SPAN_ID LIKE ('%_MP'))
                                    AND sp.INVENTORY_STATUS_CODE = 'IPL'
                                    AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
                                    AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE ('%\_9%') ESCAPE '\' OR sp.RJ_INTRACITY_LINK_ID IS NULL)                                        
            MINUS

       SELECT distinct  TO_CHAR(sp.RJ_SPAN_ID) AS SPAN_ID, 
          TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
                                    from  APP_FTTX.span@SAT sp
                                    INNER JOIN APP_FTTX.transmedia@SAT tm  -- 
                transmedia added
                                     on sp.RJ_SPAN_ID = tm.RJ_SPAN_ID
                                    WHERE length(sp.RJ_SPAN_ID) = 
               length(tm.RJ_SPAN_ID)
                                    and sp.RJ_MAINTENANCE_ZONE_CODE = 
              tm.RJ_MAINTENANCE_ZONE_CODE
                                    and sp.INVENTORY_STATUS_CODE = 
              tm.INVENTORY_STATUS_CODE
                                    AND LENGTH(sp.RJ_SPAN_ID) = 21
                                    AND (sp.RJ_SPAN_ID LIKE ('%SPN%') OR 
                sp.RJ_SPAN_ID LIKE ('%SPQ%') OR sp.RJ_SPAN_ID LIKE ('%SPR%')    
                                        OR sp.RJ_SPAN_ID LIKE ('%SPS%'))
                                    AND (sp.RJ_SPAN_ID LIKE ('%_BU') OR 
               sp.RJ_SPAN_ID LIKE ('%_MP'))
                                    AND sp.INVENTORY_STATUS_CODE = 'IPL'
                                    AND sp.RJ_MAINTENANCE_ZONE_CODE = 
              'INMUNVMB01'
                                    AND (sp.RJ_INTRACITY_LINK_ID NOT LIKE 
               ('%\_9%') ESCAPE '\' OR sp.RJ_INTRACITY_LINK_ID IS NULL);

标签： oraclejoinstored-proceduresinner-joininsert-update

Answer 1

我认为你最好做一个LEFT JOIN.

SELECT DISTINCT sp.RJ_SPAN_ID AS SPAN_ID,
                sp.RJ_MAINTENANCE_ZONE_CODE AS MAINT_ZONE_CODE,
                SYSDATE AS MSG_DATE
FROM   APP_FTTX.span@SAT sp
WHERE  LENGTH(sp.RJ_SPAN_ID) = 21
AND REGEXP_LIKE(sp.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
AND sp.INVENTORY_STATUS_CODE = 'IPL'
AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
AND NVL(INSTR(sp.RJ_INTRACITY_LINK_ID, '_'), 1) > 0
AND NOT EXISTS (SELECT 'X'
                FROM APP_FTTX.transmedia@SAT tm
                WHERE tm.rj_span_id = sp.rj_span_id
                AND tm.rj_maintenance_zone_code = sp.rj_maintenance_zone_code
                AND tm.inventory_status_code = sp.inventory_status_code);

注意NULL检查tm.rj_span_id。鉴于我们的其他逻辑，应该保证填充该列。既然是NULL，我们就知道没有找到匹配项。

我还做了一些其他的调整：

1. length(sp.RJ_SPAN_ID) = length(tm.RJ_SPAN_ID)在底部做没有用。您已经在JOIN. 如果它们的值相等，则长度必须相同。
2. 考虑使用REGEXP_LIKE来简化你的逻辑。就像是REGEXP_LIKE(sp.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
3. 删除了TO_CHAR列列表中的 。正如评论中所讨论的，它们已经是字符串。
4. 我将您的NOT LIKEand更改IS NULL为使用NVLAND INSTR，但这只是我的偏好（我一直在玩Code Golf，它影响了我的大脑）

至于您的INSERT，只需将其附加SELECT到 anINSERT INTO table_name就可以了。像这样：

INSERT INTO log_table_name
  SELECT DISTINCT sp.RJ_SPAN_ID AS SPAN_ID,
                  sp.RJ_MAINTENANCE_ZONE_CODE AS MAINT_ZONE_CODE,
                  SYSDATE AS UPDATED_DATE,
                  'YOUR_USERNAME' AS USERNAME
  FROM   APP_FTTX.span@SAT sp
  WHERE  LENGTH(sp.RJ_SPAN_ID) = 21
  AND REGEXP_LIKE(sp.rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
  AND sp.INVENTORY_STATUS_CODE = 'IPL'
  AND sp.RJ_MAINTENANCE_ZONE_CODE = 'INMUNVMB01'
  AND NVL(INSTR(sp.RJ_INTRACITY_LINK_ID, '_'), 1) > 0
  AND NOT EXISTS (SELECT 'X'
                  FROM APP_FTTX.transmedia@SAT tm
                  WHERE tm.rj_span_id = sp.rj_span_id
                  AND tm.rj_maintenance_zone_code = sp.rj_maintenance_zone_code
                  AND tm.inventory_status_code = sp.inventory_status_code);  

注意：我对UPDATED_BY列进行了硬编码，因为我不知道正确的值。