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

Stored procedures can speed up the execution of queries, improve the speed of accessing data, help to achieve modular programming, preserve consistency, and improve security. Triggers are stored procedures that are automatically executed when inserting, updating, and deleting tables, and are usually used to enforce business rules.

I. stored procedure

1. Why stored procedures are needed

It is not safe to send SQL code from the client to the server over the network and execute it, providing * * with the opportunity to steal data, as shown in the following figure, a simple SQL injection process

As can be seen from the above figure, the execution process of the application is not secure, and there are mainly the following aspects:

(1) data is not secure, and SQL code is transmitted over the network, which is easy to be intercepted by unauthorized users.

(2) every time the SQL code is submitted, it must be syntactically compiled and executed, which affects the performance of the application.

(3) the network traffic is large, and the repeatedly executed SQL code is transmitted many times on the network, which affects the network transmission volume.

two。 What is a stored procedure?

Stored procedure is a precompiled collection of SQL statements and control statements, saved in the database, can be called and executed by applications, and allows users to declare variables, logical control statements and other powerful programming functions. Contains logical control statements and data operation statements that can receive parameters, output parameters, return single or multiple result values, and return values

Advantages of using stored procedures:

(1) Modular programming, which only needs to be created once, and the stored procedure can be called any time later.

(2) Fast execution speed and high efficiency

(3) reduce network traffic

(4) have good security.

Stored procedures are divided into system stored procedures and user-defined stored procedures.

3. System stored procedure

Is a set of precompiled T-SQL statements that provide a mechanism for managing databases and updating tables, and act as a shortcut to retrieve information from system tables

(1) Common system stored procedures

The name of the system stored procedure begins with "sp_" and is stored in the Resource database

The syntax for using stored procedures is as follows:

Exec stored procedure name [parameter value]

For example: execute the following T-SQL statement

(2) commonly used extended stored procedures

Extended stored procedures are one of the various system stored procedures provided by SQL Server that allow external stored procedures to be created in other programming languages (such as C #), usually starting with "xp_" and existing separately in the form of DDL

A commonly used extended stored procedure is xp_cmdshell, which can perform some operations under the DOS command, such as creating folders and listing folders. The syntax is as follows:

Exec xp_cmdshell DOS command [no_output]

Where no_output is an optional parameter, which sets whether the return information is output after the DOS command is executed.

For example: create a folder bank under disk C and view the files

4. User-defined stored procedures

In addition to using the system's stored procedures, you can also create your own stored procedures. You can use SSMS or T-SQL statements to create stored procedures

(1) use SSMS to create stored procedures

(2) use T-SQL statements to create stored procedures

The syntax for creating a stored procedure is as follows:

The syntax for deleting a stored procedure is as follows:

Drop proc stored procedure name

Case study: the following two tables are used to write stored procedures to achieve the average score of network management major.

Trigger

Trigger is a special stored procedure that is called automatically when the data in the table is updated in response to INSERT, UPDATE, DELETE statements.

1. What is a trigger?

Triggers are stored procedures that are automatically executed when inserting, updating, and deleting tables. They are usually used to enforce business rules and can define more complex constraints than using CHECK constraints. Triggers are mainly executed by event triggers, while stored procedures can be used directly through the name of the stored procedure.

two。 Classification of triggers

INSERT trigger: triggered when data is inserted into a table

UPDATE trigger: triggered when one or more columns in a table are updated

DELETE trigger: triggered when a record in the table is deleted

3. Deleted table and inserted table

Each trigger has two special logical tables: delete tables and insert tables. It is managed by the system and stored in memory rather than in the database, so users are not allowed to modify it directly. They only temporarily store changes to the rows of data in the table, and when the trigger is done, they are also deleted.

4. The function of trigger

The main function is to achieve complex referential integrity and data consistency that cannot be guaranteed by primary and foreign keys, in addition to the following functions

(1) strengthening constraints: implementing more complex constraints than CHECK constraints

(2) tracking changes: detect operations in the database so that unauthorized updates and changes are not allowed

(3) cascade operation: detect the operations in the database and automatically cascade the contents of the whole database.

5. Create trigger

Triggers can be created using SSMS or T-SQL statements

(1) use SSMS to create triggers

(2) use T-SQL statement to create trigger

The syntax for creating a trigger using the T-SQL statement is as follows:

Create trigger trigger name / / trigger name created on table name / / the table or view name on which the trigger is executed [with encryption] / / optional, prevent triggers from publishing the for {[delete,insert,update]} / / keyword as part of SQL Server replication, specify at least one, if more than one As sql statements are separated by commas

Case: create a trigger that prompts a message when someone changes information and blocks the operation

If you need to modify the trigger, you can do this by modifying the T-SQL statement in the pop-up window.

Considerations when creating triggers

(1) create trigger must be the first statement in the batch and can only be applied to one table

(2) triggers can only be created in the current database, but can refer to external objects in the current database.

(3) in the same create trigger statement, you can define the same trigger operation for multiple user operations (such as DELETE).

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.