better query other than multiple for loop's? [message #637918] |
Fri, 29 May 2015 01:55 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi all,
c1
col1
---
1
2
3
c2
col1
---
2
3
c3
col1
---
1
2
3
for c1 loop
for c2(c1.param)
end loop;
end loop;
for c1 loop
for c3(c1.param)
end loop;
end loop;
I need to check c1 data into the c2,c3 cursor's , so that's why i have writeen like above,can you please give me sameple on better on this?
Because, package taking too much time to run
Thank you
[Updated on: Fri, 29 May 2015 01:56] Report message to a moderator
|
|
|
|
|
Re: better query other than multiple for loop's? [message #637926 is a reply to message #637921] |
Fri, 29 May 2015 03:32 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Quote:Does it mean that you want to know which C1 values exist in C2 and C3? Try INTERSECT set operator.
Hi Littlefoot, i need to pass c1 data into the c2,c3 cursor , if i got millions of records into the c1 then how?
and to implement the code in the Begin & end block
and i need to display the out put at once not one by one like
empno ename
---- ------
1 a
deptno dname
------ ----
10 vv
empno ename
---- ------
2 b
expected as ,
empno ename
---- ------
1 a
2 b
deptno dname
------ ----
10 vv
please help
[Updated on: Fri, 29 May 2015 03:35] Report message to a moderator
|
|
|
|
Re: better query other than multiple for loop's? [message #637930 is a reply to message #637927] |
Fri, 29 May 2015 04:44 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi
create table dept_table(deptno number, Id number);
insert into dept_table values(20, 105);
insert into dept_table values(10, 21);
insert into dept_table values(20, 105);
insert into dept_table values(30, 48);
insert into dept_table values(30, 48);
insert into dept_table values(30, 48);
insert into dept_table values(10, 28);
insert into dept_table values(10, 25);
----------------------------------------------------------------------------
create table emp_table(deptno number, Id number);
insert into emp_table values(20, 105);
insert into emp_table values(20, 50);
----------------------------------------------------------------------------
create table section_table(deptno number, Id number);
insert into section_table values(10, 21);
insert into section_table values(10, 28);
insert into section_table values(10, 25);
insert into section_table values(30, 21);
insert into section_table values(30, 48);
insert into section_table values(30, 95);
|
|
|
|
Re: better query other than multiple for loop's? [message #637939 is a reply to message #637937] |
Fri, 29 May 2015 08:04 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Those intersects are completely pointless.
This:
select *
from emp_table
where deptno
in(
select deptno
from emp_table
intersect
select deptno
from dept_table
);
Is equivalent to this:
select *
from emp_table
where deptno
in(select deptno
from dept_table
);
|
|
|
|