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 :-
- 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.
PLSQL CURSORS
CURSORS:-
Cursor is a method
to access more than one record from database. It will allocate an area
of memory known as context area. It contains information necessary
information including the no of rows to be processed.
Types of CURSOR’S :-
- Implicit Cursor:- It is used for all SQL Statements (DML). It is opened and closed by the PL/SQL Engine Implicitly.
- Explicit Cursor :- It has to be define Explicitly we have to do
1. Declare the Cursor.
2. Open the Cursor for a Query.
3. Fetch the records into the PL/SQL variables.
4. Close the Cursor.
STEPS REQUIRED FOR CURSOR MANAGEMENT
1) Declaration Of Cursor.
Cursor name and select statement for the cursor
to be provided in DECLARE division. During this
plsql keeps information about cursor only.
CURSOR <cursor name> IS <query>;
2) Opening of Cursor.
During this step, plsql performs the following:
- allocation of memory for cursor.
- retrieval of records into cursor from db.
- Positioning record pointer at 1st record.
OPEN <cursor name>;
3) Fetching record values from cursor into plsql
variables.
Values from current record are retrieved from
cursor into plsql variables and record pointer
is moved to next record.
FETCH <cursor name> INTO <variable list>;
4) Closing of Cursor.
During this, plsql removes memory reserved for
cursor so thelink between db & cursor is gone.
CLOSE <cursor name>;
CURSOR ATTRIBUTES
These are system variables provided by plsql to
keep the cursor status. These are :
1. %FOUND :- Keeps boolean value that indicate whether
fetch is successful or failure. If TRUE
then success and if FALSE failure.
2. %NOTFOUND :- reverse of the above.
3. %ROWCOUNT :- Keeps the number value that indicate how
many records have been fetched. In other
words current record number which always
start from 1.
4. %ISOPEN :- Keeps boolean value to indicate whether
cursor is open or close. True for Open &
False for Not Open.
These are used with cursor name. The syntax is:
<cursor name><attribute name>;
Ex: C1%Found;
LIMITATIONS:-
1) CURSOR is READ only. That means DML operations
are not allowed using CURSOR. Only records can
be retrieved.
2) CURSOR is FORWARD only. i.e. record pointer in
cursor can not move back to previous records.
No comments:
Post a Comment