Oracle Stored Procedure DELETE example

Here’s a DELETE stored procedure example in Oracle database.

1. Table SQL Script

DBUSER table creation script.

CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Stored Procedure

A stored procedure, delete the record base on the provided userId.

CREATE OR REPLACE PROCEDURE deleteDBUSER(p_userid IN DBUSER.USER_ID%TYPE)
IS
BEGIN

  DELETE DBUSER where USER_ID = p_userid;
  
  COMMIT;

END;
/

3. Calls from PL/SQL

Call from PL/SQL like this :

BEGIN
   deleteDBUSER(1001);
END;

Result
Record of userid=1001 is deleted via deleteDBUSER store procedure.