How to Use PL/SQL Insert, Update, Delete And Select Statement?

PL/SQL is a powerful extension of SQL specifically designed for Oracle databases. It enables developers to create procedural logic and execute SQL commands within Oracle database environments. In this article, we will explore the usage of fundamental PL/SQL statements such as INSERT, UPDATE, DELETE, and SELECT with the help of various examples that are essential for manipulating data in Oracle databases.

Set Up the Environment

To understand How To Use PL/SQL Insert, Update, Delete, And Select Statement we need a table on which we will perform various operations and queries. Here we will consider a table called students as shown below:

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

1. Using INSERT Statement

INSERT statement in the PL/SQL is used to add the new records or rows of data into the table. It allows u to specify the table name, columns where the data will be inserted and the corresponding values to be inserted into the columns.

Syntax for INSERT Statement

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

Explanation:

• table_name is the name of the table, which we want to insert the data.
• (column1, column2, ....) are optional lists of the columns in table. If specified, you need to be provide the values for those columns in the same order.
• VALUES (value1, value2, ...) are values to be inserted into specified columns. These values must match the data types of the provided columns.

Examples on INSERT Statement

Example 1: Adding Data to the students Table

Let' insert some data into the students table.

INSERT INTO students (student_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

INSERT INTO students (student_id, first_name, last_name)
VALUES (2, 'Jane', 'Smith');

Output:

Explanation:

• Each INSERT statement inserts one row of the data into the students table.
• The syntax of the row inserted the same as before, specifying the columns to insert the data into and it provides the corresponding values into the table.

Example 2: Using the UPDATE Statement

INSERT INTO students (student_id, first_name, last_name)
VALUES (3, 'Michael', 'Johnson');

INSERT INTO students (student_id, first_name, last_name)
VALUES (4, 'Emily', 'Williams');

Output:

Explanation:

• Each INSERT statement inserts one row of the data into the students table.
• The syntax of the row inserted the same as before, specifying the columns to insert the data into and it provides the corresponding values into the table.

2. Using UPDATE Statement

UPDATE statement in the PL/SQL is used to the modify the existing records in table. It is allowed us to specify the table name, columns to updated new values for these columns and an optional conditional to the filter which rows are to be updated.

Syntax for UPDATE Statement

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Explanation:

• table_name is the name of the table, which we want to update.
• SET column1 = value1, column2 = value2, ... are specified the columns to the updated with their new values.
• WHERE condition is specify which rows are to be update. if we not use where condition all rows in the table will be update.

Examples on UPDATE Statement

Example 1: Let's update the last name of the students as Anderson whose student_id is 3.

UPDATE students
SET last_name = 'Anderson'
WHERE student_id = 3;

Output:

Explanation: Execute the above UPDATE statement in your SQL client for update the last_name of the student with the student_id 3 to Anderson.

Example 2: Let's update the first name of the students as Mitch whose student_id is 3.

UPDATE students
SET first_name = 'Mitch'
WHERE student_id = 3;

Output:

Explanation: Execute the above UPDATE statement in your SQL client for update the first_name of the student with the student_id 3 to Mitch.

3. Using DELETE Statement

DELETE statement in the PL/SQL is used to remove the one or more records from the table. It is allowed you to the specify the table name and optional condition to the filter which rows are to be deleted.

Syntax for DELETE Statement

DELETE FROM table_name
WHERE condition;

Explanation:

• table_name is the name of the table, which we want to delete the rows of the table.
• WHERE condition is specify which rows are to be delete. if we not use where condition all rows in the table will be delete.

Examples on DELETE Statement

Example 1: Let's delete the student from the table whose student_id is 4.

DELETE FROM students
WHERE student_id = 4;

Output:

Explanation:

• students is the name of the table, which we want to delete the rows of the table.
• WHERE student_id = 4 condition is specify the row is to be deleted.

Example 2: Let's delete the student from the table whose student_id is 2.

DELETE FROM students
WHERE student_id = 2;

Output:

Explanation:

• students is the name of the table, which we want to delete the rows of the table.
• WHERE student_id = 2 condition is specify the row is to be deleted.

4. Using SELECT Statement

SELECT statement in the PL/SQL is used to the retrieve the data from one or more tables. It is allowed you to the specify the columns you want to be retrieve, table from the which you want to be retrieve the data and optional condition to filter rows retrieved.

Syntax for SELECT Statement

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Explanation:

• column1, column2, ... are the columns you want to retrieve the data.
• table_name is the name of the table, which we want to retrieve the data of the table.
• WHERE condition is specify which rows are to be retrieve. if we not use where condition all rows in the table will be retrieve.

Example on SELECT statements

SELECT student_id, first_name
FROM students;

Explanation:

• SELECT student_id and first_name specified that the columns you want to be retrieve the data from the students table.
• FROM students specified that the name of the table from the which want to be retrieve the data.

Output:

Explanation: After executing the SELECT statement, the above output shows the student_id and first_name columns from the students table.

Conclusion

Overall, We have learned the essential concepts and syntax for the using PL/SQL INSERT, UPDATE, DELETE and SELECT statements in Oracle databases. By the understanding of the fundamentals of SQL operations, you can be effectively manipulate and query the data within the Oracle databases using the PL/SQL. Whether you are adding the new data, updating the existing records, deleting the unwanted entries or retrieving the information these statements provide the necessary tools for the managing the database content.