Shulou(Shulou.com)06/01 Report--

Stored procedures, as the name suggests, are equivalent to a set of sql statements that are compiled to perform a specific function and stored in a database. The user executes the stored procedure by specifying its name and giving parameters (if the stored procedure has parameters). Stored procedures are an important object in a database and should be used by any well-designed database application.

Written format for stored procedures:

CREATE PROCEDURE [Owner.] Stored procedure name [; procedure number]

[(Parameter #1,…Parameter #1024)]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

]

[FOR REPLICATION]

AS program line

The stored procedure name cannot exceed 128 characters. Up to 1024 parameters per storage procedure

(SQL Server 7.0 and above), the parameters are used as follows:

@ Parameter Name Data Type [VARYING] [= Default] [OUTPUT]

Each parameter name should be preceded by an "@" symbol. The parameters of each stored procedure are only used internally by the program. The types of parameters can be used by other data types supported by SQL Server except IMAGE.

output: Indicates that this parameter is returnable

with {recompile|encryption}

recompile: Indicates that this stored procedure is recompiled each time it is executed

encryption: The contents of the stored procedure created are encrypted

Using stored procedures has the following advantages:

* The ability to store procedures greatly enhances the power and flexibility of the SQL language. Stored procedures can be written with flow control statements, have strong flexibility, and can complete complex judgments and more complex operations.

* Data security and integrity can be guaranteed.

#Storage procedures allow unauthorized users to access the database indirectly under control, thus ensuring data security.

#Stored procedures allow related actions to occur together, thereby maintaining the integrity of the database.

* Before running the stored procedure again, the database has parsed it syntactically and syntactically, and given an optimized execution scheme. This compiled procedure can greatly improve the performance of SQL statements. Because most of the work of executing SQL statements is already done, stored procedures can execute extremely quickly.

* Traffic on the network can be reduced.

* Put an algorithm embodying the rules of the enterprise into a database server so that:

#Centralized control.

#Change stored procedures in the server when enterprise rules change, without modifying any applications. Enterprise rules are characterized by frequent changes, and if you put the algorithms that embody them into applications, the amount of work required to modify the application (modify, publish, and install the application) is very large when the enterprise rules change. If you put the operations that embody the enterprise rules into stored procedures, then when the enterprise rules change, you can just modify the stored procedures without any changes to the application.

Php operation method

mysql_query("call stored procedure name")

trigger

A trigger is a named database object associated with a table that is activated when a specific event occurs on the table. For example, an event occurs when we insert a row into a table or trigger an event when we delete a record.

Grammar:

CREATE TRIGGER trigger_Name trigger_time trigger_event

ON tbl_Name FOR EACHROW trigger_stmt

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger is activating its

statement.

trigger_event indicates the type of statement that activates the trigger. trigger_event can be one of the following values:

INSERT: Activates triggers when a new row is inserted into a table, for example, through INSERT, LOADDATA, and REPLACE statements;

UPDATE: Activates a trigger when a row is changed, for example, via an UPDATE statement;

Delete: Activates a trigger when a row is deleted from a table, for example, through the Delete and REPLACE statements.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.