转载

Oracle内联视图更新遇到的问题.

    遇到一个批量更新的需求.
    我打算用内联视图更新+where in list的技巧处理

  1. UPDATE (
  2.     SELECT /*+ BYPASS_UJVC */ *
  3.     FROM mvbox_space.music_original t1
  4.         INNER JOIN (
  5.             SELECT REGEXP_SUBSTR(value_str, '[^,]+', 1, 1) AS p1, REGEXP_SUBSTR(value_str, '[^,]+', 1, 2) AS p2
  6.             FROM (
  7.                 SELECT SUBSTR(inlist, INSTR(inlist, ';', 1, LEVEL) + 1, INSTR(inlist, ';', 1, LEVEL + 1) - INSTR(inlist, ';', 1, LEVEL) - 1) AS value_str, level AS l
  8.                 FROM (
  9.                     SELECT ';' || '20077,1;20078,2' || ';' AS inlist
  10.                     FROM DUAL
  11.                 )
  12.                 CONNECT BY LEVEL <= LENGTH('20077,1;20078,2') - LENGTH(REPLACE('20077,1;20078,2', ';', NULL)) + 1
  13.             )
  14.         ) t2 ON t1.opus_id = t2.p1
  15. )
  16. SET visit_num = nvl(visit_num, 0) + p2, total_today = nvl(total_today, 0) + p2, total_this_week = nvl(total_this_week, 0) + p2, total_this_month = nvl(total_this_month, 0) + p2
    在测试库10.2.0.1通过.
    但是拿到线上10.2.0.4,居然报错,这个内部的HINT没有生效.
    后来改写为
  1. MERGE INTO mvbox_space.music_original t1
  2. USING (
  3.     SELECT REGEXP_SUBSTR(value_str, '[^,]+', 1, 1) AS p1, REGEXP_SUBSTR(value_str, '[^,]+', 1, 2) AS p2
  4.     FROM (
  5.         SELECT SUBSTR(inlist, INSTR(inlist, ';', 1, LEVEL) + 1, INSTR(inlist, ';', 1, LEVEL + 1) - INSTR(inlist, ';', 1, LEVEL) - 1) AS value_str, LEVEL AS l
  6.         FROM (
  7.             SELECT ';' || '20077,1;20078,2' || ';' AS inlist
  8.             FROM DUAL
  9.         )
  10.         CONNECT BY LEVEL <= LENGTH('20077,1;20078,2') - LENGTH(REPLACE('20077,1;20078,2', ';', NULL)) + 1
  11.     )
  12. ) t2 ON t1.opus_id = t2.p1
  13. WHEN MATCHED THEN UPDATE SET t1.visit_num = NVL(t1.visit_num, 0) + t2.p2, t1.total_today = NVL(t1.total_today, 0) + t2.p2, t1.total_this_week = NVL(t1.total_this_week, 0) + t2.p2, t1.total_this_month = NVL(t1.total_this_month, 0) + t2.p2

   
正文到此结束
Loading...