pl/sql--COLLECTIONS AND RECORD

I have two tables called "AA" 开发者_开发技巧and "AB"

• In table "AA" I have columns like cust_no, name, address, etc..
• In table "AB" I have one column cuno

I need to fetch cust_no from "AA" table and put it into cuno column of "AB" table.

I tried some code here...

declare
  Type Ty_Handoff_Pc Is Table Of aa%Rowtype Index By Binary_Integer;
  Type sam Is Table Of ab%rowtype;
  l_pc Ty_Handoff_Pc;
  l_ab sam;
begin
  select distinct cust_no bulk collect into l_pc from aa;
  for j in 1 .. 10 loop 
    l_ab(j) := l_pc(j).cust_no;
    insert into ab values l_ab(j);
  end loop;
end;

THANKS IN ADVANCE

jonearles, is right, the best way is to use regular SQL - insert + select.

If you have some business logic that requires row-by-row processing in PL/SQL, however, you can use a slightly different variant of what you had:

declare
  Type Ty_Handoff_Pc Is Table Of aa%Rowtype Index By Binary_Integer;
  Type sam Is Table Of ab%rowtype;
  l_pc Ty_Handoff_Pc;
  l_ab sam;
begin

  select distinct cust_no bulk collect into l_pc from aa;

  for j in 1 .. l_pc.count loop 
    l_ab(j).cuno := l_pc(j).cust_no;
    -- perhaps some other processing here...
  end loop;

  FORALL i in 1..l_ab.count
    insert into ab values l_ab(i);

end;

The advantage of the FORALL at the end is that the insert is done using a bulk in-bind array, so only one call to the SQL engine instead of one for each record.

You cannot bulk collect into an associative array. You should use a nested table defined based on the column type instead of the table rowtype.

declare
    type Ty_Handoff_Pc is table of aa.cust_no%type;
    Type sam Is Table Of ab%rowtype;
    l_pc Ty_Handoff_Pc;
begin
    select distinct cust_no bulk collect into l_pc from aa;

    for j in 1 .. l_pc.count loop
        insert into ab values(l_pc(j));
    end loop;
end;
/

Edit: As Jeffrey Kemp pointed out, you can bulk collect into an associative array.

If this is real code, and not just for learning, you should definitely do it in regular SQL. insert into ab(custno) select distinct cust_no from aa will be much faster than using PL/SQL.

declare 
type taba is recor(cust_no aa.cust_no%type);
type tabb is table of taba;
custno_t tabb;
begin 
select cust_no bulk collect into custno_t from aa;
forall i in custno_t.first..custno_t.last
insert into bb values custno_t(i);
commit;
end;