Oracle PL/SQL Insert, Update, Delete & Select Into [Example]

 DML Transactions in PL/SQL

DML stands for Data Manipulation Language. These statements are mainly used to perform the manipulation activity. It deals with the below operations.

• Data Insertion
• Data Update
• Data Deletion
• Data Selection

In PL/SQL, we can do the data manipulation only by using the SQL commands.

Data Insertion

In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column and column values as the input and insert the value in the base table.

The INSERT command can also take the values directly from another table using 'SELECT' statement rather than giving the values for each column. Through 'SELECT' statement, we can insert as many rows as the base table contains.

Syntax:

BEGIN
  INSERT INTO <table_name>(<column1 >,<column2>,...<column_n>)
     VALUES(<valuel><value2>,...:<value_n>);
END;

• The above syntax shows the INSERT INTO command. The table name and values are a mandatory fields, whereas column names are not mandatory if the insert statements have values for all the column of the table.
• The keyword 'VALUES' is mandatory if the values are given separately as shown above.

Syntax:

BEGIN
  INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>)
     SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;

• The above syntax shows the INSERT INTO command that takes the values directly from the <table_name2> using the SELECT command.
• The keyword 'VALUES' should not be present in this case as the values are not given separately.

Data Update

Data update simply means an update of the value of any column in the table. This can be done using 'UPDATE' statement. This statement takes the table name, column name and value as the input and updates the data.

Syntax:

BEGIN	
  UPDATE <table_name>
  SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n> 
  WHERE <condition that uniquely identifies the record that needs to be update>; 
END;

• The above syntax shows the UPDATE. The keyword 'SET' instruct that PL/SQL engine to update the value of the column with the value given.
• 'WHERE' clause is optional. If this clause is not given, then the value of the mentioned column in the entire table will be updated.

Data Deletion

Data deletion means to delete one full record from the database table. The 'DELETE' command is used for this purpose.

Syntax:

BEGIN
  DELETE
  FROM
  <table_name>
  WHERE <condition that uniquely identifies the record that needs to be update>; 
END;

• The above syntax shows the DELETE command. The keyword 'FROM' is optional and with or without 'FROM' clause the command behaves in the same way.
• 'WHERE' clause is optional. If this clause is not given, then the entire table will be deleted.

Data Selection

Data projection/fetching means to retrieve the required data from the database table. This can be achieved by using the command 'SELECT' with 'INTO' clause. The 'SELECT' command will fetch the values from the database, and 'INTO' clause will assign these values to the local variable of the PL/SQL block.

Below are the points that need to be considered in 'SELECT' statement.

• 'SELECT' statement should return only one record while using 'INTO' clause as one variable can hold only one value. If the 'SELECT' statement returns more than one value than 'TOO_MANY_ROWS' exception will be raised.
• 'SELECT' statement will assign the value to the variable in the 'INTO' clause, so it needs to get at least one record from the table to populate the value. If it didn't get any record, then the exception 'NO_DATA_FOUND' is raised.
• The number of columns and their datatype in 'SELECT' clause should match with the number of variables and their datatypes in the 'INTO' clause.
• The values are fetched and populated in the same order as mentioned in the statement.
• 'WHERE' clause is optional that allows to having more restriction on the records that are going to be fetched.
• 'SELECT' statement can be used in the 'WHERE' condition of other DML statements to define the values of the conditions.
• The 'SELECT' statement when using 'INSERT', 'UPDATE', 'DELETE' statements should not have 'INTO' clause as it will not populate any variable in these cases.

Syntax:

BEGIN
  SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n> 
   FROM <table_name>
   WHERE <condition to fetch the required records>;
END;

• The above syntax shows the SELECT-INTO command. The keyword 'FROM' is mandatory that identifies the table name from which the data needs to be fetched.
• 'WHERE' clause is optional. If this clause is not given, then the data from the entire table will be fetched.

Example 1: In this example, we are going to see how to perform DML operations in PL/SQL. We are going to insert the below four records into emp table.

EMP_NAME	EMP_NO	SALARY	MANAGER
BBB	1000	25000	AAA
XXX	1001	10000	BBB
YYY	1002	10000	BBB
ZZZ	1003	7500	BBB

Then we are going to update the salary of 'XXX' to 15000, and we are going to delete the employee record 'ZZZ'. Finally, we are going to project the details of the employee 'XXX'.

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER; 
l_manager VARCHAR2(250);
BEGIN	
INSERT INTO emp(emp_name,emp_no,salary,manager) 
VALUES(‘BBB’,1000,25000,’AAA’);
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,’BBB);
INSERT INTO emp(emp_name,emp_no,salary,managed 
VALUES(‘YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager) 
VALUES(‘ZZZ',1003,7500,'BBB'):‭
COMMIT;
Dbms_output.put_line(‘Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line(‘Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT:
Dbms_output.put_line('Values Deleted );
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';

Dbms output.put line(‘Employee Detail’);
Dbms_output.put_line(‘Employee Name:‘||l_emp_name);
Dbms_output.put_line(‘Employee Number:‘||l_emp_no);
Dbms_output.put_line(‘Employee Salary:‘||l_salary);
Dbms output.put line(‘Emplovee Manager Name:‘||l_manager):
END;
/

Output:

Values Inserted
Values Updated
Values Deleted
Employee Detail 
Employee Name:XXX 
Employee Number:1001 
Employee Salary:15000 
Employee Manager Name:BBB

Code Explanation:

• Code line 2-5: Declaring the variable.
• Code line 7-14: Inserting the records into emp table.
• Code line 15: Committing the insert transactions.
• Code line 17-19: Updating the salary of the employee 'XXX' to 15000
• Code line 20: Committing the update transaction.
• Code line 22: Deleting the record of 'ZZZ'
• Code line 23: Committing the delete transaction.
• Code line 25-27: Selecting the record of 'XXX' and populating into the variable l_emp_name, l_emp_no, l_salary, l_manager.
• Code line 28-32: Displaying the fetched records value.