The difference between stored procedures <3> and functions

Ii. advantages of functions and stored procedures:

1, the common use of the code can only need to be written once, and is required to the code of any application calls (. Net,c++,java, can also make DLL library).

2, this kind of several kinds of writing, several maintenance updates, everybody sharing method, simplifies the application development maintenance, improves the efficiency and the performance.

3, this modular method makes a complex problem, the large program is gradually simplified into a few simple, small program parts, to be written separately, so the structure of the program is more clear, simple, and easy to implement.

4, can provide data processing, control process, prompt information and other aspects of the consistency between the various developers.

5, save memory space. They are stored in external memory in a compressed form and are placed into memory for processing when they are called. And when multiple users call the same stored procedure or function, they only need to be loaded once.

6, improve the security and integrity of data. By putting some manipulation of the data into a stored procedure or function, you can restrict some users from doing this to the database by granting them permission to execute the statement.

Three, the difference between the function and the stored procedure:

1. The stored procedure user completes a specific operation or task (such as inserting, deleting, etc.) in the database, and the function is used to return the specific data.

2, stored procedure declaration with procedure, functions with function.

3. The stored procedure does not require a return type, and the function must return the type.

4. The stored procedure can be executed as a standalone pl-sql, the function cannot be executed as a standalone plsql and must be part of the expression.

5. Stored procedures can only return values through out and in/out, and the function may return a value using return, in addition to Out,in/out.

6. Calling stored procedures is not available in SQL statements (DML or select), and functions can.

Iv. Applicable occasions:

1. If you need to return multiple values and do not return a value, use a stored procedure, or use a function if you only need to return a value.

2. Stored procedures are typically used to perform a specified action, and functions are generally used to calculate and return a value.

3. You can call functions inside SQL to complete complex computational problems, but you cannot call stored procedures.

The difference between stored procedures <3> and functions