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

Details

In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that takes care of fetching and updating of data in the database whereas PL/SQL is the component that processes these data. Further, in this article, we will also discuss how to combine the SQL within the PL/SQL block.

In this tutorial, you will learn-

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.

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;

Syntax: 

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

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;

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;

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.

Syntax:

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

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'.

SQL in PL/SQL 

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:

 

YOU MIGHT LIKE: