Home » PLSQL » PL/SQL Program to Delete The Records From Table

PL/SQL Program to Delete The Records From Table

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: