This article is the fifth in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine.
The previous Beyond SQL 101 article, “Manipulate, Commit, and Take Back: How Change Occurs,” introduced you to data manipulation language (DML) and three of its statements: INSERT, UPDATE, and DELETE, as well as one data definition language (DDL) statement: TRUNCATE. You learned how an UPDATE statement handles NULL values differently from a SELECT statement. You also learned what a session is and that transaction control consists of making changes permanent with the COMMIT statement or undoing changes with the ROLLBACK statement. Finally, you discovered that a ROLLBACK statement has no effect on either a COMMIT statement or a TRUNCATE statement.
In this article, you’ll learn about one more DML statement: MERGE. MERGE is used to combine multiple DML commands such as INSERT, UPDATE, and DELETE into one statement. You’ll also learn about Oracle’s locking capabilities for handling DML statements issued in a database that’s accessed by multiple users concurrently. You’ll be introduced to the impact that referential integrity has on DML statements and how it’s designed to protect your data from results you don’t intend. You’ll discover how Oracle’s read consistency features help multiple users of an Oracle Database instance obtain and retain their own consistent view of data at all times. Last, you’ll get an introduction to Oracle data definition language (DDL), which you can use to create, alter, and drop schema objects.
To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (220.127.116.11.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.
Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)Creating a Single Action with MERGE
Suppose that your company requires annual updates of its employee data. You must modify the EMPLOYEE table data to assign certain employees to new managers, give certain employees salary raises, and assign certain employees to new departments. Other employees are part of an annual termination list, and you must remove them from the EMPLOYEE table. One way to accomplish this multichange action is through the MERGE command in Oracle Database.
Together, Listing 1 and Listing 2 illustrate use of the MERGE command to make an annual update to the EMPLOYEE table. In Listing 1, a copy of EMPLOYEE, named ANNUAL_REVIEW, is made. Then all the employee edits are made in the ANNUAL_REVIEW table. In Listing 2, the changes in ANNUAL_REVIEW are merged into the EMPLOYEE table. The MERGE operation compares the two tables’ rows by checking whether the EMPLOYEE_ID value in the EMPLOYEE table matches the EMPLOYEE_ID value in the ANNUAL_REVIEW table. When the EMPLOYEE_ID values match, the SALARY, MANAGER, and DEPARTMENT_ID values from the ANNUAL_REVIEW table are used to update the column values of the corresponding EMPLOYEE rows. When the EMPLOYEE_ID values don’t match, the extra employee rows from the ANNUAL_REVIEW table (the records for Mary Streicher and Ernest Muchmore) are inserted into the EMPLOYEE table.
Code Listing 1: Preparing data for a subsequent MERGE statement
-- Prework: Ready the data changes that will ultimately be merged with the EMPLOYEE table -- Create a copy of the EMPLOYEE table and preperform all expected annual edits upon this table SQL> create table annual_review as select * from employee; Table created. SQL> select * 2 from annual_review; EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID ——————————— ——————————— —————————— ————————— ——————— ———————— ————————————— 6574 Marcy Tamra 04-MAY-16 28 10 6575 Sasha Meyer 04-MAY-16 85000 1234 10 28 Emily Eckhardt 07-JUL-04 100000 10 37 Frances Newton 14-SEP-05 7500 1234 Donald Newton 24-SEP-06 80000 28 10 7895 Matthew Michaels 16-MAY-07 70000 28 10 6567 Roger Friedli 16-MAY-07 60000 28 10 6568 Betsy James 16-MAY-07 60000 28 10 6569 michael peterson 03-NOV-08 90000 20 6570 mark leblanc 06-MAR-09 65000 6569 20 6571 Thomas Jeffrey 27-FEB-10 300000 30 6572 Theresa Wong 27-FEB-10 70000 6571 30 6573 Lori Dovichi 07-JUL-11 28 10 13 rows selected. SQL> select * 2 from department; DEPARTMENT_ID NAME LOCATION ————————————— —————————— ————————————— 10 Accounting LOS ANGELES 20 Payroll NEW YORK 30 IT WASHINGTON DC 3 rows selected. -- Create a new Marketing department SQL> insert into department (department_id, name, location) 2 values (40, 'Marketing', 'CHICAGO'); 1 row created. SQL> select * 2 from department; DEPARTMENT_ID NAME LOCATION ————————————— —————————— ————————————— 10 Accounting LOS ANGELES 20 Payroll NEW YORK 30 IT WASHINGTON DC 40 Marketing CHICAGO 4 rows selected. -- Create a new manager for the Marketing department SQL> insert into annual_review (employee_id, first_name, last_name, hire_date, salary, manager, department_id) 2 values (6576, 'Mary', 'Streicher', to_date('16-MAY-2016', 'DD-MON-YYYY'), 200000, null, 40); 1 row created. -- Assign the employee Sasha Meyer to the new employee manager, Mary Streicher SQL> update annual_review 2 set manager = 6576, 3 department_id = 40 4 where employee_id = 6575; 1 row updated. -- Assign the employee Frances Newton to the Accounting department employee manager, Donald Newton SQL> update annual_review 2 set manager = 1234, 3 department_id = 10 4 where manager is null 5 and department_id is null; 1 row updated. -- Give 10% salary raises to all employees hired before June 1, 2006 SQL> update annual_review 2 set salary = salary + (salary * .1) 3 where hire_date < to_date('01-JUN-2006', 'DD-MON-YYYY'); 2 rows updated. -- To illustrate how MERGE works with DELETE, insert an employee that will meet the criteria for termination SQL> insert into annual_review (employee_id, first_name, last_name, hire_date, salary, manager, department_id) 2 values (6577, 'Ernest', 'Muchmore', to_date('01-OCT-2000', 'DD-MON-YYYY'), 1000000, null, 40) 3 ; 1 row created. SQL> commit; Commit complete. SQL> select * 2 from annual_review; EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID ——————————— —————————— —————————— ————————— ——————— ——————— ————————————— 6574 Marcy Tamra 04-MAY-16 28 10 6575 Sasha Meyer 04-MAY-16 85000 6576 40 28 Emily Eckhardt 07-JUL-04 110000 10 37 Frances Newton 14-SEP-05 82500 1234 10 1234 Donald Newton 24-SEP-06 80000 28 10 7895 Matthew Michaels 16-MAY-07 70000 28 10 6567 Roger Friedli 16-MAY-07 60000 28 10 6568 Betsy James 16-MAY-07 60000 28 10 6569 michael peterson 03-NOV-08 90000 20 6570 mark leblanc 06-MAR-09 65000 6569 20 6571 Thomas Jeffrey 27-FEB-10 300000 30 6572 Theresa Wong 27-FEB-10 70000 6571 30 6573 Lori Dovichi 07-JUL-11 28 10 6576 Mary Streicher 16-MAY-16 200000 40 6577 Ernest Muchmore 01-OCT-00 1000000 40 15 rows selected.
Code Listing 2: The MERGE statement used for multiple conditional and dependent DML commands combined into one DML statement
SQL> merge into employee e 2 using (select employee_id, first_name, last_name, hire_date, salary, manager, department_id 3 from annual_review) ar 4 on (e.employee_id = ar.employee_id) 5 when matched then 6 update set e.salary = ar.salary, 7 e.manager = ar.manager, 8 e.department_id = ar.department_id 9 when not matched then 10 insert (e.employee_id, e.first_name, e.last_name, e.hire_date, e.salary, e.manager, e.department_id) 11 values (ar.employee_id, ar.first_name, ar.last_name, ar.hire_date, ar.salary, ar.manager, ar.department_id); 15 rows merged. SQL> select * 2 from employee; EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID ——————————— —————————— —————————— ————————— ——————— ——————— ————————————— 6574 Marcy Tamra 04-MAY-16 28 10 6575 Sasha Meyer 04-MAY-16 85000 6576 40 28 Emily Eckhardt 07-JUL-04 110000 10 37 Frances Newton 14-SEP-05 82500 1234 10 1234 Donald Newton 24-SEP-06 80000 28 10 7895 Matthew Michaels 16-MAY-07 70000 28 10 6567 Roger Friedli 16-MAY-07 60000 28 10 6568 Betsy James 16-MAY-07 60000 28 10 6569 michael peterson 03-NOV-08 90000 20 6570 mark leblanc 06-MAR-09 65000 6569 20 6571 Thomas Jeffrey 27-FEB-10 300000 30 6572 Theresa Wong 27-FEB-10 70000 6571 30 6573 Lori Dovichi 07-JUL-11 28 10 6576 Mary Streicher 16-MAY-16 200000 40 6577 Ernest Muchmore 01-OCT-00 1000000 40 15 rows selected.
MERGE statements can also use a DELETE clause. This clause is an optional condition used with the WHEN MATCHED THEN UPDATE clause. The rows that satisfy both the DELETE and ON conditions are the only rows deleted. In Listing 3, the salary value for Ernest Muchmore is updated to 2000000 in the ANNUAL_REVIEW table. Then all employee records that match between the ANNUAL_REVIEW table and the EMPLOYEE table are merged, based on their EMPLOYEE_ID values. Because all EMPLOYEE_ID values match between the two tables, each salary value for every employee in the EMPLOYEE table is updated with the corresponding employee salary value from the ANNUAL_REVIEW table. Before the MERGE action is completed, the DELETE condition is applied and one row is found to satisfy both the ON and DELETE conditions. Because the record for Ernest Muchmore has a salary value greater than 1000000, it is deleted immediately after its salary value is updated to 2000000, per the UPDATE clause. Because no records that are unmatched between the two tables are found, the WHEN NOT MATCHED THEN INSERT clause is not applied.
Code Listing 3: The MERGE statement with a DELETE condition
SQL> update annual_review 2 set salary = 2000000 3 where employee_id = 6577; 1 row updated. SQL> merge into employee e 2 using (select employee_id, first_name, last_name, hire_date, salary, manager, department_id 3 from annual_review) ar 4 on (e.employee_id = ar.employee_id) 5 when matched then 6 update set e.salary = ar.salary 7 delete where salary > 1000000 8 when not matched then 9 insert (e.employee_id, e.first_name, e.last_name, e.hire_date, e.manager, e.department_id) 10 values (ar.employee_id, ar.first_name, ar.last_name, ar.hire_date, ar.manager, ar.department_id); 15 rows merged. SQL> commit; Commit complete. SQL> select * 2 from employee 3 where employee_id = 6577; No rows selected.
Use the MERGE statement only with small tables (those with no more than a few thousand records). It’s also a good idea to use MERGE only for seldom-executed actions (as in the annual update scenario) and only during nonpeak business processing hours. The reason for these precautions is that Oracle Database employs locking mechanisms to protect your edits while you perform them. When locks are in place, other users of your database might not be able to access data in the tables you’re updating. The larger the tables, the longer the merge will take and the longer the locks will be in place. The benefits of a multiuser database are undermined if access is restricted for extended periods, especially during regular business hours.Locked for Action
In a multiuser database, more than one user (or session) might want to manipulate the same piece of information. Oracle Database uses locking to ensure data consistency in such situations. When you issue DML statements such as INSERT, UPDATE, DELETE, or MERGE, the rows that satisfy the conditions for these statements are automatically locked while they are being modified. The lock prevents other sessions from modifying the records until your modifications are complete. Other sessions can only read the last-committed version of those records.
Any session attempting to modify the same row must wait for the current session’s changes to be either committed or rolled back. The commit or rollback releases the lock on the row and allows the waiting session to acquire a new lock on the row and begin modification. (Unlike the other DML statements, the SELECT statement does not lock rows, because other sessions are allowed to see committed data only.)
DML statements are not the only types of actions that acquire locks. DDL statements acquire locks on an entire table (instead of individual rows) when a DDL operation, such as ALTER TABLE (used to modify or add columns or add constraints, for example), is issued. In many cases, a DDL operation cannot take place on a table while DML locks on the table are being held, and vice versa. (This rule admits a few exceptions you’ll read about later in this series, when you learn how to create indexes.)Purging with Integrity
Oracle Database uses referential integrity to ensure that relationships between tables remain consistent. As you learned in your initial introduction to referential integrity in the SQL 101 article “Modeling and Accessing Relational Data,” the values of a foreign key correspond to values of a primary key. Unlike INSERT and UPDATE, a DELETE action can be prevented, depending on the action’s effect on dependent child records (that is, records associated via a foreign key constraint). The effect on child records is based on how the foreign key constraint is defined. The three ways you can specify how a foreign key constraint should behave with respect to DELETE actions are
If you try to delete a record that has child records, the default behavior in Oracle Database is to stop you from deleting the record, thereby preventing child records from being orphaned. This default behavior employs the RESTRICT foreign key constraint DELETE condition.
Consider the example in Listing 4. The first two DDL commands place a primary key constraint for department data on the DEPARTMENT table and a foreign key constraint on the table that references department data (in this case, the EMPLOYEE table). After executing the commands in Listing 4, you have a constrained relationship between the DEPARTMENT_ID value in the DEPARTMENT table and the DEPARTMENT_ID value in the EMPLOYEE table. Because you haven’t specified a certain DELETE condition on your foreign key constraint, the RESTRICT method is applied and you’re prevented from deleting any record in the DEPARTMENT table that has a child record in the EMPLOYEE table.
Code Listing 4: DELETEs and referential integrity
SQL> alter table department add constraint department_pk primary key (department_id); Table altered. SQL> alter table employee add constraint department_fk foreign key (department_id) references department (department_id); Table altered. SQL> delete from department 2 where name = 'Marketing'; delete from department * ERROR at line 1: ORA-02292: integrity constraint (SQL_201.DEPARTMENT_FK) violated - child record found
The error message in Listing 4 informs you that you’ve tried to violate the integrity between parent and child and you are not allowed to complete your DELETE action as such. In this case, you must either delete all associated child records first or change your foreign key constraint DELETE condition to one that is less restrictive. (Creating and altering primary and foreign key constraints will be covered more fully in a future article in this series.)Remaining Consistent
Oracle Database guarantees each executed SQL statement a read-consistent view of data. This means that when a statement begins processing, the values it obtains via a query are guaranteed not to change—even if another session changes the values in the meantime—until the session reissues its query. Recall that in Listing 6 of the previous Beyond SQL 101 article, “Manipulate, Commit, and Take Back: How Change Occurs,” you establish a second, separate connection to the database without exiting your first session. In your first session, you insert a new employee record without committing the record to the database. When you query the EMPLOYEE table for the record, you retrieve the newly inserted record.
However, your second session is unable to retrieve the same record. Only after you issue a COMMIT statement in your first session is your second session able to retrieve the new record. When a user changes data with a DML operation, Oracle Database has to keep track of the old values. Until the user commits the changes, all other users querying the data will see the old values. And if the user rolls back the changes, the old values must be available to return the data to its previous state.
Oracle stores the old values for changed, uncommitted data in undo segments. Sessions querying data that another user has changed but not yet committed must read the data from the undo segments to receive their read-consistent view of the data. When a session commits its changes, the undo segment is made available for other session DML changes and the committed changes are overwritten on the undo segment. If a session rolls back its changes, the old values from the undo segment are reapplied to the table data, effectively undoing the changes.Defining and Refining with DDL
You’re already somewhat familiar with DDL commands, because you’ve used them in your SQL_201 schema and object creation script to create the tables for the SQL_201 schema. The most basic type of object you create as an Oracle SQL developer is a table. Consider the example in Listing 5. The CREATE TABLE command, followed by the name of the table, is used to create tables. You can either list the columns and their associated datatypes explicitly (as shown in Listing 5) or create a table based on the definition of an existing table.
Code Listing 5: Creating a table with DDL
SQL> create table employee_example ( 2 employee_id number, 3 first_name varchar2(30), 4 last_name varchar2(30), 5 hire_date date, 6 salary number(9,2), 7 manager number, 8 department_id number); Table created.
When you define a table name and a column list explicitly, you must be aware of naming and length conventions and restrictions. No database object name, including table names, can exceed 30 characters (as of Oracle Database 12c Release 1). The name must begin with a letter, cannot include spaces or hyphens, and should accurately describe the data it contains. For example, the name T should never be used for a table that contains actual data for your business. Consistency is also important. All of your tables should have plural names (INVOICES and ORDERS, for example), or all should have singular names (INVOICE and ORDER, for example); never mix plural and singular table names in your database.
Column names must be unique within a table and also must not exceed 30 characters (as of Oracle Database 12c Release 1). Columns are explicitly defined by name, datatype, and length (where appropriate); column names, like table names, should also accurately describe the data values they represent. Note in Listing 5 that you use a comma to separate column definitions within a CREATE TABLE statement.Conclusion
This article has demonstrated how the MERGE statement can be used to combine multiple INSERT, UPDATE, and DELETE commands into one statement. You also learned when it’s appropriate to use the MERGE statement and when it isn’t. You discovered locking mechanisms in Oracle Database and learned how the database guarantees its users read consistency. You also discovered that Oracle Database enforces automatic referential integrity when it comes to deleting data constrained by a foreign key constraint. Last, you were introduced to data definition language and saw how to use it to create a table. In the next article in this series, you’ll learn more about DDL.
READ SQL 101, Parts 1–12.
LEARN more about relational database design and concepts.
DOWNLOAD the sample script for this article.
READ more Beyond SQL 101.
Photography by Ricardo Gomez, Unsplash