Gain insight into the advantages and disadvantages of Oracle stored procedures

Defined:

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures. A stored procedure is a process written by flow control and SQL statements that are compiled and optimized to be stored in the database server as long as the application is invoked. In Oracle, several linked processes can be grouped together to form a package.

Advantages

1. Stored procedures can make program execution more efficient and more secure, because after the process is built and stored in the database, the direct write SQL needs to analyze and execute the process more efficiently, the direct write SQL statement will bring security problems, such as: SQL injection. Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, and the general SQL statements are compiled once per execution, so using stored procedures increases the speed of the database execution.

2. The build process does not consume system resources because the process is executed only on invocation.

3. Stored procedures can be used to reduce network traffic and stored procedure code is stored directly in the database, so there is no code traffic for a large number of T-SQL statements.

4. Using stored procedures enables you to enhance the reuse of execution plans, thereby improving performance by using remote Procedure calls (RPC) to process stored procedures on the server. The RPC encapsulation parameters and the way the server-side procedure is invoked make it easy for the engine to find a matching execution plan and simply insert the updated parameter values.

5. With high maintainability, updating stored procedures typically requires less time and effort than changing, testing, and redeploying assemblies.

6. The code is streamlined and consistent, and a stored procedure can be used in different locations of the application code.

7. Enhanced Security:

A, by granting users access to stored procedures (rather than tables), they can provide access to specific data;

b, improve code security, prevent SQL injection (but not completely resolved, for example, the data manipulation language--dml, attached to the input parameters);

The C, SqlParameter class specifies the data type of the stored procedure parameter, which, as part of a deep-seated defensive strategy, validates the user-supplied value type (but is not foolproof or should be passed to the database for additional validation).

• You can encapsulate data logic and business rules so that users can access data and objects only in a way that developers and database administrators intend to use.
• Verifies that parameterized stored procedures for all user input can be used to prevent SQL injection attacks. If you use dynamic SQL, be sure to parameterize the command and never include the parameter values directly in the query string.
• You can prohibit ad hoc queries and data modifications. This prevents users from maliciously or unintentionally corrupting data or executing queries to avoid slowing down server or network performance.
• Errors can be handled in the procedure code without having to pass the error directly to the client application. This prevents error messages from being returned to prevent them from potentially contributing to probing attacks. Logs errors on the server and processes them.
• Stored procedures can be written only once and can be accessed by many applications.
• The client application does not need to know any information about the underlying data structure. As long as the change does not affect the parameter list or the returned data type, you can change the stored procedure code without making changes in the client application.
• Stored procedures can reduce network traffic by combining multiple operations into a single procedure call.
• Good security-access to execute stored procedures without having permission to manipulate the underlying tables directly
• Reduce network traffic-stored procedures can contain multiple SQL statements, but as long as a single statement executes the stored procedure, reducing the number and length of calls to the server by the client application
• Fast execution-The stored procedure is checked and compiled the first time it is executed, the compiled version is stored in the cache and is used to call the
• Guaranteed consistency-If the user modifies the data only through stored procedures, you can eliminate the problems caused by accidental modifications reduce errors for operators and programmers-because fewer messages are delivered, it is easier to perform complex tasks and not be prone to SQL errors

Borrowing SQL Server stored procedures to express, understand the concept, specific to Oracle please separate query

The stored procedure runs as follows:

　　

Run the CREATE proc procedure first. This resolves the query to ensure that the code is actually running. It differs from running scripts directly in that the CREATE PROC command can take advantage of so-called deferred name resolution. Deferred name resolution can ignore the fact that some objects do not yet exist.

After the stored procedure has been created, it waits for the first time to execute. At that time, the stored procedure was optimized, and the query plan was compiled and cached on the system. When you run the stored procedure several times, the cached query plan is used instead of creating a new query plan, unless specified by using the WITH RECOMPILE option. This means that each time the stored procedure is used, the stored procedure skips a lot of optimizations and compilation work. The exact time to save depends on the complexity of the batch, the size of the table in the batch, and the number of indexes on each table. Usually, the time saved is not much. But for most scenarios it could be 1 seconds or less-but this difference can be calculated by a percentage (1 seconds is 100% faster than 2 seconds). This difference becomes more pronounced when multiple invocations are required or in the case of loops.

Comparison of stored procedures and functions

Gain insight into the advantages and disadvantages of Oracle stored procedures