Get query result into cursor inside procedure
I am porting many procedures from MySQL to oracle and I found some problems. This is what I have so far:
CREATE OR REPLACE PROCEDURE PROCEDURE1(PROCEDURE_PARAM IN NUMBER)
IS
CURSOR C1 (CURSOR_PARAMETER IN NUMBER)
IS
SELECT COL1, COL2, MY_FUNCTION1(P1), SUM(COL3), MY_FUNCTION2(P1,P2)
COL4, MY_FUNCTION(P1,P2,P3,P4)
FROM TABLE_1
WHERE COL1 = CURSOR_PARAMETER -- How to reference it to 'Procedure_param' ?
GROUP BY COL1, COL2, COL4
ORDER BY COL2;
BEGIN
-- HOW TO FETCH RESULTS INTO CURSOR ??
END PROCEDURE1;
/
This is an example of columns and name functions (user-created functions). My problems are how to tell the cursor that the parameter is the CURSOR_PARAMETER
same as the parameter PROCEDURE_PARAM
, and most importantly, how to get the results into the cursor? I am stuck with this issue. Any help would be greatly appreciated. For this example, I could start converting all the procedures I have.
Sincerely.
source to share
you would select from the cursor like:
BEGIN
for r_row in C1(PROCEDURE_PARAM)
loop
dbms_output.put_line(r_row.col1);--or whatever you want to do here.
end loop;
end;
although I usually prefer to write them inline as
begin
for r_row in (SELECT COL1, COL2, MY_FUNCTION1(P1),
SUM(COL3), MY_FUNCTION2(P1,P2)
COL4, MY_FUNCTION(P1,P2,P3,P4)
FROM TABLE_1
WHERE COL1 = PROCEDURE_PARAM
GROUP BY COL1, COL2, COL4
ORDER BY COL2)
loop
...
end loop;
so I don't have to scroll to the cursor pointer to see what it actually does.
source to share