Блог

Главная - Вопросы по программированию - Курсор, как в параметре — рефакторинг процедуры

Курсор, как в параметре — рефакторинг процедуры

#oracle #plsql #cursor

Вопрос:

У меня есть много функций, похожих на эту:

 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE REP_HELPER1 (myIdx IN BINARY_INTEGER, from_d IN DATE, rep_table IN OUT rep_table_T) IS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



CURSOR myCUR1 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 myCUR1;
    FETCH myCUR1 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 myCUR1;
END  REP_HELPER1;

Я хочу выполнить часть от open myCUR; до close myCUR; в отдельной процедуре univesral. Так как у меня много функций, подобных приведенным выше, и курсор всегда другой. Поэтому я хотел бы иметь одну процедуру,которая выполняла бы часть открытия, извлечения и закрытия :

 PROCEDURE PB_HELPER_READ_INTO_DAYS(nIndex IN BINARY_INTEGER, myCUR by reference, rep_table IN OUT rep_table_T)

Можно ли это сделать в plsql?

РЕДАКТИРОВАТЬ: Основываясь на ваших подсказках, я написал это так:

 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE REP_HELPER1 (myIdx IN BINARY_INTEGER, from_d IN DATE, rep_table IN OUT rep_table_T) IS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
myCUR1 SYS_REFCURSOR; 
        
BEGIN 
            
         OPEN myCUR1 FOR 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;
                 MY_READ(myIdx , myCUR1, rep_table) 
    END  REP_HELPER1; 
    
PROCEDURE MY_READ(myIdx IN BINARY_INTEGER, cur IN SYS_REFCURSOR, rep_table IN OUT rep_table_T) IS
        
    BEGIN
     
            FETCH cur 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 cur;
 END MY_READ;

Комментарии:

1. Используются ли все функции rep_table и есть ли у всех курсоров столбцы , которые точно соответствуют им?

2. Все курсоры имеют столбцы типа varchar, и все курсоры имеют также 31 столбец.

3. Хорошо, похоже, что у вас может быть функция или процедура, которая приняла курсор ссылки и заполнила a rep_table .

Ответ №1:

Вариант, который будет работать, — это создать пакет, объявить курсор глобально и использовать его в любой процедуре, которую вы хотите. Например:

 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>

Это не сработает: объявление курсора в одной процедуре и работа с ним в другой:

 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>

Кроме того, вы не можете ссылаться на него, используя префикс процедуры «владелец» :

 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>

Ответ №2:

Вы можете определить курсор в спецификации пакета вне любой процедуры или функции. Затем используйте этот курсор практически в любом месте, где курсор допустим (кроме как в качестве опорного курсора). Включает в себя любую процедуру/функцию в пакете или любую автономную процедуру/функцию и даже анонимный блок. Просто не забудьте указать ссылку в package_name.cursor_name любом месте за пределами упаковки. Смотрите демонстрацию)

 create or replace package pkg_test is
  cursor c_dept is select * from dept;
  
  procedure p1;
  procedure p2;  
end pkg_test;
/

Это делает обслуживание курсора довольно простым, так как существует только одно определение, а значит, только одно место обслуживания.

Комментарии:

1. У меня много разных курсоров. То же самое относится и к шаблону (одинаковые типы файлов и одинаковое количество полей).

Ответ №3:

Вы можете поставить только FETCH и CLOSE в другой процедуре. Было бы так (когда у вас есть только один параметр OUT, тогда я предпочитаю 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;

И используйте его вот так:

 DECLARE
    cur SYS_REFCURSOR;
BEGIN 
    
    cur := REP_HELPER(...);    
    FETCH cur INTO ...

    CLOSE cur;
END;

Более продвинутым решением был бы динамический SQL с пакетом DBMS_SQL:

 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;

или

 CREATE OR REPLACE PROCEDURE REP_HELPER(curid IN OUT NUMBER, myIdx IN BINARY_INTEGER, from_d IN DATE) IS

  sql_stmt VARCHAR2(32000);
BEGIN

  sql_stmt := 'SELECT myField1, ... FROM myTable';
  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

END REP_HELPER;


DECLARE
    cur SYS_REFCURSOR;
    curid NUMBER;
    ret INTEGER;
BEGIN 
    
    curid NUMBER := DBMS_SQL.OPEN_CURSOR;
    REP_HELPER(curid, ...);    
    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;

Но я думаю, что это было бы излишеством.

Обновить:

Вы также можете создавать строку SQL динамически, например:

 sql_stmt := 'SELECT ';
FOR i IN 1..31 LOOP
   sql_stmt := sql_stmt  || 'myField'||i||',';
END LOOP;
sql_stmt := REGEXP_REPLACE(sql_stmt, ',

Комментарии:

1. У меня много разных курсоров. Что такое же, так это шаблон (одинаковые типы файлов и одинаковое количество полей во всех курсорах). Мне не нужен один глобальный статический курсор.

2. Видите ли вы какой-либо глобальный статический курсор в моем коде?

3. У меня много REP_HELPER разных функций. REP_HELPER1 ,.... REP_HELPER55 . Все REP_HELPER функции имеют разные курсоры внутри. Вы вызываете curid := REP_HELPER(...); , а это не то, что я хочу, так как я могу определить курсор только один раз...

4. Тогда я бы предложил создать только одну функцию/процедуру с еще одним параметром. В противном случае вам понадобится динамический SQL: EXECUTE IMMEDIATE 'REP_HELPER' || 5 ||'(...)' INTO cur ;

5. Я не понимаю, что вы имеете в виду, говоря об одной функции с еще одним параметром...

);
sql_stmt := sql_stmt || ' FROM '||table_name;
sql_stmt := sql_stmt || ' WHERE the_date = :d';
OPEN cur FOR sql_stmt USING from_d;

Комментарии:

1. У меня много разных курсоров. Что такое же, так это шаблон (одинаковые типы файлов и одинаковое количество полей во всех курсорах). Мне не нужен один глобальный статический курсор.

2. Видите ли вы какой-либо глобальный статический курсор в моем коде?

3. У меня много REP_HELPER разных функций. REP_HELPER1 ,…. REP_HELPER55 . Все REP_HELPER функции имеют разные курсоры внутри. Вы вызываете curid := REP_HELPER(...); , а это не то, что я хочу, так как я могу определить курсор только один раз…

4. Тогда я бы предложил создать только одну функцию/процедуру с еще одним параметром. В противном случае вам понадобится динамический SQL: EXECUTE IMMEDIATE 'REP_HELPER' || 5 ||'(...)' INTO cur ;

5. Я не понимаю, что вы имеете в виду, говоря об одной функции с еще одним параметром…

Метки: ,