Procedure

Stored Procedure

Function and Procedure:
1. Function must return a value
Procedure might(using OUT parameter)  or might not return a value.

2. Function can not call procedure.
Procedure can call function.

3. Function can use SELECT only.
Procedure can use SELECT and other DML commands.

4. Function can be called using SQL queries.
Procedure can not be called using SQL queries.

5. Function is compiled every time it is called.
Procedure is compiled once only and there is no need to compile everytime it is used.


Below section is in Progress. You can use this as reference but these might need correction since this is still in progress.

Procedure:

Procedure parameters could be IN , OUT, IN OUT . When you declare nothing that means it is IN , IN is default.

Components of a SQL stored procedure

Create or Replace Procedure ProcedureName(Variables if needed) > “or Replace ” is optional. If you use replace, that will replace the existing procedure if it exists already.

IS/AS

Declarations

Begin

Statements to be executed ;

Exception

Statements to handle exceptions;

End ProcedureName ; > Procedure name is optional

Drop Procedure:

Drop Procedure ProcedureName ;

Example 1 > To see the retrieve table data

Set ServerOutput ON;

Create or replace procedure SeeTableData()

Is

Begin

Execute immediate ‘Select * from TableName’ ;

End;

Exec SeeTableData ;

Example 2 > find square of the input data

Create or replace procedure FindSquare(i IN int )

Is

Begin

Dbms_output.enable();

Dbms_output.putline(‘The square of the given value is : ’||i*i) ;

End;

Exec FindSquare(5);

Example 3 :

Create or Replace Procedure Test1(Increment IN TableName.ColumnName%Type)

IS

NewSalary  int ;

FullName  TableName.Emp_Name%Type ;

Begin

Select Salary+Increment , FirstName||LastName into NewSalary, FullName from EMP_Table where Dept = 10;

Dbms_output.enable();

Dbms_output.putline(‘Full Name is : ’|| FullName || ’. His new salary is : ’|| NewSalary) ;

End;

Exec FindSquare(100.00);

Example 4: Use of %RowType

Create or Replace Procedure Test2(FindEmp_id IN int)

IS

RowTypeVariable  TableName%RowType ;

Begin

Select  *  into RowTypeVariable from TableName where emp_id = FindEmp_id;

Dbms_output.enable();

Dbms_output.putline(‘Name of the employee is : ’|| RowTypeVariable.FirstName || ‘  ‘ ||RowTypeVariable.LastName);

Exception

When Others then

Dbms_output.putline(SQLERRM)

End;

Example 5 : Use of IN and OUT variable

Create or Replace Procedure Test1(i IN int, j OUT int)

IS

Begin

Select SALARY into j where EMP_ID = I;

End;

Call above method like below :

Declare

x int ;

y int;

begin

x=:100;

exec Test1(x,y);   or exec Test1(100,y);

dbms_output.putline(y);

end;