The difference between the stored procedure and function

First, as shown in figure Oracle, the differences in the

The stored procedure Function
For the completion of specific operations or tasks in data For the specific data
Program head procedure state Program head function state
Program head that does not need to describe the return type Program head claims to describe the return type, and the PL/SQL must contain at least one valid return statement
You can use the in/out/ in out You can use the in/out/in out
Can be used as an independent PL/SQL statements to execute Cannot be executed immediately, must be part of the expression
By out Returns a value by using the return statement
Call any stored procedures may not be a SQL statement Can call the SQL function statement

Essentially no difference. Only the function such as: can only return a variable constraints. The stored procedure can return multiple. The function can be embedded in SQL, you can call in select, and stored procedures. Nature of the implementation are the same.
    Function more restrictions, such as not using temporary tables, can only use a table variable. There are some functions are not available. The storage process constraints are relatively small
1 in general, the stored procedure to achieve the function is a bit more complicated, but the function of the realization of the function of targeted relatively strong.

2 for stored procedures can return parameters, and a function can only be the return value or a table object.

The 3 storage process is generally as an independent part to perform, and can function as a part of the query to call, because the function

      Returns a table object, so it can be located behind the FROM keyword in the query statement.

4 when the stored procedure and function is executed, SQL will Manager to query for the corresponding procedure cache, if the

      No query corresponding procedure cache, SQL Manager will be on the stored procedures and functions are compiled.

Procedure cache: Is to save the execution plan, when compiled after the implementation of procedure in cache execution plan, SQL SERVER according to the actual situation of each execution plan to consider whether to save the plan in cache, judged a is the execution plan may be the frequency of use; second is to generate the plan price also is the compile time. Stored in the cache plan without the next execution to compile.

The difference between the stored procedure and function specific:

Stored procedures: can make the management of user information, and display their work much easier. The stored procedure SQL statements and optional control of flow statements precompiled collection, stored under a name and processed as a unit. Stored procedures in the database, the application can through a call, but allows the user declared variables, conditional execution, and other powerful programming function. The stored procedure contains program flow, logic, and queries to a database. They can accept parameters, output parameters, return a single or multiple result sets and return value.

You can use a stored procedure using the SQL statement for any purpose, it has the following advantages:

(1)Powerful, less limitation.

(2)You can execute a series of SQL statements in a single storage process.

(3)Can reference other stored procedure from the stored procedure itself, which can simplify a series of complex sentences.

(4)A stored procedure is created that is compiled in, so to implement than a single SQL statement soon.

(5)There can be multiple return values, namely the multi output parameters, and can use SELECT to return a result set.

(6)Store in the process of handling large amounts of data, and the function of processing small amounts of data;

Function: is composed of one or more SQL statements subroutine, can be used to encapsulate code for reuse. Custom function restrictions, there are many statements cannot use, many functions can not be achieved. Function and can be used directly for the return value, using table variables, return a recordset. However, user defined functions cannot be used to perform a set of modify the global state of the database operation.

Posted by Ahern at November 24, 2013 - 9:31 PM