Ефективне вибір та оновлення даних в Oracle: уникнення помилок з обмеженням кількості рядків

5 Березня 2024 в 21:48 28

Під час роботи з базою даних Oracle, часто виникає потреба вибирати дані з таблиці та одночасно оновлювати вибрані записи. Це може бути корисно, наприклад, при масовому оновленні статусів завдань, перевірці та обробці великих обсягів даних тощо. Проте, у деяких випадках виникають складнощі при спробі обмежити кількість вибраних рядків. У цій статті ми розглянемо різні підходи до вибору та оновлення даних в Oracle з обмеженням кількості рядків, а також запобіжні заходи для уникнення зазначених помилок.

Одним з поширених випадків, коли необхідно вибрати обмежену кількість рядків з таблиці та оновити їх, є ситуація, коли ми маємо велику кількість записів, але хочемо обробити лише перші, наприклад, 1000. У цьому випадку зазвичай виникають проблеми при спробі використати кліматичний операцію FETCH FIRST або ROWNUM.

Наприклад, розглянемо наступний SQL-запит:


UPDATE (
    SELECT
        someColumns
    FROM
        table
    WHERE
        Status = 'TODO'
    AND ROWNUM &lt;= 1000
) 
SET 
    status = 'Done', 
    Date = SYSTIMESTAMP;

UPDATE (

SELECT

someColumns

FROM

table

WHERE

Status = 'TODO'

AND ROWNUM <= 1000

)

SET

status = 'Done',

Date = SYSTIMESTAMP;

У цьому запиті ми спробуємо оновити статус та дату для перших 1000 записів з таблиці, де статус є “TODO”. Проте, при виконанні запиту може з’явитися помилка “ORA-01733: віртуальний стовпець не допускається тут”, що вказує на проблеми з використанням кліматичного оператора ROWNUM у підзапиті.

Також, спроба використати оператор FETCH FIRST може призвести до аналогічної помилки. Ці помилки виникають через обмеження, які Oracle накладає на можливість оновлення рядків, обрані у підзапиті.

Другим способом спроби обмежити кількість вибраних рядків є використання функції ROWNUM безпосередньо в головному запиті. Наприклад:


UPDATE table
SET status = 'Done', 
    Date = SYSTIMESTAMP
WHERE Status = 'TODO' AND ROWNUM &lt;= 1000;

UPDATE table

SET status = 'Done',

Date = SYSTIMESTAMP

WHERE Status = 'TODO' AND ROWNUM <= 1000;

Проте, і в цьому випадку може виникнути помилка “ORA-01732: операція маніпулювання даними не дозволена для цього виду”, що вказує на обмеження використання оператора ROWNUM при виконанні операцій оновлення.

Для уникнення цих помилок і досягнення мети – вибору та оновлення обмеженої кількості рядків – ми можемо скористатися альтернативним підходом. Одним із способів реалізації є використання підзапиту для вибору необхідних рядків, а потім використання функції FOR UPDATE, щоб блокувати ці записи для оновлення іншими користувачами.

Ось приклад такого запиту:


UPDATE table t
SET t.status = 'Done', 
    t.Date = SYSTIMESTAMP
WHERE t.primary_key IN (
    SELECT primary_key
    FROM (
        SELECT primary_key
        FROM table
        WHERE Status = 'TODO'
        AND ROWNUM &lt;= 1000
    )
    FOR UPDATE
);

UPDATE table t

SET t.status = 'Done',

t.Date = SYSTIMESTAMP

WHERE t.primary_key IN (

SELECT primary_key

FROM (

SELECT primary_key

FROM table

WHERE Status = 'TODO'

AND ROWNUM <= 1000

)

FOR UPDATE

);

У цьому запиті ми використовуємо підзапит для вибору рядків, які ми хочемо оновити, а потім ми використовуємо функцію FOR UPDATE для блокування цих записів для оновлення іншими користувачами. Це дозволяє нам вибрати та оновити обмежену кількість рядків, уникнувши зазначених помилок.

Таким чином, у цій статті ми розглянули різні підходи до вибору та оновлення даних в Oracle з обмеженням кількості рядків, а також способи уникнення помилок, що можуть виникнути при використанні кліматичних операторів FETCH FIRST або ROWNUM. Ці знання допоможуть вам ефективно працювати з базою даних Oracle та виконувати завдання з вибором та оновленням даних.