santosh-asp tutorial

Oracle Store Procedures

A procedure is a group of PL/SQL statements that can be called by name. The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL.

How to create Store Procedure:-
Syntax:-

  

    CREATE [OR REPLACE] PROCEDURE procedure_name  
        [ (parameter [,parameter]) ]  
    IS  
        [declaration_section]  
    BEGIN  
        executable_section  
    [EXCEPTION  
        exception_section]  
    END [procedure_name];

Explanation:- Following are the three types of procedures that must be defined to create a procedure.

IN:- It is a default parameter. It passes the value to the subprogram.
OUT:- It must be specified. It returns a value to the caller.
IN OUT:- It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.

Example:- Example:- We create table named with Employee as shown in below:

  
   CREATE TABLE Employee  
    (
    Empcode varchar2(30) primary key,
    Name varchar2(100),
    Age number(5),   
    Salary numeric(10,2),
    Email varchar2(200),   
    State varchar2(100)  
    CONSTRAINT Employee_pk PRIMARY KEY (Empcode)  
    );

Now, we create a store procedure for insert records in 'Employee' table as shown in below:-

  

create or replace procedure "USP_Insert"    
(
    Empcode in varchar2,
    Name in varchar2,
    Age in number,   
    Salary in numeric,
    Email in varchar2,   
    State in varchar2 
)    
is    
begin    
insert into Employee (Empcode,Name,Age,Salary,Email,State) values(Empcode,Name,Age,Salary,Email,State);
end;

How to call or execute Store Procedure in Oracle:-

Syntax:

  

BEGIN    
   store_procedure_name(paramete lists);  
   dbms_output.put_line('User statement for display message.');    
END;

Example:- Now, we insert some records by store procedure USP_Insert as shown below:-

  

BEGIN    
   USP_Insert('Emp1001','Santosh Kumar Singh',22,12000,'s@gmail.com','Bihar');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;  

//Now, we can insert multiple records by using store procedure as shown in below.

BEGIN    
   USP_Insert('Emp1002','Reena kumari',25,15000,'r@gmail.com','Bihar');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;  

BEGIN    
   USP_Insert('Emp1003','Anu Singh',21,14000,'a@gmail.com','UP');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;

BEGIN    
   USP_Insert('Emp1004','Gagan Agrawal',24,11000,'g@gmail.com','Haryana');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;

BEGIN    
   USP_Insert('Emp1005','Suraj',21,13000,'su@gmail.com','Bihar');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;

BEGIN    
   USP_Insert('Emp1006','Pramod kumar sah',27,14000,'pk@gmail.com','New Delhi');  
   dbms_output.put_line('Record has been inserted successfully.');    
END;

Select * from Employee
Out Put:-

Oracle Drop Procedure
Syntax:-

  

DROP PROCEDURE procedure_name;

Example:-

  

DROP PROCEDURE USP_Insert;