When the SQL statement is executed, it must be compiled and then executed. The stored procedure is to compile some SQL statements. It can be used directly when used. So the efficiency will be high
A stored procedure describes the process of writing by stream control and SQL statement. This process is stored in the database server after compiling and optimizing, as long as the call is used. In Oracle, several associated processes can be combined together to constitute a package. The use of the stored procedure has the following advantages: * The ability to store procedures greatly enhance the functionality and flexibility of SQL language. The stored procedure can be written with stream control statements, with strong flexibility, can complete complex judgment and more complex operations. * The security and integrity of the data can be guaranteed. # By stored procedures, users with no permissions can be indirectly accessed under control, thereby ensuring the security of data. # By the stored procedure, the associated action can occur together, so that the integrity of the database can be maintained. * Before running the stored procedure, the database has been analyzed and syntax analysis and gives an optimized execution plan. This process that has been compiled can greatly improve the performance of the SQL statement. Since most of the work of executing the SQL statement has been completed, the stored procedure can be implemented at a very fast speed. * The amount of traffic can be reduced. * Make an arithmetic program that reflects the rules of the enterprise in the database server, so that: # Control. # When the corporate rule changes, change the stored procedure in the server, no need to modify any applications. The characteristics of corporate rules are to change frequently. If the computing program embodying the corporate rules is placed in the application, the application workload is very large (modification, distribution, and installation applications) when the corporate rule changes. . If the operation of the company's rules is placed in the stored procedure, the application is not required when the company rule changes, and the application is not required. Different data fixtures are not written, and the usage of Oracle and Sybase stored procedures will be described in later lectures. Two Oracle stored procedures Oracle Create the syntax of the stored procedure: create [or report] Procedure Process Name Parameters 1 [IN | OUT | IN OUT] Data Type [, parameter 2 [in | OUT | IN OUT] Data Types]. The PL / SQL statement will illustrate the writing and usage of the Oracle database stored procedure. You can create a stored procedure, whenever the user modifies important data from the database, 即 用户 用户 名 矗 矗 矗 矗 矗 矗 n 矗;;;;;; d d d n;;;; 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗 矗,, Operation) VALUES (user, sysdate, 'update' end; this stored procedure can be called in the appropriate location to record the modification of the user's table. For example, create a modified trigger on the table Sal_COMM, whenever the user modifies this table, The user's name, modification time, and operation are recorded in Table Update_log_tab: CREATE TRIGGER AUPDATE AFTER UPDATE TRIGGER AUPDATE AFTER UPDATE TRIGGER AUPDATE AFTER UPDATE TRIGER AUG END - The stored procedures for the Sybase stored procedures are similar to Oracle, but similar, They still have a certain difference. The memory process of Sybase is described below.
