sql

Learn Oracle sql : How to create stored procedure in SQL

This is the series for learning Oracle SQL . In this blog we will learn  to create stored procedure in SQL . Stored procedure is the stored code in Oracle database which can be reused again & again. If you have some statement which you have to execute again and again then you can write it in stored procedure.

Stored Procedure in SQL

Syntax


CREATE OR REPLACE PROCEDURE <Procedure_Name>(<Variables>)
AS
BEGIN
<SQL Statement>
END ;
/

In the above given code we use CREATE OR REPLACE PROCEDURE  statement for creating the procedure and where  <Procedure_Name> here you have to write the procedure name. In <Variable> field you have to define the input and output parameter used in the procedure. And In the <SQL Statement> you have to write the code which you have to execute again and again.

 

Lets understand it with an example , we have table Customer_Detail

IDNAMECITY PHONEPIN
1RejeevDehradun8266034546248001
2SamRudrapur8564524524263153
3SameulNew Delhi8889996662110001

This table contain column ID ,NAME,CITY,PHONE & PIN . We will understand procedure by taking this table as a reference .

Create Stored Procedure with Single Variable

In the above table I want the details of the customer who belongs to the particular city. I will write the procedure for this case.


CREATE OR REPLACE PROCEDURE GET_DETAILS_CITY( P_city Varchar2) AS
BEGIN
SELECT * FROM Customer_Detail WHERE CITY=P_city;
END;
/

In this procedure GET_DETAILS_CITY we passed the variable P_city then the sql statement  filter the table Customer_Detail WHERE CITY=P_city;

For executing the Stored procedure we use Exec  Statement.

Exec GET_DETAILS_CITY (‘Dehradun’);

Output : 

IDNAMECITYPHONEPIN
1RejeevDehradun8266034546248001

 

Create Stored Procedure with more than one Variable

You have seen the procedure above with one variable , now we will see it with multiple variables.Now we will pass three variables P_CITY,P_PIN,P_NAME . The procedure will process the data by using those variable in sql statement to get the desired output.


CREATE OR REPLACE PROCEDURE GET_CUSTOMER_DETAILS
(P_CITY VARCHAR2,P_PIN NUMBER,P_NAME VARCHAR2) IS
BEGIN
SELECT * FROM CUSTOMER_DETAIL where NAME=P_NAME AND CITY=P_NAME AND PIN=P_PIN;
END ;
/

sql

In this procedure GET_CUSTOMER_DETAILS  we passed the  3 variable  then the sql statement  filter the table CUSTOMER_DETAIL where  NAME=P_NAME AND CITY=P_NAME AND PIN=P_PIN;

For executing the Stored procedure we use Exec  Statement.

Exec GET_CUSTOMER_DETAILS  (‘Rudrapur’,’Sam’,263153);

Output:

IDNAMECITYPHONEPIN
2SamRudrapur8564524524263153

This is all about creating the stored procedure .I hope you understood it well .

Also read :

 

 

  •  
    15
    Shares
  • 15
  •  
  •  
  •  
  •  
  •  

About the Author

Prashant Goswami

I am the blog writer who will teach how to love programming. My experience in programming is about 6 years. And i am the lover of programming , so i will make you people.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: