Add records in collection [message #617976] |
Sun, 06 July 2014 02:16 |
|
shumail
Messages: 149 Registered: September 2012 Location: Canada
|
Senior Member |
|
|
Hi all
Appreciate if someone help me out. I tried to cover my scenario with Scott schema.
I have the following table structure.
drop table test21;
CREATE TABLE test21
AS SELECT * FROM emp
where 1<>2;
Sample Code
---------
Seq creation
CREATE SEQUENCE test21_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
DECLARE
CURSOR c1 IS SELECT * FROM emp;
TYPE t_emp IS TABLE OF c1%rowtype;
v_emp t_emp:=t_emp();
g_limit number:=1000;
BEGIN
OPEN c1;
loop fetch c1 BULK COLLECT INTO v_emp LIMIT g_limit;
exit WHEN v_emp.count=0;
forall i IN v_emp.FIRST..v_emp.LAST
INSERT INTO test21
(empno,
JOB,
ename,
hiredate,
deptno
)
VALUES
(
test21_seq.nextval,
v_emp(i).JOB,
v_emp(i).ename,
v_emp(i).hiredate,
v_emp(i).deptno
);
END loop;
COMMIT;
close c1;
end;
--------
Rule
Now what I want is that if there is a deptno=20 then I need to create 2 records against deptno 20, something like below:
1st record
JOB='ITCLERK'
ename='ab'
hiredate='17-DEC-80'
deptno=20
2nd record
JOB='xxx'
ename='cd'
hiredate='17-NOV-80'
deptno=20
-------
Please let me know how come I do this?. I think I need to extend my collection before insert but not sure. Thanks in advance
[Updated on: Sun, 06 July 2014 02:18] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: Add records in collection [message #618095 is a reply to message #618090] |
Mon, 07 July 2014 12:23 |
|
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
It works for me:
SQL> DECLARE
2 CURSOR c1 IS SELECT * FROM emp;
3 TYPE t_emp IS TABLE OF c1%rowtype;
4 v_emp t_emp;
5 g_limit number:=1000;
6 BEGIN
7 OPEN c1;
8 loop fetch c1 BULK COLLECT INTO v_emp LIMIT g_limit;
9 exit WHEN v_emp.count=0;
10 FOR i IN 1..v_emp.count
11 loop
12 IF v_emp(i).deptno=20 THEN
13 --Create 1st record
14 v_emp.EXTEND;
15 v_emp(v_emp.LAST).deptno :=v_emp(i).deptno;
16 v_emp(v_emp.LAST).empno :=v_emp(i).empno;
17 v_emp(v_emp.LAST).ename :='ab';
18 v_emp(v_emp.LAST).JOB :='ITCLERK';
19 v_emp(v_emp.LAST).hiredate:='17-DEC-80';
20 --Create 2nd record
21 v_emp.EXTEND;
22 v_emp(v_emp.LAST).deptno :=v_emp(i).deptno;
23 v_emp(v_emp.LAST).empno :=v_emp(i).empno;
24 v_emp(v_emp.LAST).ename :='cd';
25 v_emp(v_emp.LAST).JOB :='xxx';
26 v_emp(v_emp.LAST).hiredate:='17-NOV-80';
27 END IF;
28 end loop;
29 forall i IN 1..v_emp.count
30 INSERT INTO test21
31 (empno,
32 JOB,
33 ename,
34 hiredate,
35 deptno
36 )
37 VALUES
38 (
39 v_emp(i).empno,
40 v_emp(i).JOB,
41 v_emp(i).ename,
42 v_emp(i).hiredate,
43 v_emp(i).deptno
44 );
45 END loop;
46 close c1;
47 end;
48 /
PL/SQL procedure successfully completed.
|
|
|
|