Solved

PL/SQL For Loop Condition for different SQL statement

Posted on 2008-10-23
6
1,239 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
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Industry Leaders: 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

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 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