Home » SQL & PL/SQL » SQL & PL/SQL » Can we have 2 insert statements for 1 for loop (Oracle 10g Forms, WinXP)
| Can we have 2 insert statements for 1 for loop [message #599929] |
Wed, 30 October 2013 05:00  |
 |
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
[EDIT 1: Topics from SQL and Forms forums merged by LF]
[EDIT 2: Topics split again by LF; this one remained in SQL forum, another one moved back to Forms forum]
Can we have 2 insert statements for 1 for loop?
how can we have 2 insert statements into 1 for loop!
FORALL j IN stu.FIRST .. stu.LAST
insert into CHASSISM_test
(make, stu, invoiceno, gross_tot, discount)
values
(make(j), stu(j), INVOICEno(j), GrossTot(j), discount(j));
commit;
insert into chassisd_test
(chassisno, wbase, baseprice, lspl_amount, ex_duty, sale_tax, net_paid, pdi)
values
(chassisn(j),
wbas(j),
basicprice(j),
lessamt(j),
exduty(j),
saletx(j),
netpaid(j),
pd(j));
commit;[Updated on: Wed, 30 October 2013 08:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Can we have 2 insert statements for 1 for loop? [message #599933 is a reply to message #599932] |
Wed, 30 October 2013 05:15  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Use two insert selects, or a for loop, or some other approach.
You can't do it with FORALL, because as Michel says it's not actually a loop.
Other than that we have no idea how you're trying to use this snippet of code so can't really make detailed suggestions.
|
|
|
|
|
|
| Re: Can we have 2 insert statements for 1 for loop? [message #599943 is a reply to message #599940] |
Wed, 30 October 2013 06:07 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Try:
FORALL j IN stu.FIRST .. stu.LAST
insert all
into CHASSISM_test (make, stu, invoiceno, gross_tot, discount)
values (make(j), stu(j), INVOICEno(j), GrossTot(j), discount(j));
into chassisd_test (chassisno, wbase, baseprice, lspl_amount, ex_duty, sale_tax, net_paid, pdi)
values (chassisn(j), wbas(j), basicprice(j), lessamt(j), exduty(j), saletx(j), netpaid(j), pd(j))
;
|
|
|
|
|
|
|
|
| Re: Can we have 2 insert statements for 1 for loop? [message #599966 is a reply to message #599950] |
Wed, 30 October 2013 09:05 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Sorry I missed the SELECT part:
SQL> create table t1 (col1 number);
Table created.
SQL> create table t2 (col2 int, col3 varchar2(10));
Table created.
SQL> declare
2 type t is record (col1 number, col2 int, col3 varchar2(10));
3 type tt is table of t index by binary_integer;
4 v tt;
5 begin
6 v(1).col1 := 1; v(1).col2 := 1; v(1).col3 := 'A';
7 v(2).col1 := 2; v(2).col2 := 2; v(2).col3 := 'B';
8 v(3).col1 := 3; v(3).col2 := 3; v(3).col3 := 'C';
9 forall i in v.first..v.last
10 insert all
11 into t1 (col1) values (c1)
12 into t2 (col2, col3) values (c2,c3)
13 select v(i).col1 c1, v(i).col2 c2, v(i).col3 c3 from dual;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> select * from t1;
COL1
----------
1
2
3
3 rows selected.
SQL> select * from t2;
COL2 COL3
---------- ----------
1 A
2 B
3 C
3 rows selected.
|
|
|
|
| Re: Can we have 2 insert statements for 1 for loop? [message #599967 is a reply to message #599966] |
Wed, 30 October 2013 09:38 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Your suggestion also works:
SQL> rollback;
Rollback complete.
SQL> declare
2 type t is record (col1 number, col2 int, col3 varchar2(10));
3 type tt is table of t index by binary_integer;
4 v tt;
5 begin
6 v(1).col1 := 1; v(1).col2 := 1; v(1).col3 := 'A';
7 v(2).col1 := 2; v(2).col2 := 2; v(2).col3 := 'B';
8 v(3).col1 := 3; v(3).col2 := 3; v(3).col3 := 'C';
9 forall i in v.first..v.last
10 insert all
11 into t1 (col1) values (v(i).col1)
12 into t2 (col2, col3) values (v(i).col2, v(i).col3)
13 select dummy from dual;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> select * from t1;
COL1
----------
1
2
3
3 rows selected.
SQL> select * from t2;
COL2 COL3
---------- ----------
1 A
2 B
3 C
3 rows selected.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 02:54:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
