oracle PL/SQL使用选择值作为表名更新多个表

7lrncoxx 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(98)

我能问一下..
我将更新几个表..我是插入表的列表，我想更新..
下面是查询：

insert into dgtmp_nama_tbl
select 'dgtmp_nf_vpnip_upd' from dual
union
select 'dgtmp_nf_metroe_upd' from dual
union
select 'dgtmp_nf_astinet_upd' from dual
union
select 'dgtmp_nf_iptransit_upd' from dual
union
select 'dgtmp_nf_cndc_upd' from dual
union
select 'dgtmp_nf_sdwan_upd' from dual
union
select 'dgtmp_nf_slwdm_upd' from dual
union
select 'dgtmp_nf_sldigital_upd' from dual
union
select 'dgtmp_nf_neulayer_upd' from dual
union
select 'dgtmp_nf_neu_intconnect_upd' from dual

然后我用PL/SQL更新表。但它会返回这样一个错误：
SQL错误[6550] [65000]：ORA-06550：第8行，第12列：PL/SQL：ORA-00942：404-页面不存在页面不存在.第8行，第3栏：PL/SQL：SQL语句被忽略
下面是我的pl/sql脚本：

declare
    cursor c_list_tbl
    is
    select nama_tbl from dgtmp_nama_tbl;
begin
    for i in c_list_tbl
    loop
        update i.nama_tbl a
        set a.divisi = ( select a.divisi from rmart_accnum_all_divisi_2023 b where a.account_num = b.account_num ),
        a.segmen = ( select a.segmen from rmart_accnum_all_divisi_2023 b where a.account_num = b.account_num );
    commit;
    end loop;
end;

有谁能帮我检查一下有什么问题吗？对不起，我的英语不好，我希望你们能明白我在说什么。
谢谢提前…
我已经测试过了，返回错误

oracle

来源：https://stackoverflow.com/questions/77087238/pl-sql-update-several-table-using-select-values-as-a-table-name

2条答案

按热度按时间

dpiehjr41#

你需要动态SQL来实现这一点。

DECLARE
   CURSOR c_list_tbl IS SELECT nama_tbl FROM dgtmp_nama_tbl;

   l_str  VARCHAR2 (1000);
BEGIN
   FOR i IN c_list_tbl
   LOOP
      l_str := 'UPDATE ' || i.nama_tbl || ' a
         SET a.divisi =
                (SELECT b.divisi
                   FROM rmart_accnum_all_divisi_2023 b
                  WHERE a.account_num = b.account_num),
             a.segmen =
                (SELECT b.segmen
                   FROM rmart_accnum_all_divisi_2023 b
                  WHERE a.account_num = b.account_num)';

     EXECUTE IMMEDIATE l_str;
   END LOOP;

   COMMIT;
END;

赞(0）回复(0）举报 2023-10-16

yv5phkfx2#

你需要使用动态SQL（并且应该清理你的用户输入）：

BEGIN
    FOR i IN (select nama_tbl from dgtmp_nama_tbl) LOOP
      EXECUTE IMMEDIATE 'MERGE INTO ' || DBMS_ASSERT.SIMPLE_SQL_NAME(i.nama_tbl) || ' dst
        USING (
          SELECT account_num
          FROM   rmart_accnum_all_divisi_2023
        ) src
        ON (src.account_num = dst.account_num)
        WHEN MATCHED THEN
          UPDATE
          SET divisi = dst.divisi,
              segmen = dst.segmen';
  END LOOP;
  COMMIT;
END;
/

但是，您正在将这些值更新为相同的值。我认为你的代码应该是（并且可以包括错误处理）：

DECLARE
  table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
BEGIN
    FOR i IN (select nama_tbl from dgtmp_nama_tbl) LOOP
      BEGIN
        EXECUTE IMMEDIATE 'MERGE INTO ' || DBMS_ASSERT.SIMPLE_SQL_NAME(i.nama_tbl) || ' dst
          USING (
            SELECT account_num,
                   divisi,
                   segmen
            FROM   rmart_accnum_all_divisi_2023
          ) src
          ON (src.account_num = dst.account_num)
          WHEN MATCHED THEN
            UPDATE
            SET divisi = src.divisi,
                segmen = src.segmen';
    EXCEPTION
      WHEN table_does_not_exist THEN
        DBMS_OUTPUT.PUT_LINE(i.nama_tbl || ' does not exist.');
    END;
  END LOOP;
  COMMIT;
END;
/

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle PL/SQL使用选择值作为表名更新多个表

2条答案

相关问题

热门标签

最新问答