Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1259
  • Last Modified:

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.

0
strav
Asked:
strav
1 Solution
 
sujith80Commented:
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
 
GGuzdziolCommented:

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
 
awking00Commented:
See attached.
comments.txt
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stravAuthor Commented:
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
 
yuchingCommented:
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
 
gajmpCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now