Insert Statement in PL/SQL

Introduction to the Insert Statement in PL/SQL

The Insert Statement in PL/SQL is a fundamental operation in Oracle databases. It allows you to add new records into tables using PL/SQL blocks, procedures, or functions. Understanding how to use the PL/SQL INSERT statement is essential for building applications, automating data loads, or managing transactional systems.

This guide explains the INSERT statement in PL/SQL for beginners and intermediate learners, covering syntax, examples, best practices, and common mistakes.

What Is the Insert Statement in PL/SQL?

The INSERT statement in PL/SQL is used to insert one or more rows into an Oracle table. Unlike a standard SQL INSERT, PL/SQL allows the use of procedural logic, variables, and exception handling.

• Adding new records to tables
• Inserting dynamic values using variables
• Bulk data insertion using loops
• Inserting data from other tables

Basic Syntax of Insert Statement in PL/SQL

Standard INSERT INTO Syntax

📄

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Simple Insert Statement in PL/SQL Example

Example: Insert a Single Row

📄

BEGIN
    INSERT INTO employees (emp_id, emp_name, emp_salary)
    VALUES (101, 'John Smith', 50000);
    COMMIT;
END;

Explanation:

• The INSERT statement adds a new employee record.
• COMMIT saves the changes permanently.
• Using a PL/SQL block ensures controlled execution.

Insert Statement in PL/SQL Using Variables

Example: Insert Using PL/SQL Variables

📄

DECLARE
    v_emp_id     NUMBER := 102;
    v_emp_name   VARCHAR2(50) := 'Alice Brown';
    v_salary     NUMBER := 60000;
BEGIN
    INSERT INTO employees (emp_id, emp_name, emp_salary)
    VALUES (v_emp_id, v_emp_name, v_salary);
    COMMIT;
END;

This is useful when values are dynamic and not hard-coded.

INSERT INTO SELECT Example

📄

BEGIN
    INSERT INTO employee_backup (emp_id, emp_name, emp_salary)
    SELECT emp_id, emp_name, emp_salary
    FROM employees
    WHERE emp_salary > 50000;
    COMMIT;
END;

Inserting Data from Other Tables in PL/SQL

In PL/SQL, you can insert data from one table into another using the INSERT INTO ... SELECT statement. This method is useful for:

• Copying data from a source table to a target table.
• Backing up or archiving records.
• Filtering and transforming data before insertion.

Basic Syntax

📄

INSERT INTO target_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table
WHERE condition;

Example: Copy Employees with High Salary

Suppose you want to copy all employees earning more than 50,000 from the employees table to employee_backup table:

📄

BEGIN
    INSERT INTO employee_backup (emp_id, emp_name, emp_salary)
    SELECT emp_id, emp_name, emp_salary
    FROM employees
    WHERE emp_salary > 50000;
    
    COMMIT;
END;

Explanation:

• The SELECT query retrieves all employees with salary greater than 50,000.
• The INSERT INTO statement inserts those records into the employee_backup table.
• COMMIT ensures that the inserted data is saved permanently.

Using PL/SQL Variables with INSERT INTO SELECT

You can also combine variables with INSERT INTO SELECT for dynamic conditions:

📄

DECLARE
    v_min_salary NUMBER := 60000;
BEGIN
    INSERT INTO employee_backup (emp_id, emp_name, emp_salary)
    SELECT emp_id, emp_name, emp_salary
    FROM employees
    WHERE emp_salary > v_min_salary;
    
    COMMIT;
END;

Key Points:

• Ensure the column data types match between source and target tables.
• You can include WHERE clauses to filter specific rows.
• INSERT INTO SELECT is efficient for bulk data movement.

 Use Cases of Insert Statement in PL/SQL

• Registering new users in applications.
• Logging transactions in financial systems.
• Storing audit records for compliance.
• Automating data imports from external sources.

 Insert Statement in PL/SQL

• Always specify column names explicitly.
• Use COMMIT only when necessary.
• Handle exceptions for failed inserts.
• Avoid hardcoding values when possible.

Example with Exception Handling

📄

BEGIN
    INSERT INTO employees (emp_id, emp_name, emp_salary)
    VALUES (103, 'David Lee', 70000);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;

When Using Insert Statement in PL/SQL

Difference Between SQL Insert and PL/SQL Insert

The Insert Statement in PL/SQL is essential for managing and adding data in Oracle databases. By using variables, subqueries, loops, and exception handling, developers can perform robust and efficient insert operations. Mastery of the INSERT statement improves application reliability and scalability.

Frequently Asked Questions (FAQs)

1. What is the use of INSERT statement in PL/SQL?

It is used to add new records into tables while supporting variables, procedural logic, and exception handling.

2. Can we use INSERT without COMMIT in PL/SQL?

Yes, but changes will not be permanent until a COMMIT is executed. Without it, the insert can be rolled back.

3. How do I insert multiple rows in PL/SQL?

You can use loops or an INSERT INTO SELECT statement to insert multiple rows efficiently.

4. What is the difference between INSERT and INSERT INTO SELECT in PL/SQL?

INSERT adds explicit values, while INSERT INTO SELECT copies data from another table based on conditions.

5. Is INSERT statement in PL/SQL case-sensitive?

SQL keywords are not case-sensitive, but string values inserted may be case-sensitive depending on database settings.