asked on

PL/SQL For Loop Condition for different SQL statement

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.

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GGuzdziol

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

awking00

See attached.
comments.txt

strav

ASKER

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.

yuching

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;
...

gajmp

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;