?
Solved

PL/SQL For Loop Condition for different SQL statement

Posted on 2008-10-23
6
Medium Priority
?
1,248 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 90 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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