Data Manipulation Language (INSERT, UPDATE, DELETE)

Data Manipulation Language (DML) statements are the backbone of managing information within your Oracle database. In PL/SQL, you can directly embed INSERT, UPDATE, and DELETE statements to add new records, modify existing ones, or remove data. This direct integration is essential for building applications that interact dynamically with your database.

Note: When performing DML operations in PL/SQL, remember that these changes are not permanent until a COMMIT statement is issued. This provides crucial transaction control, allowing you to group operations and roll them back if an error occurs. These operations are fundamental to managing data lifecycle in Oracle databases.

Example 1: INSERT Statement

DECLARE
    v_new_employee_id NUMBER := 207; -- Assuming 207 is a new, unused employee ID
    v_first_name      VARCHAR2(50) := 'John';
    v_last_name       VARCHAR2(50) := 'Doe';
    v_email           VARCHAR2(100) := 'JDOE@example.com';
    v_phone_number    VARCHAR2(20) := '515.123.4567';
    v_hire_date       DATE := SYSDATE;
    v_job_id          VARCHAR2(10) := 'IT_PROG';
    v_salary          NUMBER := 7000;
    v_commission_pct  NUMBER := NULL;
    v_manager_id      NUMBER := 103;
    v_department_id   NUMBER := 60;
BEGIN
    -- Inserts a new row into the employees table.
    INSERT INTO employees (employee_id, first_name, last_name, email, phone_number,
                           hire_date, job_id, salary, commission_pct, manager_id, department_id)
    VALUES (v_new_employee_id, v_first_name, v_last_name, v_email, v_phone_number,
            v_hire_date, v_job_id, v_salary, v_commission_pct, v_manager_id, v_department_id);

    DBMS_OUTPUT.PUT_LINE('Employee ' || v_first_name || ' ' || v_last_name || ' inserted successfully.');

    COMMIT; -- Make the changes permanent
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee ID ' || v_new_employee_id || ' already exists.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during INSERT: ' || SQLERRM);
        ROLLBACK; -- Rollback changes if an error occurs
END;
/

Explanation This PL/SQL block demonstrates an INSERT statement. We declare several variables to hold the values for the new employee record. The INSERT INTO statement then uses these variables to populate the corresponding columns in the employees table. The COMMIT statement is crucial here to make the insertion permanent in the Oracle database. Exception handling is included to catch common errors like DUP_VAL_ON_INDEX if the employee ID already exists, and a general OTHERS handler to roll back the transaction on any other error, ensuring data integrity in Oracle database transactions.

Example 2: UPDATE Statement

DECLARE
    v_employee_id NUMBER := 207;
    v_new_salary  NUMBER := 8000;
BEGIN
    -- Updates the salary of the employee with employee_id 207.
    UPDATE employees
    SET salary = v_new_salary
    WHERE employee_id = v_employee_id;

    IF SQL%ROWCOUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' salary updated to ' || v_new_salary || '.');
        COMMIT; -- Make the changes permanent
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found, no update performed.');
        ROLLBACK; -- No update, so no need to commit, but good practice to rollback if part of a larger transaction
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during UPDATE: ' || SQLERRM);
        ROLLBACK; -- Rollback changes if an error occurs
END;
/

Explanation This example showcases an UPDATE statement. We declare variables for the employee_id to be updated and the v_new_salary. The UPDATE statement modifies the salary column for the specified employee. We use SQL%ROWCOUNT (which we'll discuss in detail later) to check if any rows were affected by the update. If SQL%ROWCOUNT is greater than 0, it means an employee was found and updated, and we COMMIT the change. Otherwise, we inform the user that no update occurred. This is a common pattern for modifying existing data in Oracle PL/SQL, ensuring data consistency.

Example 3: DELETE Statement

DECLARE
    v_employee_id NUMBER := 207;
BEGIN
    -- Deletes the employee with employee_id 207.
    DELETE FROM employees
    WHERE employee_id = v_employee_id;

    IF SQL%ROWCOUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' deleted successfully.');
        COMMIT; -- Make the changes permanent
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found, no deletion performed.');
        ROLLBACK; -- No deletion, so no commit, but rollback good practice
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during DELETE: ' || SQLERRM);
        ROLLBACK; -- Rollback changes if an error occurs
END;
/

Explanation This block demonstrates a DELETE statement. We specify the employee_id to be deleted. The DELETE FROM statement removes the row corresponding to that ID from the employees table. Similar to UPDATE, we use SQL%ROWCOUNT to verify if a row was actually deleted before issuing a COMMIT. This ensures that your Oracle PL/SQL code handles scenarios where the target row might not exist, crucial for maintaining data integrity in Oracle database operations.

Example 4: INSERT ... SELECT Statement

DECLARE
    v_department_id NUMBER := 90; -- Assuming this department exists
    v_job_id        VARCHAR2(10) := 'AD_VP';
BEGIN
    -- Inserts new employees into a new table (e.g., archived_employees)
    -- by selecting existing employees from the employees table.
    INSERT INTO archived_employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id)
    SELECT employee_id, first_name, last_name, email, hire_date, job_id, department_id
    FROM employees
    WHERE department_id = v_department_id
    AND job_id = v_job_id;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees archived from department ' || v_department_id || '.');

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during INSERT ... SELECT: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation The INSERT ... SELECT statement is incredibly useful for populating a table with data retrieved from another table (or the same table). In this example, we're assuming an archived_employees table exists. We select employees from a specific department and job role from the employees table and insert them directly into archived_employees. This is an efficient way to transfer or duplicate data in Oracle SQL and PL/SQL, often used for data warehousing, backup, or batch processing.

Example 5: MERGE Statement (for UPSERT)

-- Create a dummy table for demonstration of MERGE
-- DROP TABLE product_inventory;
-- CREATE TABLE product_inventory (
--     product_id   NUMBER PRIMARY KEY,
--     product_name VARCHAR2(100),
--     quantity     NUMBER
-- );
-- INSERT INTO product_inventory VALUES (1, 'Laptop', 10);
-- INSERT INTO product_inventory VALUES (2, 'Mouse', 50);
-- COMMIT;

DECLARE
    v_product_id   NUMBER := 1;
    v_product_name VARCHAR2(100) := 'Laptop Pro';
    v_quantity     NUMBER := 15;
BEGIN
    -- MERGE statement to update a row if it exists, otherwise insert it.
    MERGE INTO product_inventory target
    USING (SELECT v_product_id AS product_id,
                  v_product_name AS product_name,
                  v_quantity AS quantity
           FROM DUAL) source
    ON (target.product_id = source.product_id)
    WHEN MATCHED THEN
        UPDATE SET target.product_name = source.product_name,
                   target.quantity = source.quantity
    WHEN NOT MATCHED THEN
        INSERT (product_id, product_name, quantity)
        VALUES (source.product_id, source.product_name, source.quantity);

    DBMS_OUTPUT.PUT_LINE('Product ID ' || v_product_id || ' merged successfully.');
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during MERGE: ' || SQLERRM);
        ROLLBACK;
END;
/

-- Example with a new product
DECLARE
    v_product_id   NUMBER := 3;
    v_product_name VARCHAR2(100) := 'Keyboard';
    v_quantity     NUMBER := 25;
BEGIN
    MERGE INTO product_inventory target
    USING (SELECT v_product_id AS product_id,
                  v_product_name AS product_name,
                  v_quantity AS quantity
           FROM DUAL) source
    ON (target.product_id = source.product_id)
    WHEN MATCHED THEN
        UPDATE SET target.product_name = source.product_name,
                   target.quantity = source.quantity
    WHEN NOT MATCHED THEN
        INSERT (product_id, product_name, quantity)
        VALUES (source.product_id, source.product_name, source.quantity);

    DBMS_OUTPUT.PUT_LINE('Product ID ' || v_product_id || ' merged successfully.');
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred during MERGE: ' || SQLERRM);
        ROLLBACK;
END;
/

Explanation The MERGE statement (often called an "upsert" operation) is a powerful DML command that allows you to conditionally INSERT or UPDATE rows in a target table based on whether matching rows exist in a source. In this example, we're updating a product's quantity and name if the product_id exists in product_inventory; otherwise, a new product is inserted. This significantly simplifies logic that would otherwise require separate SELECT, UPDATE, and INSERT statements, making it highly efficient for batch processing and data synchronization in Oracle database solutions.