Main Tutorials

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.

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
mjeed
4 years ago

thank you so much sir, you give me a lot of help , i will Gertrude soon and i learn so much from you , hope you have a wonderful time with your life , may Allah(god) full your life with happiness .