PL/SQL INSERT INTO

PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows us to write complex queries and scripts that include procedural logic, control structures, and error handling. The INSERT INTO statement in PL/SQL is essential for adding new rows of data to tables, making it a core element of data manipulation and database management. This article covers various ways to use the INSERT INTO statement in PL/SQL, complete with examples and explanations.

What is PL/SQL INSERT INTO?

The INSERT INTO statement is used to insert new rows of data into a table. It is an essential part of data manipulation in SQL and PL/SQL, allowing us to add of new records to a database.

Syntax for Specific Columns

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

Key Terms:

• table_name: The name of the table into which data is being inserted.
• column1, column2, column3, ...: The columns in the table that you want to insert data into.
• value1, value2, value3, ...: The values to be inserted into the respective columns.

Syntax for All Columns

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Key Terms:

• table_name: The name of the table into which data is being inserted.
• value1, value2, value3, ...: The values to be inserted into all columns of the table in the order they are defined.

Example of Using PL/SQL INSERT INTO

1. Creating and Populating the departments Table

This SQL command creates the departments table with two columns: department_id (the primary key) and department_name (for the department's name).

These SQL commands also insert two records into the departments table:

• The first record assigns department_id 1 to 'Human Resources'.
• The second record assigns department_id 2 to 'Finance'.

Query:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER
);

INSERT INTO departments (department_id, department_name) VALUES (1, 'Human Resources'); 
INSERT INTO departments (department_id, department_name) VALUES (2, 'Finance');

Output:

2. Creating and Populating the employees Table

This query creates the employees table with employee_id as the primary key, and includes columns for first_name, last_name, and department_id to link employees to their respective departments.

These queries insert two employee records into the employees table, linking John Doe to the Human Resources department and Jane Smith to the Finance department using their department_id.

Query:

CREATE TABLE departments ( 
  department_id NUMBER PRIMARY KEY,   
 department_name VARCHAR2(50)
);

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (101, 'John', 'Doe', 1);  
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (102, 'Jane', 'Smith', 2);

Output:

Example 1: Insert a Single Record

This PL/SQL block inserts a new employee named Alice Johnson with employee_id 103 into the employees table, assigning her to the department with department_id 1.

Syntax:

 INSERT INTO employees (employee_id, first_name, last_name, department_id)
    VALUES (103, 'Alice', 'Johnson', 1);

Output:

Explanation: The PL/SQL INSERT statement adds a new record for Alice Johnson to the employees table. With employee_id 103, her first name is 'Alice', last name is 'Johnson', and she is assigned to the department with department_id 1 (Human Resources).

The output will show Alice Johnson listed alongside existing employees, now part of the Human Resources department.

Example 2: Insert Multiple Rows

The SQL INSERT statement adds two new records to the employees table in a single command. Bob Brown is added with employee_id 104 and assigned to the Finance department while Carol Davis is added with employee_id 105 and linked to the Human Resources department. This operation efficiently updates the table with both new entries at once.

Syntax:

INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES 
    (104, 'Bob', 'Brown', 2),
    (105, 'Carol', 'Davis', 1);

Output:

Explanation: The SQL INSERT statement adds two new rows to the employees table in a single operation. Bob Brown is assigned employee_id 104 and linked to the Finance department (department_id 2), while Carol Davis, with employee_id 105, is assigned to the Human Resources department (department_id 1).

The output will show these two new employees listed in the employees table alongside existing records.

Example 3: Insert with Subquery

This PL/SQL block inserts a new employee named Eve White into the employees table, with department_id retrieved from the departments table where the department name is 'Finance'.

Syntax:

INSERT INTO employees (employee_id, first_name, last_name, department_id)
    SELECT 106, 'Eve', 'White', department_id
    FROM departments
    WHERE department_name = 'Finance';

Output:

Explanation: The PL/SQL INSERT statement adds a new record for Eve White into the employees table. The department_id for Eve is retrieved from the departments table where the department_name is 'Finance'.

The output shows Eve White added to the employees table with employee_id 106 and linked to the Finance department, with the corresponding department_id automatically inserted.

Example 4: Conditional Insert Based on Another Table

This PL/SQL block inserts new records into the employees table based on a condition that checks the departments table. Each INSERT INTO statement uses a SELECT statement to fetch the department_id from the departments table where the department_name matches the specified values.

Syntax:

INSERT INTO employees (employee_id, first_name, last_name, department_id)
    SELECT 109, 'Lisa', 'Parker', department_id
    FROM departments
    WHERE department_name = 'Human Resources';

    INSERT INTO employees (employee_id, first_name, last_name, department_id)
    SELECT 110, 'Mark', 'Taylor', department_id
    FROM departments
    WHERE department_name = 'Finance';

Output:

Explanation :

• Two new rows are added to the employees table, each with the department_id fetched from the departments table.
• The COMMIT statement ensures that all changes are saved.

Conclusion

The INSERT INTO statement in PL/SQL is a powerful tool for adding data to tables. By using PL/SQL blocks, we can manage transactions, insert multiple records, and leverage subqueries and sequences for more complex operations. These techniques are essential for effective data manipulation and management in Oracle databases.

prathamsahani0368

Follow

Improve

Article Tags :

• Databases
• PL/SQL