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.

  1. CREATE TABLE CUSTOMER (
  2. NAME VARCHAR2(20),
  3. GENDER VARCHAR2(7),
  4. ADDRESS VARCHAR2(100));
Step 2

After creating the table, write a Stored Procedure for an insert:
  1. CREATE OR REPLACE PROCEDURE INSERTcustomer (
  2. p_name CUSTOMER.NAME%TYPE,
  3. p_gender CUSTOMER.GENDER%TYPE,
  4. p_address CUSTOMER.ADDRESS%TYPE)
  5. IS
  6. BEGIN
  7. INSERT INTO CUSTOMER (NAME, GENDER, ADDRESS)
  8. VALUES (p_name, p_gender, p_address);
  9. COMMIT;
  10. END;
  11. /

Step 3

Stored Procedure for an update:

  1. CREATE OR REPLACE PROCEDURE UPDATEcustomer (
  2. p_name IN CUSTOMER.NAME%TYPE,
  3. p_gender IN CUSTOMER.GENDER%TYPE,
  4. p_address IN CUSTOMER.ADDRESS%TYPE)
  5. IS
  6. BEGIN
  7. UPDATE CUSTOMER SET NAME=p_name, GENDER=p_gender, ADDRESS=p_address WHERE NAME=p_name;
  8. COMMIT;
  9. END;
  10. /

Step 4

Stored Procedure for a select:

  1. CREATE OR REPLACE PROCEDURE SELECTcustomer (
  2. p_name IN CUSTOMER.NAME%TYPE,
  3. p_customer_display OUT SYS_REFCURSOR)
  4. IS
  5. BEGIN
  6. OPEN p_customer_display for SELECT NAME, GENDER, ADDRESS FROM CUSTOMER WHERE NAME=p_name;
  7. END;
  8. /

Step 5

Stored Procedure for a delete:

  1. CREATE OR REPLACE PROCEDURE DELETEcustomer (
  2. p_name IN CUSTOMER3.NAME%TYPE)
  3. IS
  4. BEGIN
  5. DELETE FROM CUSTOMER WHERE NAME=p_name;
  6. END;
  7. /