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:
- UTL_FILE.FREMOVE Example in PL/SQL
- 20 Useful Oracle Insert Statement Examples
- Oracle TO_CHAR (number) Examples
