Stored Procedure:-
A
Stored procedure is named PL/SQL block which perform one or more
specific task.A procedure has a header and a body. The header consists
of the name
of the procedure and the parameters or variables passed to the
procedure. The body consists or declaration section, execution section
and exception section similar to a general PL/SQL Block.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
Function:-
A
function is a named PL/SQL Block which is similar to a procedure.The
major difference between a procedure and a function is, a function
must always return a value, but a procedure may or may not return a
value.
You can call your function in multiple ways like following:-
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
Difference between function and procedure
1. Function is mainly used in the case where it must return a value.
Where as a procedure may or may not return a value or may return more
than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored
Procedure: supports deferred name resolution. Example while writing a
stored procedure that uses table named table1 and table2 etc..but actually
not exists in database is allowed only in during creation but runtime
throws error Function wont support deferred name resolution.
7.Stored
procedure returns always integer value by default zero. where as
function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A
procedure may modify an object where a function can only return a value
The RETURN statement immediately completes the execution of a
subprogram and returns control to the caller
Advantages and Comparison of Stored Procedure/Function with normal SQL:-
Overhead: Stored procedure statements are stored directly in the database, they may
remove all or part of the compilation overhead that is typically
required in situations where software applications send inline (dynamic)
SQL queries to a database.
In addition, while they avoid some overhead, pre-compiled SQL
statements add to the complexity of creating an optimal execution plan
because not all arguments of the SQL statement are supplied at compile
time. Depending on the specific database implementation and
configuration, mixed performance results will be seen from stored
procedures versus generic queries or user defined functions.
Avoidance of network traffic: A major advantage with stored
procedures is that they can run directly within the database engine. The benefit here is that network communication
costs can be avoided completely. This becomes particularly important for
complex series of SQL statements.
Encapsulation of business logic: Stored procedures allow programmers to embed business logic an API in the database, which can simplify data management and
reduce the need to encode the logic elsewhere in client programs. The database system can ensure data integrity and consistency
with the help of stored procedures.
Delegation of access-rights: In many systems, stored
procedures can be granted access rights to the database that users who
execute those procedures do not directly have.
Some protection from SQL injection attacks: Stored procedures
can be used to protect against injection attacks. Stored procedure
parameters will be treated as data even if an attacker inserts SQL
commands. Also, some DBMSs will check the parameter's type.
Disadvantage of procedure/functions:-
Vender Specific:-Stored procedure languages are quite often vendor-specific. Switching to
use another vendor's database most likely requires rewriting any
existing stored procedures.
Restricted Overloading Functionality:- Procedure
and function overloading is available only within database packages or
as PL/SQL sub-blocks. Database stored procedures cannot be overloaded.
If you try to add a second overloaded stored procedure with the same
procedure identifier, an error will result.
Disabled Procedure:-Database
procedures can be accidentally dropped by a person with sufficient
privilege. This is nothing new, except that there is no practical way of
writing application code to detect inoperable procedure(s). Since SQL
and PL/SQL are interpreted languages, an invalid or missing stored
procedure is not detectable until module run-time. When the error is
encountered, a confusing, cryptic database error results that is of very
little help to the average application user.
Automatic dependency tracking:-When
a simple DDL operation, such as adding a new column to an existing
table, is performed, many stored procedures are marked as needing
recompilation. What is not provided is the ability to recompile all
these dependencies in a single action or to ascertain which procedures
are, in fact, invalid. Other than compiling each object one at a time
and checking its results, we have no way of determining the effect of a
DDL modification.
