PL/SQL - conditional Cursor loop?

[norty303]

I'm fairly new to PL/SQL so please bear with my descriptions and terminology!

I have a procedure where I'm trying to process a choice of 2 cursor depending on an input parameter. So, I have 2 cursors, cA and cB (both of which output the same number of columns and types, cA returns a single row, cB is multiple rows).

So far I've been coming up with something like this, but I know it isn't right

procedure call_cursor(p_input in varchar2)

cursor cA is...

cursor cB is...

begin
if p_input = 'x' then
   for rRecord in cA loop
elsif p_input = 'y' then
   for rRecord in cB loop

....
....
....

end loop;
end if;

I'm trying to avoid duplication of code by having 2 separate procedures for each cursor, but it seems that this should be quite easy to achieve (and I'm sure it is!)

Thanks

 

[taupirho]

Please post your cursor selects, also , in the meantime, have a look at ref cursors


In order to understand recursion, you must first understand recursion.

 

[norty303]

I'm not sure why the cursor selects are relevant here. They both output 4 columns of data types varchar2, number, number, number.

I ensured that both outputs are identical so that I could pass them to a common record rowtype if required.

 

[taupirho]

Here is an example of using ref cursors, change to suit your own requirments

$ JAVA> type mypack.sql

create or replace package xxx as
type myrec is record (name varchar2(20));
type mycur is ref cursor return myrec;
procedure myproc (cur_param in out mycur, x in varchar2);
end xxx;

/
show error

create or replace package body xxx as

procedure myproc(cur_param IN OUT mycur, x in varchar2)
is
begin

if x = 'X'
then
open cur_param for
select ename from scott.emp;
else
open cur_param for
select job from scott.emp;
end if;



end myproc;

end xxx;

/
show error
exit
$ JAVA> sqlplus scott/tiger @mypack

SQL*Plus: Release 9.2.0.2.0 - Production on Wed Aug 13 11:36:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production


Package created.

No errors.

Package body created.

No errors.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production


$ JAVA> type mytest.sql
set serveroutput on
declare
testcur xxx.mycur;
testrec xxx.myrec;
begin
dbms_output.put_line('First bring out the Employee Names');
xxx.myproc(testcur,'X');
loop
fetch testcur into testrec;
exit when testcur%NOTFOUND;
dbms_output.put_line(testrec.name);
end loop;
close testcur;
dbms_output.put_line('Now bring out the Job Titles');
xxx.myproc(testcur,'Y');
loop
fetch testcur into testrec;
exit when testcur%NOTFOUND;
dbms_output.put_line(testrec.name);
end loop;
close testcur;
end;
/
exit

$ JAVA> sqlplus scott/tiger @mytest

SQL*Plus: Release 9.2.0.2.0 - Production on Wed Aug 13 11:38:30 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production

First bring out the Employee Names
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Now bring out the Job Titles
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

PL/SQL procedure successfully completed.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$ JAVA>




In order to understand recursion, you must first understand recursion.