ORACLE PROCEDURES AND FUNCTIONS

What are Procedure and Functions?

A Procedure or Function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A stored Procedure or Function is a named PL/SQL  code block that has been complied and stored in one of the Oracle engine’s system tales.

Procedures or Functions are made up of :-

1. Declarative Part.
2. Executable Part.
3. Optional Exception –handling part.

Advantages of using a Procedures or Functions

1. Security :- Stored Procedure and Function can help enforce data security.

2. Performance:- Amount of information sent over a network is less. No compilation step is required to execute the code.

3. Memory Allocation:- The stored Procedure or Function having shared memory capabilities. Only on copy of procedure or Function needs to be loaded for execution by multiple users.

4. Productivity:- By writing Procedure or Function  redundant coding can be avoided.

5. Integrity:- A Procedure or Function needs to be tested only once to guarantee that it returns an accurate result.

Procedure and Function Differences?

The procedure may or may not return a value. It will return the values by its own name and function must and should return a value and it will return values with function name.

Function is executed in select statement. Procedure is called using

Exec.

A procedure is capable of performing DML operations. Where as

A function is not capable of doing DML operations except SELECT.

A Procedure reduce the code redundancy using out parameter concept where as It cannot be avoided in a Function.

A Procedure is a complete executable statement of PL/SQL block where as A Function  is a part of statement of PL/SQL block.