PL/SQL Function and Procedure

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.