How To Use PL SQL Insert, Update, Delete And Select Statement

This tutorial explains how to use PL SQL INSERT, UPDATE, DELETE and SELECT commands with programming examples:

In this article, we will continue with PL/SQL series. In the PL/SQL DataTypes, Constants and Variable tutorial, we have learned about PL SQL data types, variables, constants & literals in detail with the help of programming examples. Here, we will discuss the PL SQL DML statements and how they work.

We shall explore the PL SQL INSERT, DELETE, UPDATE, and SELECT commands to manipulate data in PL/SQL tables.

Let’s start with the learnings!!

PL SQL DML Commands

Let’s start to understand INSERT, DELETE, UPDATE, and SELECT commands implementation on tables. Collectively these are called Data Manipulation Language (DML) statements.

PL SQL INSERT Command

INSERT INTO statement is used to insert data into a table. It is called a Data Manipulation Language. It uses the table name and column names and values as inputs and performs the insertion of values into the table.

Syntax:

INSERT INTO <<table name>> VALUES (<<val1>>, <<val2>>, …..);

The table name and values to be inserted are the mandatory part of the PL SQL INSERT statement. Optionally, we can also mention the column names where the values are to be inserted.

We have created the EMPLOYEE table with the help of the SQL statement given below:

CREATE TABLE EMPLOYEE (
   CODE INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   AGE INT NOT NULL,     
   PRIMARY KEY (CODE)
);

Code implementation with PL SQL INSERT:

BEGIN
INSERT INTO EMPLOYEE VALUES (1, 'FLEX', 34);
INSERT INTO EMPLOYEE VALUES (2, 'HARRY', 35);
END

The output of the above code should be:

SELECT * from EMPLOYEE;

INSERT INTO SELECT

INSERT INTO ALL COLUMNS

INSERT INTO SELECT is used to insert rows into a table by acting upon the SELECT statement result. It may be required to obtain a row from the table and insert into a different table.

Syntax:

INSERT INTO << target table name >> (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM << source table name >>
WHERE condition;

INSERT INTO SELECT command needs the source and target table’s data types to be the same. In case we require copying the entire rows of the source table to the target table, we can omit the WHERE condition.

We have created another table called MANAGER with the help of the SQL statement given below:

CREATE TABLE MANAGER (
   CODE INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   AGE INT NOT NULL,     
   PRIMARY KEY (CODE)
);

Let us again consider the EMPLOYEE table.

Code implementation with INSERT INTO SELECT.:

BEGIN
INSERT INTO MANAGER (CODE, NAME, AGE)
SELECT CODE, NAME, AGE       
FROM EMPLOYEE
WHERE CODE = 1;
END;

Output of the above code:

SELECT * from MANAGER;

INSERT PARTIAL DATA

We can also insert partial data into another table. Let us first create the MANAGERDETAIL table from the EMPLOYEE table with the below query.

CREATE TABLE MANAGERDETAIL AS SELECT * FROM EMPLOYEE WHERE 1 = 0;

Here, WHERE 1 = 0 refers to the first column of the EMPLOYEE table which is CODE.

Next, we will use the INSERT INTO SELECT statement to duplicate the data from the EMPLOYEE table to the MANAGERDETAIL table.

Code implementation with INSERT INTO SELECT;

BEGIN
 INSERT INTO MANAGERDETAIL
 SELECT CODE, NAME, AGE       
 FROM EMPLOYEE WHERE CODE! = 0
END;

Output of the above code:

SELECT * from MANAGERDETAIL;

In the above example, we have not mentioned the column names in the INSERT command, as the output of the SELECT command has the values which match with the MANAGERDETAIL table columns. Also, we have included a condition with the clause WHERE.

INSERT PARTIAL DATA AND LITERAL

We can insert partial data and literal value to a table.

Let us first create the MANAGERCONTACT table with the below query:

CREATE TABLE MANAGERCONTACT (
   CODE INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   AGE INT NOT NULL,     
   PRIMARY KEY (CODE)
);

Now, let us again take the help of the EMPLOYEE table and try to duplicate the data from the EMPLOYEE table to the MANAGERCONTACT table.

Code implementation with INSERT INTO:

BEGIN
 INSERT INTO MANAGERCONTACT (CODE, NAME, AGE)
 SELECT CODE, NAME, 0      
 FROM EMPLOYEE;
END;

The output of the above code:

SELECT * from MANAGERCONTACT;

In the above example, we have obtained the values from the EMPLOYEE table and also included 0 literal in the INSERT statement which modified the values of the AGE column to 0 in MANAGERCONTACT table.

INSERT ALL

An INSERT ALL statement is used to add more than one row into a single table or multiple tables. It is also called a multi-table command and is of two types conditional and unconditional.

#1) UNCONDITIONAL INSERT ALL

To insert more than one row into a table, an INSERT ALL statement is used.

Syntax:

INSERT ALL
    INTO << table name >> (c1, c2, c3) VALUES (v1, v2, v3)
    INTO << table name >> (c1, c2, c3) VALUES (v4, v5, v6)
    INTO << table name >> (c1, c2, c3) VALUES (v7, v8, v9)

Here, the c1, c2, and c3 are the column names, and v1, v2 and v3 are the values to be inserted. In case, we have to use literal value instead of subquery; we have to use the below query:

SELECT * FROM dual;

We have created the SOCCERPLAYERS table with the help of the SQL statement given below:

CREATE TABLE SOCCERPLAYERS (
   PLAYERID INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   COUNTRY VARCHAR (15),     
   PRIMARY KEY (PLAYERID)
);

Code implementation with INSERT ALL:

BEGIN
INSERT ALL 
    INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY)
    VALUES (4, 'DANIEL', 'USA') 
   INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY)
    VALUES (8, 'DANNY', 'ITALY')
      INTO SOCCERPLAYERS (PLAYERID, NAME, COUNTRY)
    VALUES (7, 'FRANK', 'FRANCE')    
SELECT 1 FROM dual;
END;

The output of the above code:

SELECT * from SOCCERPLAYERS;

In the above example, three rows have been inserted at once in a table with an INSERT ALL command.

To insert more than one row into multiple tables, an INSERT ALL statement is used.

Syntax:

INSERT ALL
    INTO << table_name1 >> (c1, c2, c3) VALUES (v1, v2, v3)
    INTO << table_name2 >> (c1, c2, c3) VALUES (v4, v5, v6)
    INTO << table_name3 >> (c1, c2, c3) VALUES (v7, v8, v9)
Subquery;

Here, the c1, c2, and c3 are the column names, and v1, v2 and v3 are the values to be inserted. The table_name1, table_name2 and table_name3 are the table names.

#2) CONDITIONAL INSERT ALL

An INSERT ALL statement can insert more than one row into tables depending on some conditions.

Syntax:

INSERT [ALL | FIRST]
    WHEN cond1 THEN
        INTO table_name1 << column names >> VALUES << list of values >>
    WHEN cond2 THEN 
        INTO table_name2 <<column names >> VALUES << list of values >>
    ELSE
        INTO table_name3 <<column names >> VALUES << list of values >>
Subquery;

The keyword ALL is used to specify that all the conditions under the WHERE clause shall be executed. If a condition is satisfied, the corresponding INTO clause would run.

If the keyword FIRST is used then for each individual row obtained from a subquery, the Oracle executes the criteria in the clause WHEN from top to bottom. Once a condition is satisfied, the corresponding INTO clause would run and jump to another WHEN clause for that row.

We have created the VEHICLE, CARS, SMALLCARS, and LARGECARS tables with the help of the SQL statements given below:

CREATE TABLE VEHICLE (
   CARID INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   COLOR VARCHAR (15),     
   PRIMARY KEY (CARID)
);

Insert the below values into the VEHICLE table:

INSERT INTO VEHICLE VALUES (1, 'AUDI', 'RED');
INSERT INTO VEHICLE VALUES (4, 'BMW', 'WHITE');
INSERT INTO VEHICLE VALUES (7, 'KIA', 'GREEN');

CREATE TABLE CARS (
   CARID INT NOT NULL,
   NAME VARCHAR (15) NOT NULL, 
   COLOR VARCHAR (15),     
   PRIMARY KEY (CARID)
);

CREATE TABLE SMALLCARS AS
SELECT *
FROM CARS;

CREATE TABLE LARGECARS AS
SELECT *
FROM CARS;

Code implementation with INSERT ALL.

          
BEGIN
INSERT ALL
   WHEN CARID = 1 THEN
      INTO CARS
   WHEN CARID = 4 THEN
      INTO SMALLCARS
   WHEN CARID = 7 THEN
      INTO LARGECARS      
     SELECT CARID, NAME, COLOR FROM VEHICLE;
  END;	

The output of the above code:

SELECT * from CARS;

SELECT * from SMALLCARS;

SELECT * from LARGECARS;

In the above example, one row has been inserted in each of the three tables based on the CARDID value.

There are certain restrictions with the INSERT ALL statement:

• It is used only for inserting values in tables. It does not work with views or materialized views.
• It is not capable of inserting values into remote tables.
• The number of columns that an INSERT INTO command can handle should not be greater than 999.
• An expression of table collection cannot be utilized in a multi-table INSERT command.
• A multi-table INSERT subquery is not capable of using a sequence.

PL SQL Update Command

The UPDATE statement is used to modify the values in a table. It is also called a Data Manipulation Language. It uses the names of the table, column, and values as inputs and performs the modification of values on the table.

Syntax:

UPDATE <<table name>> SET <<col1>>=<<val1>>, <<col2>>=<<val2>>,… WHERE <<criteria is met>>;

The SET keyword is used to change the column value with the new value. Optionally, the WHERE keyword is used, if omitted, then the value of the specified column in the complete table will be modified.

UPDATE SINGLE COLUMN OF A ROW

Let us again consider the EMPLOYEE table.

Code implementation with UPDATE:

BEGIN
UPDATE EMPLOYEE SET NAME = 'HENRY' WHERE CODE=1;
END;

The output of the above code:

SELECT * from EMPLOYEE;

UPDATE MULTIPLE COLUMNS OF A ROW

Let us again consider the EMPLOYEE table.

Code implementation with UPDATE:

BEGIN
  UPDATE EMPLOYEE SET NAME = 'COMO', AGE = 30 WHERE CODE=2;
END;

The output of the above code:

SELECT * from EMPLOYEE;

UPDATE MULTIPLE ROWS

Let us again consider the EMPLOYEE table.

Code implementation with UPDATE:

BEGIN  
UPDATE EMPLOYEE SET AGE = AGE +1;
 END;

Output of the above code:

SELECT * from EMPLOYEE;

In the above example, the AGE of all the employees increased by 1.

PL SQL Delete Command

DELETE statement is used to remove an entire record from the table. It is also called a Data Manipulation Language.

Syntax:

DELETE FROM << table name >> WHERE << criteria is met >>;

The keyword FROM is not mandatory and a DELETE statement shall yield the same result if FROM is added or not added in the query. Optionally, the WHERE keyword is used, if omitted, then the complete table will be deleted.

DELETE SINGLE ROW FROM TABLE

Let us again consider the EMPLOYEE table.

Code implementation with DELETE:

BEGIN
     DELETE FROM EMPLOYEE WHERE CODE=2;
END;

Output of the above code:

SELECT * from EMPLOYEE;

DELETE MULTIPLE ROWS FROM TABLE

Let us again consider the MANAGERDETAIL table.

Code implementation with DELETE:

BEGIN
  DELETE FROM MANAGERDETAIL WHERE CODE > 0;
END;

Output of the above code:

SELECT * from MANAGERDETAIL;

DELETE ENTIRE ROWS FROM TABLE

Let us again consider the VEHICLE table.

Code implementation with DELETE:

BEGIN
  DELETE FROM VEHICLE;
END;

Output of the above code:

SELECT * from VEHICLE;

PL SQL Select Command

The SELECT statement is used to fetch data from the database. The SELECT INTO statement is used to retrieve the values from the database and store them to the local variables introduced by the PL/SQL.

If we only use a SELECT statement, it returns a single record. In case a SELECT statement is fetching multiple values, TOO_MANY_ROWS exception is thrown by PL/SQL. While using the SELECT INTO statement, we assign at least one value to the variable. However, if no record is fetched from the database, the NO_DATA_FOUND exception is thrown.

The count of columns and their type should be equal to the count of the variables and their type in the INTO keyword. The values are obtained from the database in the same sequence as defined in the SELECT statement.

The WHERE keyword inside the SELECT statement is optional and allows us to obtain records that meet certain criteria. It is important to note that SELECT query with DELETE, UPDATE, and INSERT commands do not have an INTO clause.

Syntax:

SELECT << col1 >>, << col2 >>, ..<< coln >> INTO << var1 >>, << var2 >>, … << valn
 >> FROM << table name >> WHERE << criteria is met >>;

FETCH DATA FROM ALL COLUMNS OF A TABLE

Let us again consider the EMPLOYEE table.

Code implementation with SELECT:

BEGIN
   SELECT * FROM EMPLOYEE WHERE CODE=1;
END;

Output of the above code:

Code implementation with SELECT statement throwing an exception:

BEGIN
   SELECT * FROM EMPLOYEE WHERE CODE=2;
 END;   

Output of the above code:

The no data found exception is thrown as the SELECT statement failed to obtain a row matching with the condition specified as there is no employee with CODE 2 in the database.

FETCH DATA FROM A COLUMN OF A TABLE

Let us again consider the EMPLOYEE table.

BEGIN
 SELECT CODE FROM EMPLOYEE;
END;   

The output of the above code:

FETCH DATA FROM MULTIPLE COLUMNS OF A TABLE

Let us again consider the EMPLOYEE table.

BEGIN
   SELECT CODE, NAME FROM EMPLOYEE;
END;  

Output of the above code:

In the above example, two columns CODE and NAME have been added in the SELECT statement.

Frequently Asked Questions And Answers

Q #1) What is INSERT, UPDATE, and DELETE in SQL?

Answer: The INSERT, UPDATE, and DELETE are commands in SQL which help to operate and update data. The INSERT statement inserts rows to a table. DELETE statement deletes rows from a table and the UPDATE statement updates values in the rows of the table.

Q #2) Can we INSERT, UPDATE and DELETE in view in SQL?

Answer: Yes, we can INSERT, UPDATE, and DELETE in view in SQL. If the view has joined between the tables, we can only perform INSERT and UPDATE actions but cannot do a DELETE operation. DROP statement is used to delete a view.

Q #3) Is INSERT DDL or DML?

Answer: The INSERT is a DML or Data Manipulation Language). The other DML statements include UPDATE, DELETE, and so on. DDL statement is capable of creating tables, schema, and database.

Q #4) Can we use JOIN IN delete query?

Answer: Yes, we can use the INNER JOIN statement in the DELETE command for deleting rows from a table.

Q #5) What is the difference between truncate and delete command?

Answer: The TRUNCATE is a DDL statement while DELETE is DML. The TRUNCATE command is capable of removing the complete table. It does not keep the integrity of the table. The DELETE statement can only delete particular data on the table. It can also incorporate conditions for deletion.

Q #6) What are DML, DCL, and DDL?

Answer: SQL statements are of the types – DML, DCL, and DML.

The Data Definition Language or DDL includes commands like ALTER, DROP, CREATE, RENAME, TRUNCATE, and COMMENT. The Data Manipulation Language or DML includes commands like INSERT, DELETE, MERGE, UPDATE, CALL, LOCK TABLE and EXPLAIN PLAN.

The Data Control Language or DCL includes commands like GRANT and REVOKE.

Q #7) Is truncate faster than drop?

Answer: Yes, TRUNCATE operation is faster than DROP. The TRUNCATE command only deletes the data from the table. The DROP command erases the table structure and the data from the table.

Conclusion

In this tutorial, we have discussed in detail some basic concepts of PL SQL commands that are essential to develop knowledge on it. We have covered the following topics listed below:

• Data Manipulation Language commands.
• Usage of PL SQL  INSERT.
• Usage of PL SQL UPDATE.
• Usage of PL SQL DELETE.
• Usage of PL SQL SELECT.

<< PREV Tutorial | NEXT Tutorial>>