how to use select into to insert many records

i have a stored procedure that will copy data from table A to table B and return id that is generated from table B to update table a record . this is sample data table A is :

id----Name---refId---value----KPI 
 1    ahmed    550    50      30
 2    ali             30 
 3    soo      561    40      30

Table B is :

Id ---- Name ---- Value
550     ahmed       50
561     soo         30

i’ve tried the following i’ve defined a rec type

VAR_RECORD_A mySchema.A%rowtype;
refffId Number ;
BEGIN
select 
* 
into
VAR_RECORD_A 
from mySchema.A
where (value passed to function)=  var_kpi_element_req.BPM_KPI_ID ;
end ;
begin
insert into mySchema.B
(name , value)
Values 
(VAR_RECORD_A.name , VAR_RECORD_A.value)
RETURNING Id into refffId ;
end;

begin 
update A 
set refId = refffId 
where kpi =(value passed to function)
end;

my problem is the select into will return many records , i believe my code will work if only return 1 record

Answer

The simplest solution is to use a Cursor loop:

    -- don't declare VAR_RECORD_A as rowtype ... 
    refffId Number ;
BEGIN
    for VAR_RECORD_A in ( -- instead make it a cursor ref 
        select * 
        from mySchema.A
        where (value passed to function)=  var_kpi_element_req.BPM_KPI_ID    
  ) 
  loop

    insert into mySchema.B
    (name , value)
    Values 
    (VAR_RECORD_A.name , VAR_RECORD_A.value)
    RETURNING Id into refffId ;

    update A 
    set refId = refffId 
    where kpi =(value passed to function)
    and a.PK = VAR_RECORD_A.pk -- identify current row of A for update

  end loop;

---

Note: I have removed the unnecessary BEGIN … END notations. Only declare nested blocks when there’s real value in doing so.

In real life I would want to make this bulk operation more efficient by using collections and forall statements for a set-based approach.

Also I would consider whether I needed to lock the selected rows from A FOR UPDATE OF KPI, in case my whole transaction failed due to another session having a lock on one row. Doing this would allow you to use the WHERE CURRENT OF syntax when updating A.