PL/SQL Program to Delete The Records From Table

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 1 min read

In this tutorial, you will learn how to delete the records from a table in Oracle using PL/SQL.

In Oracle PL/SQL, we can delete the records using DELETE DML Statement and be using TRUNCATE DDL command. I am giving below 2 examples based on EMP table of SCOTT schema to delete the records using DELETE and TRUNCATE commands.

PL/SQL Program to Delete The Records From Table Examples

1. Using DELETE Statement

In Oracle, DELETE Statement is used to delete the records from a table. There is a COMMIT (to save) or ROLLBACK  (to undo) statement is required after performing the delete operation. The following example will delete the records from EMP table where JOB is equal to SALESMAN using Delete statement.

BEGIN
DELETE FROM EMP
WHERE job = 'SALESMAN';

COMMIT;
END;
/

2. Using TRUNCATE Command.

In Oracle, TRUNCATE Command is used to delete all the records from a table permanently. So after using the TRUNCATE command, there is no need to do COMMIT. The following example will delete all the records from the EMP table.

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;
/

See also: