Object DML of MERGE INTO in PL/SQL

Question

Object DML of Update:

UPDATE DEPARTMENTS d
SET d=NEW OBJ_DEPARTMENT(1, 'Information Technology')
WHERE d.OBJECT_VALUE=(
    SELECT VALUE(d)
    FROM DEPARTMENTS d
    WHERE ROWNUM=1
);

Object DML of Insert:

INSERT INTO DEPARTMENTS d
VALUES(NEW OBJ_DEPARTMENT(1, 'Information Technology'));

Object DML of Delete:

DELETE FROM DEPARTMENTS d
WHERE d.OBJECT_VALUE=NEW OBJ_DEPARTMENT(1, 'Information Technology');

What would be the syntax of Object DML of MERGE INTO where the destination is an object table and the source is a nested table of objects?

CREATE OR REPLACE OBJECT TYPE OBJ_DEPARTMENT IS OBJECT(
    DEPARTMENT_ID INTEGER,
    DEPARTMENT VARCHAR2(2000)
);
/
CREATE TABLE DEPARTMENTS OF OBJ_DEPARTMENT(
    CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DEPARTMENT_ID)
);
/

Answer 1

Create a new collection of OBJ_DEPARTMENT, populate it in PL/SQL, and use the table operator to access that collection in a SQL statement.

This example shows the existing row, Information Technology, getting changed to Humand Resources, and the new row, Accounting, being inserted.

INSERT INTO DEPARTMENTS d
VALUES(NEW OBJ_DEPARTMENT(1, 'Information Technology'));


CREATE OR REPLACE TYPE OBJ_DEPARTMENT_TAB IS TABLE OF OBJ_DEPARTMENT;
/

declare
    obj_departments obj_department_tab := obj_department_tab(
        obj_department(1, 'Human Resources'),
        obj_department(2, 'Accounting')
    );
begin
    merge into departments old_departments
    using
    (
        select * from table(obj_departments)
    ) new_departments
    on (old_departments.department_id = new_departments.department_id)
    when matched then update
        set old_departments.department = new_departments.department
    when not matched then insert
        values(new_departments.department_id, new_departments.department);
end;
/

select * from departments;

DEPARTMENT_ID   DEPARTMENT
-------------   ----------
1               Human Resources
2               Accounting