UPDATE 多个表

针对多个表，执行同样的操作。

procedure pkg_k_updateAac001(
      prm_aac001_old                IN      VARCHAR2,
      prm_aac001_new                IN      VARCHAR2,
      prm_AppCode                   OUT     VARCHAR2,  -- 执行状态代码
      prm_ErrorMsg                  OUT     VARCHAR2)  -- 失败信息描述

   is
         v_exeSQL  varchar2(200);
         v_tableName varchar2(50);
         cursor updateTable_name
           is 
          select table_name 
            from user_tables 
           where table_name in ('KC45','KC20','KC41K1','KC41','KC31',
                                'KC19','KC19K1','KC19K2','KC21','KC24','KC24K1','KC25',
                                'KC28','KC40','KC40K1','KC40K2'); 
   begin    
        prm_AppCode  := pkg_Constant.gn_def_OK;
        prm_ErrorMsg := '';

        open updateTable_name;
        loop
            fetch updateTable_name into v_tableName;
            exit when updateTable_name%notfound;
          select 'update '||v_tableName||
                 ' set aac001 = '''||prm_aac001_new||
                 ''' where aac001 = '''||prm_aac001_old||''''
            into v_exeSQL
            from dual;
            execute immediate v_exeSQL;
        end loop;
        close updateTable_name;

   -- 成功处理
    <<label_OK>>

    -- 关闭仍然打开的游标
    RETURN;

    -- 处理失败
    <<label_ERROR>>
    prm_AppCode := pkg_Constant.gn_def_ERR;
    RETURN;

    EXCEPTION
    -- WHEN NO_DATA_FOUND THEN
    -- WHEN TOO_MANY_ROWS THEN
    -- WHEN DUP_VAL_ON_INDEX THEN
    WHEN OTHERS THEN
      prm_AppCode  := pkg_Constant.gn_def_ERR;
      prm_ErrorMsg := prm_ErrorMsg || SQLERRM;
      -- 关闭仍然打开的游标
   end pkg_k_updateAac001;