Saturday, October 25, 2008

How to create procedure in Oracle.

Stored procedure is a data base object which has some syntax defined to make it undestand by the server to perform actions upon request.
Simillarly we have functions,triggers,views etc... called as data base objects that are once if compiled can be used to perform functionalities required.

Syntax for creating a stored procedure.

Create or Replace [Procedure Name]
(
Input/Output parameters list
)
as
[Variable declarations]
begin
[procedure body]
end;

Example:-
Procedure to find sum of 2 numbers
Create or Replace Procedure SumNos
(
a1 in number,//Input parameter
a2 in number,//Input parameter
a3 out sys_refcursor //Output parameter
)
as
tempSum number; //Variable declaration
begin
//Procedure body
tempSum:=a1+a2;
// To open a3 cursor and fill the result set from below select query
open a3 for
select tempSum as 'Sum' from dual;
end;


The above stored procedure can be copied and pasted directly in sql plus to create procedure in data base.
Note: It says create or replace which means if there exists a procedure with same name then it will be relaced with this new procedure content.

No comments: