Oracle PL/SQL оператор MERGE — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать оператор MERGE в Oracle PL/SQL с синтаксисом и примерами.

Описание

Oracle PL/SQL оператор MERGE позволяет выбрать строки из одной таблицы для обновления или вставки в другую таблицу. Решение о том, обновлять или вставлять данные в целевую таблицу, основывается на условии в предложении ON. Оператор MERGE - это удобный способ совместить как минимум две операции. Это позволяет избежать использования нескольких DML операторов INSERT и UPDATE.
У вас должны быть объектные привилегии INSERT и UPDATE для целевой таблицы и привилегии SELECT для исходной таблицы.

Синтаксис

Синтаксис Oracle PL/SQL оператора MERGE:

MERGE [hint] INTO [schema .] table_A [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

Параметры или аргумент

schema

схема базы данных.

INTO

используйте оператор INTO, чтобы указать целевую таблицу, которую вы обновляете или вставляете данные.

table_A

целевая таблица.

t_alias

алиас (псевдоним).

USING { table | view | subquery }

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

ON ( condition )

указывает условие, при котором операция MERGE либо обновляет, либо вставляет данные. Для каждой строки table_A, для которой выполняется условие поиска, Oracle обновляет строку, используя соответствующие данные из исходной таблицы. Если условие не выполняется ни для одной строки, Oracle вставляет в table_A данные на основе соответствующей строки исходной таблицы.

WHEN MATCHED | NOT MATCHED

Используйте эти предложения, чтобы указать Oracle, как реагировать на результаты условия соединения в предложении ON. Вы можете указать эти два предложения в любом порядке.

merge_update_clause

определяет новые значения столбцов table_A. Oracle выполняет это обновление, если условие предложения ON истинно. Если предложение обновления выполняется, то активируются все триггеры обновления, определенные в table_A.
Пример merge_update_clause: UPDATE SET column = { expr | DEFAULT } [, column = { expr | DEFAULT }]

Ограничения на обновление View

Вы не можете указать DEFAULT при обновлении View.
Вы не можете обновить столбец, на который есть ссылка в условии ON.

merge_insert_clause

определяет значения для вставки в столбец table_A, если условие предложения ON ложно. Если предложение вставки выполняется, то активируются все триггеры вставки, определенные в table_A.
Пример merge_insert_clause: INSERT ( column [, column]... ) VALUES ( expr [, expr]... )

Ограничение на слияние с View

Вы не можете указать DEFAULT при обновлении View.

Примеры Oracle PL/SQL MERGE

В следующем примере создается таблица bonuses в схеме oe с полем bonus равным 100, по умолчанию.

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

1	CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

Затем вставляем в таблицу bonuses всех сотрудников, совершивших продажи (на основе столбца sales_rep_id таблицы oe.orders).

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id
      FROM employees e, orders o
     WHERE e.employee_id = o.sales_rep_id
     GROUP BY e.employee_id);
   
SELECT * FROM bonuses;   
--результат
EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

INSERT INTO bonuses(employee_id)

(SELECT e.employee_id

FROM employees e, orders o

WHERE e.employee_id = o.sales_rep_id

GROUP BY e.employee_id);

SELECT * FROM bonuses;

--результат

EMPLOYEE_ID BONUS

----------- ----------

153 100

154 100

155 100

156 100

158 100

159 100

160 100

161 100

163 100

Наконец, менеджер по персоналу решает, что все сотрудники должны получить премию. Тем, кто не совершил продажи, выплачивается бонус в размере 1% от зарплаты. Тем, кто уже совершил продажи, бонус увеличивается на 1% от зарплаты. Оператор MERGE реализует эти изменения за один шаг:

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id 
            FROM employees
           WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*0.1);

--результат
EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        156        200
        158        190
        159        180
        160        175
        161        170
        163        195
        157        950
        145       1400
        170        960
        179        620
        152        900
        169       1000

MERGE INTO bonuses D

USING (SELECT employee_id, salary, department_id

FROM employees

WHERE department_id = 80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary*0.1);

--результат

EMPLOYEE_ID BONUS

----------- ----------

153 180

154 175

155 170

156 200

158 190

159 180

160 175

161 170

163 195

157 950

145 1400

170 960

179 620

152 900

169 1000