Loops and Cursors

[Irish01]

I have no idea where to begin! I'm using two implicit cursors and need to write a stored procedure that accepts a date string as an in parameter, and an integer as an out parameter. (Loop within a loop.) Does anybody know where I can look at examples of something like this??

 

[rcurva]

Here's a sample, I don't know if I get you exactly:

create or replace procedure sample (pdate IN varchar2, pnum OUT number)

vcol NUMBER;

CURSOR cur1 IS
SELECT *
FROM table1
WHERE col1 = pdate;

CURSOR cur2 IS
SELECT *
from TABLE2
WHERE col3 = vcol; -- parameterized cursor

BEGIN
FOR rec1 IN cur1 LOOP
vcol := rec1.col1 -- assign a value from the outer cursor

FOR rec2 IN cur2 LOOP

-- YOUR PROCESS HERE...

END LOOP;

END LOOP;

IF <your conditions> then
p_num := 0;
ELSE
p_num := 1;
END IF;

END;
/

 

[carp]

You might want to take a look at TimboA's thread entitled &quot;Merging data from two cursors&quot;. It's in this forum and dated 9/17.

 

[oliveng]

the syntax is
create or replace procedure sample (pdate IN varchar2, pnum OUT number) is

CURSOR cur1 IS
SELECT *
FROM table1
WHERE col1 = pdate;

CURSOR cur2(vcol NUMBER) IS
SELECT *
from TABLE2
WHERE col3 = vcol; -- parameterized cursor

BEGIN
FOR rec1 IN cur1 LOOP
-- vcol := rec1.col1 assign a value from the outer cursor

FOR rec2 IN cur2(rec1.col1) LOOP

-- YOUR PROCESS HERE...

END LOOP;

END LOOP;

IF <your conditions> then
p_num := 0;
ELSE
p_num := 1;
END IF;

END;
/
In this example, you can use function, the syntax is :
create or replace function simple (pdate IN varchar2) return number is
pnum number;

CURSOR cur1 IS
SELECT *
FROM table1
WHERE col1 = pdate;

CURSOR cur2(vcol NUMBER) IS
SELECT *
from TABLE2
WHERE col3 = vcol; -- parameterized cursor

BEGIN
FOR rec1 IN cur1 LOOP
-- vcol := rec1.col1 assign a value from the outer cursor

FOR rec2 IN cur2(rec1.col1) LOOP

-- YOUR PROCESS HERE...

END LOOP;

END LOOP;

IF <your conditions> then
p_num := 0;
ELSE
p_num := 1;
END IF;
return p_num;
END;
/