时间戳

首页 > 解决方案 > 如何使用 Bulk collect/FORALL 优化 PL/SQL 代码？

sql - 如何使用 Bulk collect/FORALL 优化 PL/SQL 代码？

问题描述

pl/sql 代码花费太多时间来删除所有表中的字符 

begin
  for i in ( select TABLE_NAME,COLUMN_NAME  from all_tab_columns
              where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%' and table_name not like 'BIN%' and data_type ='VARCHAR2'
                and column_name not like '%RR_NO%' and column_name not like '%RRNO%' order by 5 desc  )
  loop
    execute immediate  'UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || '= REPLACE (' ||i.COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') WHERE INSTR('||i.COLUMN_NAME ||',CHR(10))>0';
    execute immediate  'UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || '= REPLACE (' ||i.COLUMN_NAME ||',CHR(13),'||chr(39)||chr(39)|| ') WHERE INSTR('||i.COLUMN_NAME ||',CHR(13))>0';
    dbms_output.put_line(i.TABLE_NAME||' - '||i.COLUMN_NAME||'- '||sql%rowcount);
  end loop;
  commit;
end;

请通过使用 Bulk collect/FORALL 来帮助我

现在我正在手动使用

select ' UPDATE '|| TABLE_NAME || ' SET ' ||COLUMN_NAME || '= REPLACE (' ||COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') WHERE INSTR('||COLUMN_NAME ||',CHR(10))>0;'
   from all_tab_columns
   where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%' and table_name not like 'BIN%'
   and data_type ='VARCHAR2' and column_name not like '%RR_NO%' and column_name not like '%RRNO%';

select ' UPDATE '|| TABLE_NAME || ' SET ' ||COLUMN_NAME || '= REPLACE (' ||COLUMN_NAME ||',CHR(13),'||chr(39)||chr(39)|| ') WHERE INSTR('||COLUMN_NAME ||',CHR(13))>0;'
   from all_tab_columns
   where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%'
   and table_name not like 'BIN%' and data_type ='VARCHAR2' and column_name not like '%RR_NO%' and column_name not like '%RRNO%';

复制此 SQL 结果并手动执行。

运行脚本需要 1 个多小时。

DECLARE
   CURSOR c1 IS SELECT TABLE_NAME,COLUMN_NAME  FROM ALL_TAB_COLUMNS
                    WHERE OWNER='BILL' AND DATA_LENGTH > 1 AND TABLE_NAME NOT LIKE 'SYS_EXPORT_SCHEMA%' AND TABLE_NAME NOT LIKE 'BIN%' AND DATA_TYPE ='VARCHAR2'
                    AND COLUMN_NAME NOT LIKE '%RR_NO%' AND COLUMN_NAME NOT LIKE '%RRNO%';
        TYPE RecList IS TABLE OF c1%ROWTYPE;
        recs RecList;
BEGIN
    BEGIN
        OPEN c1;
            LOOP
                FETCH c1 BULK COLLECT INTO recs LIMIT 500;
                EXIT WHEN c1%NOTFOUND;
                    FOR i IN recs.FIRST .. recs.LAST
                        LOOP
                            EXECUTE IMMEDIATE  'UPDATE '|| recs(i).TABLE_NAME || ' SET ' ||recs(i).COLUMN_NAME || '= REPLACE (' ||recs(i).COLUMN_NAME ||',CHR(10),'||CHR(39)||CHR(39)|| ') WHERE INSTR('||recs(i).COLUMN_NAME ||',CHR(10))>0';
                            dbms_output.put_line(recs(i).TABLE_NAME||'  -  '||recs(i).COLUMN_NAME||'  -  '||sql%rowcount);
                            EXECUTE IMMEDIATE  'UPDATE '|| recs(i).TABLE_NAME || ' SET ' ||recs(i).COLUMN_NAME || '= REPLACE (' ||recs(i).COLUMN_NAME ||',CHR(13),'||CHR(39)||CHR(39)|| ') WHERE INSTR('||recs(i).COLUMN_NAME ||',CHR(13))>0';
                            dbms_output.put_line(recs(i).TABLE_NAME||'  -  '||recs(i).COLUMN_NAME||'  -  '||sql%rowcount);
                        END LOOP;
            END LOOP;
            exception when others then
            dbms_output.put_line(sqlcode||sqlerrm);
        CLOSE C1;
    END;
commit;
END;
/

我写了一个查询正在工作，是否有任何提示可以进一步优化此查询。

标签： sqloracleplsqlplsqldeveloperbulk-collect

解决方案

以下内容未经测试，只是为了给您一个想法，请尝试准备值，首先发送将它们添加到更新中。然后确保您在覆盖该列的表上有一个索引。当您使用函数时，DML 将更难读取索引。同样以这种方式，您正在使用绑定变量方法（软解析），它将更好地提高性能。

  select REPLACE (' ||i.COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') into R_COL from dual;

  select INSTR('||i.COLUMN_NAME ||',CHR(10)) into I_COL from dual;

UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || ' = R_COL WHERE I_COL >0;

推荐阅读