Solved

PL/SQL For Loop Condition for different SQL statement

Posted on 2008-10-23
6
1,243 Views
Last Modified: 2013-12-19
Hi,

I have a PL/SQL codes which will  get a number of records as parameter to a function.

My codes look like below

for k in (select * from emp where emp_no=t_emp_no ) loop
select get_data(k.emp_name,k.emp_add)
end loop;

But there is a requirement which I need to have an extra condition which needs to link to other table to get the accurate emp_no based on certain condition.

Eg.
If t_type ='CLASSIC' then
 sql1 :='select a.emp_no,a.emp_add from a.emp, b.salary where a.emp_no=b.emp_no and salary < 1500';
else
sql1:='select * from emp where emp_no=t_emp_no';
end if

However, when I pass this sql1 in to for loop like below
for k in (sql1) loop
end loop;

It has error. I would like to know how can I overcome this issue.

0
Comment
Question by:strav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 30 total points
ID: 22784610
You cannot use a curser for loop in this case. Use REF CURSOR.

Frame sql statement based on your condition.
Open REF CURSOR
loop
 select get_data(......);
end loop;
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 22784845

for k in (
select a.emp_no,a.emp_add 
  from a.emp
  where a.emp_no in (select b.emp_no from <emp_salary> b where b.salary < 1500)
    or t_type != 'CLASSIC'
) loop
  select get_data(k.emp_name,k.emp_add) 
end loop;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 22787022
See attached.
comments.txt
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:strav
ID: 22792589
Hi sujith80,

Can you please advise further on this?
 I tried the cond like below before.

If t_Type ='CLASSIC' then
 sql1:= select empno,emp_add from emp a,emp_salary b where a.empno=b.empno and b.salary < 1500;
else
 sql1:= select empno,emp_add from emp;
end if;

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  ref_empno emp.emp_no%type;
  ref_emp_add emp.emp_add%type;
  begin
  open c_emp for sql1;
  fetch c_emp into ref_empno,ref_emp_add;
  loop
.....
end loop;
close c_emp;
end;

I do not have compilation error. but when I execute this PL/SQL, it has error like it does not recognise the sql1.

0
 
LVL 11

Expert Comment

by:yuching
ID: 22793803
hi,

can do like this either way
--> Just add string '  and ' to indicate sql1 is a string/ nvarchar
If t_Type ='CLASSIC' then
 sql1:= 'select empno,emp_add from emp a,emp_salary b where a.empno=b.empno and b.salary < 1500';
else
 sql1:= 'select empno,emp_add from emp';
end if;
...
0
 
LVL 3

Expert Comment

by:gajmp
ID: 22823694
Try this one. If u want PL/SQL then convert into the PL/SQL object and the argument will be t_type and t_emp_no

Declare
t_type varchar2(10);
Type emp_name is table of emp.emp_name%type index by pls_integer;
Type emp_add is table of emp.emp_add%type index by pls_integer;
l_emp_name emp_name;
l_emp_add emp_add;
sql1 varchar2(100);
t_emp_no number;
Begin
    If t_type ='CLASSIC' then
     sql1 :='select a.emp_name,a.emp_add from a.emp, b.salary where a.emp_no=b.emp_no and salary < 1500';
    else
     sql1:='select emp_name, emp_add from emp where emp_no='||t_emp_no;
    end if;
    Execute Immediate sql1 bulk collect into l_emp_name, l_emp_add;
    For i in 1..l_emp_name.count
    Loop
        select get_data(l_emp_name(i),l_emp_add(i));
    End Loop;  
End;
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question