I have many functions similiar to this one:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE REP_HELPER (myIdx IN BINARY_INTEGER, from_d IN DATE, rep_table IN OUT rep_table_T) IS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CURSOR myCUR IS SELECT myField1,
myField2,
myField3,
myField4,
myField5,
myField6,
myField7,
myField8,
myField9,
myField10,
myField11,
myField12,
myField13,
myField14,
myField15,
myField16,
myField17,
myField18,
myField19,
myField20,
myField21,
myField22,
myField23,
myField24,
myField25,
myField26,
myField27,
myField28,
myField29,
myField30,
myField31
FROM myTable;
BEGIN
-- I wish to move the part below to different procedure
OPEN myCUR;
FETCH myCUR INTO rep_table(myIdx).day1, rep_table(myIdx).day2, rep_table(myIdx).day3, rep_table(myIdx).day4, rep_table(myIdx).day5,
rep_table(myIdx).day6, rep_table(myIdx).day7, rep_table(myIdx).day8, rep_table(myIdx).day9, rep_table(myIdx).day10,
rep_table(myIdx).day11, rep_table(myIdx).day12, rep_table(myIdx).day13, rep_table(myIdx).day14, rep_table(myIdx).day15,
rep_table(myIdx).day16, rep_table(myIdx).day17, rep_table(myIdx).day18, rep_table(myIdx).day19, rep_table(myIdx).day20,
rep_table(myIdx).day21, rep_table(myIdx).day22, rep_table(myIdx).day23, rep_table(myIdx).day24, rep_table(myIdx).day25,
rep_table(myIdx).day26, rep_table(myIdx).day27, rep_table(myIdx).day28, rep_table(myIdx).day29, rep_table(myIdx).day30,
rep_table(myIdx).day31;
CLOSE myCUR;
END REP_HELPER;
I wish to do the part from open myCUR; to close myCUR; in a separate procedure. As I have many functions like the above but the cursor is always different. So I would like to have procedure which would do the open,fetch, close part :
PROCEDURE PB_HELPER_READ_INTO_DAYS(nIndex IN BINARY_INTEGER, myCUR by reference, rep_table IN OUT rep_table_T)
Is it possible to to it in plsql?
CodePudding user response：
Option which would work is to create a package, declare cursor globally and use it in any procedure you want. For example:
SQL> create or replace package pkg_test is
2 procedure p1;
3 end;
4 /
Package created.
SQL> create or replace package body pkg_test is
2 cursor c1 is select * from dept;
3 c1r c1%rowtype;
4
5
6 procedure p1 is
7 begin
8 open c1;
9 fetch c1 into c1r;
10 close c1;
11 end p1;
12 end;
13 /
Package body created.
SQL>
This won't work: declaring a cursor in one procedure and working with it in another:
SQL> create or replace package pkg_test is
2 procedure p1;
3 procedure p2;
4 end;
5 /
Package created.
SQL> create or replace package body pkg_test is
2 procedure p1 is
3 cursor c1 is select * from dept;
4 c1r c1%rowtype;
5 begin
6 null;
7 end p1;
8
9
10 procedure p2 is
11 begin
12 open c1;
13 fetch c1 into p1.c1r;
14 close c1;
15 end p2;
16 end;
17 /
Warning: Package Body created with compilation errors.
SQL> show err
Errors for PACKAGE BODY PKG_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5 PL/SQL: SQL Statement ignored
12/11 PLS-00201: identifier 'C1' must be declared
13/5 PL/SQL: SQL Statement ignored
13/11 PLS-00201: identifier 'C1' must be declared
14/5 PL/SQL: SQL Statement ignored
14/11 PLS-00201: identifier 'C1' must be declared
SQL>
Also, you can't reference it using the "owner" procedure's prefix:
SQL> create or replace package body pkg_test is
2 procedure p1 is
3 cursor c1 is select * from dept;
4 c1r c1%rowtype;
5 begin
6 null;
7 end p1;
8
9
10 procedure p2 is
11 begin
12 open p1.c1;
13 fetch p1.c1 into p1.c1r;
14 close p1.c1;
15 end p2;
16 end;
17 /
Warning: Package Body created with compilation errors.
SQL> show err
Errors for PACKAGE BODY PKG_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5 PL/SQL: SQL Statement ignored
12/14 PLS-00225: subprogram or cursor 'P1' reference is out of scope
13/5 PL/SQL: SQL Statement ignored
13/11 PLS-00225: subprogram or cursor 'P1' reference is out of scope
14/5 PL/SQL: SQL Statement ignored
14/11 PLS-00225: subprogram or cursor 'P1' reference is out of scope
SQL>
CodePudding user response：
You can put only FETCH and CLOSE in a different procedure. Would be this (when you have only one OUT parameter, then I prefer a FUNCTION):
CREATE OR REPLACE FUNCTION REP_HELPER (myIdx IN BINARY_INTEGER, from_d IN DATE) RETURN SYS_REFCURSOR IS
myCur SYS_REFCURSOR;
BEGIN
OPEN myCur FOR
SELECT myField1, ...
FROM myTable;
RETURN myCur;
END REP_HELPER;
And use it like this:
DECLARE
cur SYS_REFCURSOR;
BEGIN
cur := REP_HELPER(...);
FETCH cur INTO ...
CLOSE cur;
END;
A more advanced solution would be dynamic SQL with DBMS_SQL Package:
CREATE OR REPLACE FUNCTION REP_HELPER(myIdx IN BINARY_INTEGER, from_d IN DATE) RETURN NUMBER IS
curid NUMBER := DBMS_SQL.OPEN_CURSOR;
sql_stmt VARCHAR2(32000);
BEGIN
sql_stmt := 'SELECT myField1, ... FROM myTable';
DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
RETURN curid;
END REP_HELPER;
DECLARE
cur SYS_REFCURSOR;
curid NUMBER;
ret INTEGER;
BEGIN
curid := REP_HELPER(...);
ret := DBMS_SQL.EXECUTE(curid);
-- Switch from DBMS_SQL to native dynamic SQL
cur := DBMS_SQL.TO_REFCURSOR(curid);
FETCH cur INTO ...
CLOSE cur;
END;
But I think this would be an overkill.