Stored Procedure For Insert, Update, Select, Delete Using Oracle

Introduction

This is a beginner's article showing how to create a Stored Procedure doing inserts, updates, selects and deletes using Oracle.

Step 1

The following will create a table in Oracle. For example I will create a table for customer details.

CREATE TABLE CUSTOMER (
NAME VARCHAR2(20),
GENDER VARCHAR2(7),
ADDRESS VARCHAR2(100));

Step 2

After creating the table, write a Stored Procedure for an insert:

CREATE OR REPLACE PROCEDURE INSERTcustomer (
p_name CUSTOMER.NAME%TYPE,
p_gender CUSTOMER.GENDER%TYPE,
p_address CUSTOMER.ADDRESS%TYPE)
IS
BEGIN
INSERT INTO CUSTOMER (NAME, GENDER, ADDRESS)
VALUES (p_name, p_gender, p_address);
COMMIT;
END;
/

Step 3

Stored Procedure for an update:

CREATE OR REPLACE PROCEDURE UPDATEcustomer (
p_name IN CUSTOMER.NAME%TYPE,
p_gender IN CUSTOMER.GENDER%TYPE,
p_address IN CUSTOMER.ADDRESS%TYPE)
IS
BEGIN
UPDATE CUSTOMER SET NAME=p_name, GENDER=p_gender, ADDRESS=p_address WHERE NAME=p_name;
COMMIT;
END;
/

Step 4

Stored Procedure for a select:

CREATE OR REPLACE PROCEDURE SELECTcustomer (
p_name IN CUSTOMER.NAME%TYPE,
p_customer_display OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_customer_display for SELECT NAME, GENDER, ADDRESS FROM CUSTOMER WHERE NAME=p_name;
END;
/

Step 5

Stored Procedure for a delete:

CREATE OR REPLACE PROCEDURE DELETEcustomer (
p_name IN CUSTOMER3.NAME%TYPE)
IS
BEGIN
DELETE FROM CUSTOMER WHERE NAME=p_name;
END;
/