将循环中的oracle关联数组附加到该循环中的另一个关联数组

yzuktlbb 于 2022-11-22 发布在 Oracle

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

我正在尝试在循环内进行批量收集，该循环具有动态SQL，并根据循环的输入执行多次，然后插入到表中（插入193234条记录大约需要4分钟）。为了尝试不同的方法，我考虑在循环中使用批量收集选择，并使用该循环的每次迭代填充集合，比如说第一次迭代给出10行，然后第二个给出0行，第三个返回15行，那么在循环结束时，集合应该包含15条记录。退出循环后，我将使用forall和我在循环内填充的集合一次性执行插入，而不是在循环内的每次迭代中执行插入。
下面是一个类似于应用程序示例代码，我只是使用不同的表来简化问题。

create table test_tab as select owner, table_name, column_name from all_tab_cols where 1=2;

create or replace procedure p_test
as

  l_sql varchar2(4000);
  type t_tab is table of test_tab%rowtype index by pls_integer;
  l_tab t_tab;
  l_tab1 t_tab;
  l_cnt number := 0;

begin

for i in (with tab as (select 'V_$SESSION' table_name from dual 
                       union all
                       select 'any_table'  from dual
                       union all
                       select 'V_$TRANSACTION' from dual
                       union all
                       select 'test_table' from dual
                       )
                    select table_name from tab )
loop
  
  l_sql := 'select owner, table_name, column_name from all_tab_cols where table_name = '''||i.table_name||'''';
  
 -- dbms_output.put_line(l_sql );
  execute immediate l_sql bulk collect into l_tab;
  
  dbms_output.put_line(l_sql ||' > '||l_tab.count);
  l_cnt := l_cnt +1;
  
  if l_tab.count<>0
  then
    l_tab1(l_cnt) := l_tab(l_cnt);
  end if;  
    
end loop;  
  dbms_output.put_line(l_tab1.count);
  forall i in indices of l_tab1
  insert into test_tab values (l_tab1(i).owner, l_tab1(i).table_name, l_tab1(i).column_name);

end;

它只在test_tab表中插入2行，而根据我的系统，它应该插入150行。

select owner, table_name, column_name from all_tab_cols where table_name = 'V_$SESSION' > 103
select owner, table_name, column_name from all_tab_cols where table_name = 'any_table' > 0
select owner, table_name, column_name from all_tab_cols where table_name = 'V_$TRANSACTION' > 47
select owner, table_name, column_name from all_tab_cols where table_name = 'test_table' > 0
2

以上是我系统中的DBMS_OUTPUT，如果示例表名在您的DB中不存在，您可以在循环中更改表名。
Oracle版本-

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

***EDIT***下面的屏幕截图显示了PLSQL_PROFILER的突出显示的计时，其中在第114行的过程中写入了Actual insert...select...，在第132行使用了批量收集和forall（带有嵌套表）以及multiset，看起来我们在这里使用批量收集、multiset和forall至少节省了40秒。

oracle

来源：https://stackoverflow.com/questions/74299875/append-oracle-associative-array-in-a-loop-to-another-associative-array-within-th

2条答案

按热度按时间

mzmfm0qo1#

首先，不要使用关联数组集合，只使用嵌套表集合类型。可以使用MULTISET UNION ALL操作符连接嵌套表集合（避免使用循环）。

CREATE TYPE test_type IS OBJECT(
  owner       VARCHAR2(30),
  table_name  VARCHAR2(30),
  column_name VARCHAR2(30)
);

CREATE TYPE test_tab_type IS TABLE OF test_type;

然后道：

create procedure p_test
as
  l_sql CLOB := 'select test_type(owner, table_name, column_name) from all_tab_cols where table_name = :table_name';
  l_table_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
                                          'V_$SESSION',
                                          'ANY_TABLE',
                                          'V_$TRANSACTION',
                                          'TEST_TABLE'
                                        );
  l_tab  test_tab_type := test_tab_type();
  l_temp test_tab_type;
  l_cnt  number := 0;
BEGIN
  FOR i IN 1 .. l_table_names.COUNT LOOP
    EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_temp USING l_table_names(i);
  
    dbms_output.put_line(
      l_sql || ': ' || l_table_names(i) || ' > '||l_temp.count
    );
    l_cnt := l_cnt +1;
    l_tab := l_tab MULTISET UNION ALL l_temp;  
  END LOOP;

  dbms_output.put_line(l_tab.count);

  insert into test_tab
  SELECT *
  FROM   TABLE(l_tab);
end;
/

其次，如果可以在一个查询中完成所有查询，就不要执行多个查询，而使用IN语句;并且如果您在一条语句中完成所有操作，那么就不需要担心连接集合的问题。

create or replace procedure p_test
as
  l_table_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
                                          'V_$SESSION',
                                          'ANY_TABLE',
                                          'V_$TRANSACTION',
                                          'TEST_TABLE'
                                        );
  l_tab  test_tab_type;
BEGIN
  select test_type(owner, table_name, column_name)
  bulk collect into l_tab
  from all_tab_cols
  where table_name IN (SELECT column_value FROM TABLE(l_table_names));

  dbms_output.put_line(l_tab.count);

  insert into test_tab
  SELECT *
  FROM   TABLE(l_tab);
end;
/

第三，如果可以在一条语句中执行INSERT ... SELECT ...，这将比使用SELECT ... INTO ...然后使用单独的INSERT快得多;这样做意味着您不需要使用任何集合。

create or replace procedure p_test
as
begin
  INSERT INTO test_tab (owner, table_name, column_name)
  select owner, table_name, column_name
  from   all_tab_cols
  where  table_name IN (
           'V_$SESSION',
           'ANY_TABLE',
           'V_$TRANSACTION',
           'TEST_TABLE'
         );
end;
/

fiddle

赞(0）回复(0）举报 2022-11-22

wsxa1bj12#

对于Oracle 19 c，我建议使用SQL_MACRO(table)在适当的位置构建动态SQL，并使用普通SQL。下面是一个基于all_tab_cols构建动态SQL的示例，但它可以是构建此类SQL的任何其他逻辑（具有已知的列名和列顺序）。然后，您可以使用insert ... select ...而不使用PL/SQL，因为SQL宏是在查询解析时处理的。
设定：

create table t1
as
select level as id, mod(level, 2) as val, mod(level, 2) as col
from dual
connect by level < 4;

create table t2
as
select level as id2, mod(level, 2) as val2, mod(level, 2) as col2
from dual
connect by level < 5;

create table t3
as
select level as id3, mod(level, 2) as val3, mod(level, 2) as col3
from dual
connect by level < 6;

用法：
第一页第二页
| 表格名称|列|瓦尔|碳纳米管|
| - -|- -|- -|- -|
| 第一层|一个|一个|2个|
| 第一层|第0页|第0页|一个|
| 第二层|一个|一个|2个|
| 第二层|第0页|第0页|2个|
| 三号航站楼|一个|一个|三个|
| 三号航站楼|第0页|第0页|2个|
fiddle

赞(0）回复(0）举报 2022-11-22

我来回答

将循环中的oracle关联数组附加到该循环中的另一个关联数组

2条答案

相关问题

热门标签

最新问答