What is a DML in Oracle? & What are the two DML Statements?

DML in Oracle

Fundamental commands for accessing and modifying data within pre-existing database objects, like tables, are Data Manipulation Language (DML) statements. In contrast to Data Definition Language (DDL) statements, which implicitly commit transactions and define or remove database objects, DML statements give users more control over their modifications because they do not immediately commit the current transaction. The four main DML statements are MERGE, DELETE, UPDATE, and INSERT. With these instructions, you can enter, store, and modify data in the database. Program blocks can execute these DML statements thanks to Oracle’s PL/SQL, a procedural extension of SQL.

INSERT: Adding New Rows to a Table

One or more additional rows of data can be added to a table using the INSERT command. You usually get feedback like “1 row created” for single row insertions when it’s executed.

Usage and Syntax: As long as you provide a value for each column in the order specified in the table definition, you can enter values straight into every column of a table without mentioning the column names. As an alternative, you can place NULL values into unidentified columns or change the order of data fields by explicitly listing the columns you are adding into. When a value is attempted to be inserted that is greater than the width or magnitude of a column, errors such as “value too large for column” or “mismatched datatype” will occur.

Example: Let’s first create a table named EMPLOYEES to demonstrate:

CREATE TABLE EMPLOYEES (
    EMP_ID          NUMBER(6) PRIMARY KEY,
    EMP_NAME        VARCHAR2(50) NOT NULL,
    SALARY          NUMBER(10, 2),
    DEPARTMENT_ID   NUMBER(3)
);

Now, let’s insert values:

-- Inserting a single row, providing values for all columns
INSERT INTO EMPLOYEES VALUES (101, 'Alice Smith', 60000.00, 10);
-- Output: 1 row created.

-- Inserting a single row, specifying columns and inserting a NULL for SALARY
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, DEPARTMENT_ID) VALUES (102, 'Bob Johnson', 20);
-- Output: 1 row created.

-- Inserting a single row, specifying columns and all values
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY, DEPARTMENT_ID) VALUES (103, 'Charlie Brown', 75000.00, 10);
-- Output: 1 row created.

Output after inserts (using SELECT * FROM EMPLOYEES;):

Advanced Insertions: Subqueries can be used with the INSERT command to select data from one or more other tables and add many rows to a table. When performing multi-row INSERT operations, particularly when using a SELECT command, the APPEND hint can be used to tell Oracle to put new records at the end of the table’s physical storage space instead of trying to reuse existing blocks. This will increase performance. Note that a WHERE clause is never allowed in an INSERT query.

UPDATE: Modifying Existing Data in a Table

One or more columns in one or more existing rows inside a table can have their values changed using the UPDATE command.

Emphasis on WHERE clause importance: In a UPDATE statement, the WHERE clause is crucial. It details which rows will be impacted by the upgrade and under what circumstances. Every record in the table will be changed by the UPDATE statement if the WHERE clause is left out. Use of this could result in accidental data loss or corruption.

Example:

-- Update Alice Smith's salary
UPDATE EMPLOYEES SET SALARY = 65000.00 WHERE EMP_ID = 101;
-- Output: 1 row updated.

-- Update Bob Johnson's salary and assign him to department 10 (emphasising WHERE clause)
UPDATE EMPLOYEES SET SALARY = 58000.00, DEPARTMENT_ID = 10 WHERE EMP_ID = 102;
-- Output: 1 row updated.

-- INCORRECT USAGE: Without a WHERE clause, all salaries would be set to 70000.00
-- UPDATE EMPLOYEES SET SALARY = 70000.00;

Output after updates:

You can also set a column’s value to NULL using the UPDATE statement, for instance, UPDATE COMFORT SET Noon = NULL WHERE City = 'KEENE'.

DELETE: Remove Rows from a Table

To eliminate one or more rows of data from a table, use the DELETE command.

Emphasis on WHERE clause importance: In a DELETE statement, the WHERE clause is just as important as the UPDATE clause. It lays down the criteria that determine which rows need to be deleted. The table will be essentially empty if the WHERE clause is left out, as all of the rows will be removed. This emphasises how important it is to use DELETE with a WHERE clause, but with extreme caution.

Example:

-- Delete Bob Johnson's record
DELETE FROM EMPLOYEES WHERE EMP_ID = 102;
-- Output: 1 row deleted.

-- INCORRECT USAGE: Without a WHERE clause, all records would be deleted
-- DELETE FROM EMPLOYEES;

Output after delete:

Transaction Control: Because DML statements do not implicitly commit, you can use a ROLLBACK command to reverse any DELETE statements that were made incorrectly. The TRUNCATE command, a DDL declaration that cannot be undone, differs significantly from this.

Merge the UPDATE and INSERT processes

You can combine INSERT and UPDATE actions into a single statement by using the MERGE command, which has been available since Oracle9i. Its powers were expanded to allow more control over inserts, updates, and even conditional deletes in a single query in Oracle Database 10g. When you wish to update a row if it already exists and insert it if not, this command is very helpful for “upsert” procedures.

Syntax and Usage: The MERGE statement is used to evaluate a condition data, such as a table, view, or query. If the condition is met (WHEN MATCHED), it is executed; if not, it is executed as an insert (WHEN NOT MATCHED).

Example: Let’s add some data to another table EMPLOYEE_STAGING to simulate new data or updates.

CREATE TABLE EMPLOYEE_STAGING (
    EMP_ID          NUMBER(6) PRIMARY KEY,
    EMP_NAME        VARCHAR2(50) NOT NULL,
    SALARY          NUMBER(10, 2),
    DEPARTMENT_ID   NUMBER(3)
);

INSERT INTO EMPLOYEE_STAGING VALUES (101, 'Alice Smith', 70000.00, 10); -- Existing EMP_ID, new SALARY
INSERT INTO EMPLOYEE_STAGING VALUES (104, 'David Lee', 55000.00, 30);   -- New EMP_ID
INSERT INTO EMPLOYEE_STAGING VALUES (105, 'Eve Davis', 80000.00, 20);   -- New EMP_ID
-- Output: 3 rows created.
COMMIT;

Now, let’s MERGE data from EMPLOYEE_STAGING into EMPLOYEES:

MERGE INTO EMPLOYEES E
USING EMPLOYEE_STAGING S
ON (E.EMP_ID = S.EMP_ID)
WHEN MATCHED THEN
    UPDATE SET E.EMP_NAME = S.EMP_NAME, E.SALARY = S.SALARY, E.DEPARTMENT_ID = S.DEPARTMENT_ID
WHEN NOT MATCHED THEN
    INSERT (EMP_ID, EMP_NAME, SALARY, DEPARTMENT_ID)
    VALUES (S.EMP_ID, S.EMP_NAME, S.SALARY, S.DEPARTMENT_ID);
-- Output: 3 rows merged.

Output after merge:

Although it doesn’t specifically specify how many rows were added as opposed to updated, the MERGE command gives the number of rows processed from the table. By including extremely complicated business logic, this command significantly expands the capabilities of the ordinary INSERT and UPDATE commands. The accurate use of WHERE clauses in the USING clause of MERGE statements is essential to guarantee accuracy, as is the case with all update processes.