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 :-
- Declarative Part.
- Executable Part.
- Optional Exception –handling part.
Advantages of using a Procedures or Functions
- Security :- Stored Procedure and Function can help enforce data security.
- Performance:- Amount of information sent over a network is less. No compilation step is required to execute the code.
- 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.
- Productivity:- By writing Procedure or Function redundant coding can be avoided.
- 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.