Solved

PL/SQL For Loop Condition for different SQL statement

Posted on 2008-10-23
6
1,233 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 31

Expert Comment

by:awking00
ID: 22787022
See attached.
comments.txt
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now