database - Oracle:使用动态查询更新多列

Question

我正在尝试将 NVARCHAR2 类型的所有列更新为数据库中的某个随机字符串。我遍历了数据库中类型为 nvarchar2 的所有列，并为每一列执行了更新语句。

for i in (
    select 
        table_name,
        column_name 
    from 
        user_tab_columns
    where 
        data_type = 'NVARCHAR2'
    ) loop
execute immediate 
    'update ' || i.table_name || 'set ' || i.column_name ||
    ' = DBMS_RANDOM.STRING(''X'', length('|| i.column_name ||'))
    where ' || i.column_name || ' is not null';

为了提高效率，我不想为 nvarchar2 类型的每一列运行更新语句，而是想使用单个更新语句更新特定表的所有 nvarchar 列(即，每个表一个更新语句)。为此，我尝试将表中的所有 nvarchar 列批量收集到临时存储中。但是，我坚持为此编写动态更新语句。你能帮我解决这个问题吗？提前致谢!

Answer 1

你可以试试这个。但是，根据您的表格，它可能不是最快的解决方案。

for aTable in (
    select table_name,
        listagg(column_name||' = nvl2('||column_name||', DBMS_RANDOM.STRING(''XX'', length('||column_name||')), NULL)') WITHIN GROUP (ORDER BY column_name) as upd,
        listagg(column_name) WITHIN GROUP (ORDER BY column_name) as con
    from user_tab_columns 
    where DATA_TYPE = 'NVARCHAR2'
    group by table_name
  ) loop

    execute immediate 
       'UPDATE '||aTable.table_name ||
       ' SET '||aTable.upd ||
       ' WHERE COALESCE('||aTable.con||') IS NOT NULL';

end loop;

结果更新(使用 DBMS_OUTPUT.PUT_LINE(..) 验证)应该如下所示:

UPDATE MY_TABLE SET 
   COL_A = nvl2(COL_A, DBMS_RANDOM.STRING('XX', length(COL_A)), NULL),
   COL_B = nvl2(COL_B, DBMS_RANDOM.STRING('XX', length(COL_B)), NULL)
WHERE COALESCE(COL_A, COL_B) IS NOT NULL;