Assigning one nested table to other in same position [message #683775] |
Sat, 20 February 2021 11:13 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Hi All,
I want to assign the values from one nested table to other in same position, pleas suggest how to do?
The actual requiremnt is as follows,
DECLARE
type rec
IS
record
(
empno NUMBER,
ename VARCHAR2(20),
JOB VARCHAR2(20));
type tab
IS
TABLE OF rec;
tab1 tab;
tab2 tab;
begin
--14 rows with empno
select empno,null,null bulk collect into tab2 from emp ;
--3 records with empno
select empno,ename,job bulk collect into tab1 from emp where job='MANAGER';
--need to assign same 3 ename records from tab1 to tab2 against same empno
--same need to perform 3 job records from tab1 to tab2 agains same empno for job CLERK
FOR i IN tab1.first..tab1.last
loop
tab2(tab1.empno).ename:=tab1(empno).ename;
end loop;
FOR i IN tab2.first..tab2.last
LOOP
dbms_output.put_line(tab2(i).ename||' '||tab2(i).empno);
END LOOP;
END;
Thank You
|
|
|
|
Re: Assigning one nested table to other in same position [message #683781 is a reply to message #683775] |
Sun, 21 February 2021 06:58 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ideally you should run single select:
select empno,
case job when 'MANAGER' then ename end ename,
case job when 'MANAGER' then job end job
bulk collect
into tab1
from emp;
Otherwise:
SET SERVEROUTPUT ON
DECLARE
type rec
IS
record
(
empno NUMBER,
ename VARCHAR2(20),
JOB VARCHAR2(20));
type tab
IS
TABLE OF rec;
tab1 tab;
tab2 tab;
type list_by_empno
is table of number
index by pls_integer;
v_list_by_empno list_by_empno;
begin
--14 rows with empno
select empno,null,null bulk collect into tab2 from emp ;
--3 records with empno
select empno,ename,job bulk collect into tab1 from emp where job='MANAGER';
--need to assign same 3 ename records from tab1 to tab2 against same empno
--same need to perform 3 job records from tab1 to tab2 agains same empno for job CLERK
FOR i IN tab2.first..tab2.last
LOOP
v_list_by_empno(tab2(i).empno) := i;
END LOOP;
FOR i IN tab1.first..tab1.last
loop
tab2(v_list_by_empno(tab2(i).empno)).ename:=tab1(i).ename;
tab2(v_list_by_empno(tab2(i).empno)).job:=tab1(i).job;
end loop;
FOR i IN tab2.first..tab2.last
LOOP
dbms_output.put_line(tab2(i).job||' '||tab2(i).ename||' '||tab2(i).empno);
END LOOP;
END;
/
MANAGER JONES 7369
MANAGER BLAKE 7499
MANAGER CLARK 7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|