Oracle

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.

  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. /   

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found