使用forall立即使用for for Update语句

Question

我需要根据查询获取的数据更新表。 但是，在查询中，我使用ListAgg生成列名称及其相应的值。 我能够通过使用简单的循环使用行处理来更新表，但是我正在探索使用即时执行forall的方法，因为我正在更改更新语句运行时的设置子句。

set serveroutput on;

DECLARE 
l_sql VARCHAR2(4000);
cursor c1 is SELECT 
    OFFER_MIGRATION_ID,
    listagg(column_name||' = nvl('||column_name||','''||DEFAULT_CHAR_VALUE||''')', ','||chr(13)) within group (order by column_name) as set_clause
FROM
(
SELECT  distinct och.OFFER_MIGRATION_ID,
        och.ATTR_ID,
        ch_map.COLUMN_NAME,
        coalesce(och_list.value_name,och.DEFAULT_CHAR_VALUE) as DEFAULT_CHAR_VALUE
 FROM OFFERING_CHARS och,
      CHAR_MAP ch_map,
      OFFERING_CHARS_list och_list
Where ch_map.transformation = 'CONSTANT_DICT'
  and ch_map.attr_id = och.ATTR_ID
  and och.DEFAULT_CHAR_VALUE is not null
  and och_list.offer_migration_id(+) = och.offer_migration_id
  and och_list.characteristic_id(+) = och.characteristic_id
  and och_list.value(+) = och.DEFAULT_CHAR_VALUE
)
GROUP BY OFFER_MIGRATION_ID;  

TYPE char_list IS TABLE OF c1%rowtype index by pls_integer;
l_char_list char_list;
BEGIN 

/******** CONSTANT_FROM_DICT *********/
open c1;
fetch c1 bulk collect into l_char_list;
close c1;

FORALL i IN l_char_list.first .. l_char_list.last

    EXECUTE IMMEDIATE 'UPDATE product_char SET :1 WHERE offer_migration_id = '||l_char_list(i).offer_migration_id
    using l_char_list(i).set_clause;
 
 COMMIT;
 
END;

此块给出错误 -

Error report -
ORA-06550: line 35, column 92:
PLS-00440: FORALL bulk IN-bind variables cannot be used here
ORA-06550: line 35, column 5:
PL/SQL: Statement ignored
ORA-06550: line 35, column 5:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

光标的set_clause列的值将就像 -

OFFER_MIGRATION_ID, SET_CLAUSE

10001   "CCA_CATEGORY = nvl(CCA_CATEGORY,'PD'),CCA_LIFECYCLE_STATUS = nvl(CCA_LIFECYCLE_STATUS,'A'),EQUIPMENT_TYPE = nvl(EQUIPMENT_TYPE,'Devices')"
20001   DATA_ALLOWANCE_SO = nvl(DATA_ALLOWANCE_SO,'9225')
20023   "DURATION = nvl(DURATION,'6'),DATA_ALLOWANCE_SUP_OFFER = nvl(DATA_ALLOWANCE_SUP_OFFER,'9203')"
20024   DATA_ALLOWANCE_SUP_OFFER = nvl(DATA_ALLOWANCE_SUP_OFFER,'9203')

我的db是 -

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

在plsql块中使用的循环，我正在寻找更好执行需要很长时间。

FOR i IN 1..l_char_list.COUNT
 LOOP
    l_sql := 'UPDATE stgdm_product_char SET '||chr(13)||l_char_list(i).set_clause||chr(13)||' WHERE offer_migration_id = '||l_char_list(i).offer_migration_id;
--    dbms_output.put_line(l_sql);
    EXECUTE IMMEDIATE l_sql;
 END LOOP;

Answer 1

您不需要这么复杂。由于目标表具有已知的固定列集，因此您只能在pivot子句中白色列表这些列，然后使用Merge语句（那么您只需要单个SQL语句，不需要动态SQL）。

由于您尚未提供适当的最小可重复的示例，char_map和offering_chars_list进入单个表your_combined_tables ，它可以简单地进行反向工程师：

CREATE TABLE your_combined_tables(
  offer_migration_id, column_name, default_char_value
) AS
SELECT 10001, 'CCA_CATEGORY',             'PD'      FROM DUAL UNION ALL
SELECT 10001, 'CCA_LIFECYCLE_STATUS',     'A'       FROM DUAL UNION ALL
SELECT 10001, 'EQUIPMENT_TYPE',           'Devices' FROM DUAL UNION ALL
SELECT 20001, 'DATA_ALLOWANCE',           '9225'    FROM DUAL UNION ALL
SELECT 20023, 'DURATION',                 '6'       FROM DUAL UNION ALL
SELECT 20023, 'DATA_ALLOWANCE_SUP_OFFER', '9203'    FROM DUAL UNION ALL
SELECT 20024, 'DATA_ALLOWANCE_SUP_OFFER', '9203'    FROM DUAL;

CREATE TABLE product_char(
  offer_migration_id       NUMBER PRIMARY KEY,
  cca_category             VARCHAR2(20),
  cca_lifecycle_status     VARCHAR2(20),
  equipment_type           VARCHAR2(20),
  data_allowance           VARCHAR2(20),
  duration                 VARCHAR2(20),
  data_allowance_sup_offer VARCHAR2(20)
);

INSERT INTO product_char (offer_migration_id)
SELECT 10001 FROM DUAL UNION ALL
SELECT 20001 FROM DUAL UNION ALL
SELECT 20023 FROM DUAL UNION ALL
SELECT 20024 FROM DUAL;

然后您可以使用：

MERGE INTO product_char dst
USING (
  SELECT *
  FROM   your_combined_tables
  PIVOT  (
    MAX(default_char_value)
    FOR column_name IN (
      'CCA_CATEGORY'             AS cca_category,
      'CCA_LIFECYCLE_STATUS'     AS cca_lifecycle_status,
      'EQUIPMENT_TYPE'           AS equipment_type,
      'DATA_ALLOWANCE'           AS data_allowance,
      'DURATION'                 AS duration,
      'DATA_ALLOWANCE_SUP_OFFER' AS data_allowance_sup_offer
    )
  )
) src
ON (src.offer_migration_id = dst.offer_migration_id)
WHEN MATCHED THEN
  UPDATE
  SET cca_category             = NVL(dst.cca_category, src.cca_category),
      cca_lifecycle_status     = NVL(dst.cca_lifecycle_status, src.cca_lifecycle_status),
      equipment_type           = NVL(dst.equipment_type, src.equipment_type),
      data_allowance           = NVL(dst.data_allowance, src.data_allowance),
      duration                 = NVL(dst.duration, src.duration),
      data_allowance_sup_offer = NVL(dst.data_allowance_sup_offer, src.data_allowance_sup_offer);

然后在之后 使用： 合并，该表包含：

effer_migration_id	cca_category	cca_lifecycle_status	设备_TYPE	data_allowance	持续时间	data_allowance_sup_offer
10001	pd	a	设备	null	null	null
20001	null	null	null	9225	null	null
20023	null	null	null	null	6	9203
20024	null	null	null	null	null	9203

db＆lt;＆gt; fiddle