如何在存储过程中将光标输出存储为字符串

如果您使用游标进行循环,则不需要麻烦定义游标记录——这就是游标进行循环的美妙之处!

您的代码可以被重写为类似(N.B.未经测试)的内容:

CREATE OR REPLACE PROCEDURE sc2_merge(source_table_name      IN VARCHAR2,
                                      destination_table_name IN VARCHAR2,
                                      bk_column_name         IN VARCHAR2 DEFAULT 'bk_hash_key',)

 AS
  merge_cmd      VARCHAR(32767);

  CURSOR update_record IS
    SELECT listagg('t.' || column_name || ' = ' || 's.' || column_name, ',') col_list
    FROM   all_tab_cols
    WHERE  table_name = destination_table_name
    AND    column_name NOT IN ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');

BEGIN

  FOR update_set_cmd IN update_record
  LOOP
    merge_cmd := 'MERGE INTO ' || destination_table_name || ' t
                         USING ' || source_table_name || ' s
                         ON (t.' || bk_column_name || '= s.' || bk_column_name || ')
                         WHEN MATCHED THEN
                                          UPDATE SET ' || update_set_cmd.col_list;

    EXECUTE IMMEDIATE merge_cmd;
  END LOOP;

END;
/

注意,为了能够引用记录中的字段,我给光标中的计算列提供了一个别名( col_list )。然后要访问该字段,请使用以下格式 <record_name>.<field_name> ,即。 update_set_cmd.col_list 。

此外,我在循环中移动了merge语句,因为您将只在一行上循环。

因为是这种情况,所以实际上根本不需要循环,只需将列选择到一个变量中即可:

CREATE OR REPLACE PROCEDURE sc2_merge(source_table_name      IN VARCHAR2,
                                      destination_table_name IN VARCHAR2,
                                      bk_column_name         IN VARCHAR2 DEFAULT 'bk_hash_key',)

 AS
  merge_cmd  VARCHAR2(32767);
  v_col_list VARCHAR2(32767);

BEGIN

  SELECT listagg('t.' || column_name || ' = ' || 's.' || column_name, ',') col_list
  INTO   v_col_list
  FROM   all_tab_cols
  WHERE  table_name = destination_table_name
  AND    column_name NOT IN ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');

  merge_cmd := 'MERGE INTO ' || destination_table_name || ' t
                         USING ' || source_table_name || ' s
                         ON (t.' || bk_column_name || '= s.' || bk_column_name || ')
                         WHEN MATCHED THEN
                                          UPDATE SET ' || v_col_list;

  EXECUTE IMMEDIATE merge_cmd;

END;
/

当没有为这个隐式游标返回行或返回了太多行时,您不需要担心错误处理,因为您正在对整个数据集使用聚合函数(即,您没有 group by 子句)-这将始终返回一行。

您正在获取 记录 从光标转换为字符串;那行不通。您需要从该记录中获取一个字段,在这种情况下,这也意味着您需要对游标查询中的列表达式进行别名:

...
CURSOR update_record IS
    SELECT  listagg('t.'|| column_name ||' = '|| 's.'|| column_name, ',') AS cols
--------------------------------------------------------------------------^^^^^^^
  FROM all_tab_cols
...
     FOR v_update_record IN update_record LOOP
              update_set_cmd:= v_update_record.cols;
----------------------------------------------^^^^^
     END LOOP;
...

而且你不需要申报 v_update_record ,这在您的游标循环中是隐含的。

不过,您并不真正需要光标,因为总有一行您可以直接选择:

BEGIN
   SELECT  listagg('t.'|| column_name ||' = '|| 's.'|| column_name, ',')
   INTO update_set_cmd
     FROM all_tab_cols
    WHERE table_name = destination_table_name
       and column_name not in ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');

   merge_cmd:= ...
...

无论哪种方式,一旦过程完成并尝试执行,动态SQL都会出错,因为后面缺少一个空格 INTO :

   merge_cmd:= 'MERGE INTO' || destination_table_name || ' t

需要

   merge_cmd:= 'MERGE INTO ' || destination_table_name || ' t

但它还没有走那么远。。。

还要记住,因为你正在做:

 WHERE table_name = destination_table_name

你传递的值 destination_table_name 必须与数据字典中出现的情况相同。当您稍后使用未加引号的名称时,这意味着您没有使用带引号的标识符(这很好),在这种情况下,名称必须以大写形式传递,或者您可以在查询中更改它:

 WHERE table_name = UPPER(destination_table_name)

如果你确实引用了标识符,那么不要这样做,但你必须引用动态中的标识符 MERGE 。