Возврат обновленных строк из сохраненной функции

Я пытаюсь выбрать некоторые строки из таблицы в ORACLE и в то же время обновить состояние выбранных строк. Я нашел способ сделать это с помощью хранимой функции и курсоров, но мне не удается вернуть строки после использования курсора для обновления. Это мой код:

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;

    CURSOR c_operations IS
        SELECT * FROM TABLE1
        WHERE STATUS != 'OK'
        FOR UPDATE OF TABLE1.STATUS;

BEGIN

    FOR r_operation IN c_operations
    LOOP

        UPDATE
            TABLE1
        SET
            TABLE1.STATUS = 'OK'
        WHERE
            TABLE1.ID_TABLE1 = r_operation.ID_TABLE1;

    END LOOP;

    COMMIT;      

    -- Missing conversion from cursor to sys_refcursor

    RETURN l_return;

END;

Обновление работает, но я все еще не знаю, как вернуть обновленные строки, которые находятся в курсоре (c_operations).

Спасибо.


oracle stored-procedures plsql stored-functions
person Oleh Hrinchenko    schedule 26.11.2019    source источник
comment
@APC Нет, мне нужен список всех обновленных строк.   -  person Oleh Hrinchenko    schedule 26.11.2019
comment
@APC Это упрощение моей реальной функции: я хочу обновить строки с определенным статусом и заблокировать их, потому что у меня большой объем данных, и все должно происходить в одном и том же соединении с базой данных. Вот почему мне нужна сохраненная функция с выбором * для обновления, а затем обновить выбранные значения. Затем верните все выбранные значения в мою программу для их обработки. Это немного сложно...   -  person Oleh Hrinchenko    schedule 26.11.2019

Ответы (3)


arrow_upward
2
arrow_downward

Я собираюсь сделать некоторые предположения:

  1. id_table1 — это первичный ключ таблицы, поэтому ваше обновление RBAR (*) влияет только на одну строку.
  2. id_table1 является числовым

Если эти предположения неверны, вам нужно будет настроить следующий код.

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
   l_return   SYS_REFCURSOR;

   l_id       table1.id_table1%type;
   l_upd_ids  sys.odcinumberlist := new sys.odcinumberlist();

   CURSOR c_operations IS
        SELECT * FROM TABLE1
        WHERE STATUS != 'OK'
        FOR UPDATE OF TABLE1.STATUS;

BEGIN

    FOR r_operation IN c_operations   LOOP

        UPDATE TABLE1
        SET    TABLE1.STATUS = 'OK'
        WHERE  TABLE1.ID_TABLE1 = r_operation.ID_TABLE1
        returning TABLE1.ID_TABLE1 into l_id;

        l_upd_ids.extend();
        l_upd_ids(l_upd_ids.count()) := l_id;

    END LOOP;

    COMMIT;      

    open l_return for
        select * from table(l_upd_ids);

    RETURN l_return;

END;

Ключевые моменты решения.

  • использует поддерживаемую Oracle коллекцию (числа) sys.odcinumberlist для хранения обновленных идентификаторов;
  • использует предложение RETURNING для захвата значения id_table1 для обновленной строки;
  • сохраняет возвращенный ключ в коллекцию;
  • использует функцию table() для переноса коллекции в таблицу, которую можно запросить в курсоре ref.

Именно по этому последнему пункту я решил использовать sys.odcinumberlist, а не определять коллекцию в процедуре. Это тип SQL, поэтому мы можем использовать его в операторах SELECT.

(*) Строка за строкой. Обновление отдельных записей в цикле PL/SQL является самым медленным способом выполнения массовых обновлений и обычно представляет собой антишаблон. Достаточно простого UPDATE на основе набора. Тем не менее, вы знаете свою собственную ситуацию, поэтому я собираюсь оставить все как есть.

person APC    schedule 26.11.2019
comment
Спасибо за совет! Сейчас это работает. Я знаю, что это не лучшая практика в общих случаях, однако у нас здесь сложное приложение, где нам нужно обрабатывать множество конкретных деталей, и это не обычный случай. Вы все равно спасли день здесь :) - person Oleh Hrinchenko; 26.11.2019

arrow_upward
1
arrow_downward

Мне кажется, что вам не нужен начальный курсор, поскольку вы меняете СТАТУС каждой строки, которая не является «ОК», на «ОК», поэтому вы можете сделать это с помощью простого оператора UPDATE. Затем используйте оператор OPEN...FOR, чтобы вернуть курсор всех строк, в которых STATUS не равен 'OK', что не должно ничего возвращать, поскольку вы уже изменили все значения состояния на 'OK'. Я предлагаю вам переписать вашу процедуру следующим образом:

CREATE OR REPLACE FUNCTION FUNCTION_NAME
   RETURN SYS_REFCURSOR
IS
  l_return   SYS_REFCURSOR;
BEGIN
  UPDATE TABLE1
    SET STATUS = 'OK'
    WHERE STATUS != 'OK';

  COMMIT;      

  OPEN l_return FOR SELECT *
                      FROM TABLE1
                      WHERE STATUS != 'OK'
                      FOR UPDATE OF TABLE1.STATUS;

  RETURN l_return;
END;
person Bob Jarvis - Reinstate Monica    schedule 26.11.2019

arrow_upward
0
arrow_downward

Вместо цикла обновления, как насчет массового обновления, собирающего обновленные идентификаторы. Затем табличная функция из этих возвращенных идентификаторов.

create type t_table1_id is
   table of integer;

create or replace function set_table1_status_ok
   return sys_refcursor
is 
    l_results_cursor sys_refcursor;
    l_updated_ids    t_table1_id;
begin
    update table1
       set status  = 'Ok'
     where status != 'Ok'
    returning table1.id 
       bulk collect
       into l_updated_ids;

    open l_results_cursor for
         select * 
           from table1 
          where id in (select * from table(l_updated_ids));
    return l_results_cursor;
end set_table1_status_ok;

-- test 
declare 
   updated_ids sys_refcursor;
   l_this_rec  table1%rowtype; 
begin
    updated_ids := set_table1_status_ok();

    loop 
        fetch updated_ids into l_this_rec;
        exit when updated_ids%notfound;
        dbms_output.put_line ( l_this_rec.id || ' updated.');
    end loop;
    close updated_ids;
end ;
person Belayer    schedule 26.11.2019