SQL stored procedure

SQL stored procedure

I. Concepts, advantages and syntax of SQL stored procedures Before learning the program process, let's first understand what a stored procedure is? What advantages does stored procedure have? 　　Definition: Store commonly used or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command. At this point, someone may ask: is the stored procedure just a bunch of SQL statements? Why does Microsoft add this technology? What is the difference between a stored procedure and a general SQL statement? 　　Advantages of stored procedures: 1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution. 2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. 3. The stored procedure can be reused to reduce the workload of database developers. 4. High security. You can set that only one user has the right to use the specified stored procedure. 　　Types of stored procedures: 1. system stored procedure: starts with SP _. It is used to set the system, obtain information, and manage the system, For example, sp_help is used to obtain information about the specified object. 2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system. Reference content is as follows: Exec master .. xp_mongoshell 'Ping 10.8.16.1' 3. User-Defined stored procedures, which we refer to as stored procedures Common formats Reference content is as follows: Create procedure procedue_name [@ Parameter data_type] [Output] [With] {recompile | encryption} As SQL _statement 　　Explanation: Output: indicates that this parameter can be returned. With {recompile | encryption} Recompile: Indicates re-compiling every time this stored procedure is executed. Encryption: the content of the created stored procedure is encrypted. 　　Ii. SQL stored procedure learning: creating a stored procedure The table book content is as follows: Title price 001 C language entry $30 002 PowerBuilder report development$52 　　Example 1: query the stored procedure of the table book content CreateProcQuery_book As Select*FromBook Go ExecQuery_book 　　Example 2: Add a record to the table book and query the total amount of all books in the table CreateProcInsert_book @ Param1Char (10), @ param2Varchar (20), @ param3Money, @ param4MoneyOutput WithEncryption--------- Encryption As InsertBook (number, title, price)Values (@ param1, @ param2, @ param3) Select@ Param4 = sum (price)FromBook Go 　　Example: Declare@ Total_priceMoney ExecInsert_book'003 ', 'delphiControl Development Guide ', $100, @ total_price Print'Total amount is '+ convert (varchar, @ total_price) Go Three types of Stored Procedure return values: 1. Return an integer with return 2. Return parameters in output format 3. recordset Differences between return values: Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch. 　　Example 3: There are two tables: product and order. The table content is as follows: Reference content is as follows: 　　Product Product NO. Product Name customer order quantity 001 pen 30 002 brush 50 003Pencil100 　　Order Product NO. Customer name customer deposit 001 Nanshan District $30 002 Luohu district $50 003 Baoan District $4 Connect two tables into a temporary table by serial number. This table only contains serial numbers, product names, Customer names, deposits, and total amount, Total amount = Deposit * Number of orders. Temporary tables are stored in the stored procedure. The Code is as follows: CreateProcTemp_sale As SelectA. Product NO., A. Product Name, B. Customer name, B. customer deposit, A. Number of customer orders *B. Customer DepositsAs total amount Into# TemptableFromProductAInnerJoinOrderBOnA. Product NO. = B. Product NO. If@ Error = 0 Print'Good' Else Print'Fail' Go 　　Iii. SQL stored procedure learning: Calling stored procedures Several Methods for calling stored procedures with Parameters 1) this is also the simplest method. Two input parameters have no return values and are used for insert, update, and delete operations. Reference content is as follows: Conn. Execute "procname varvalue1, varvalue2" 2) If you want to return the recordset: Reference content is as follows: Set rs = server. Createobject ("ADODB. recordset ") Rs. Open "Exec procname varvalue1, varvalue2", Conn 3) Neither of the preceding methods can return values (except for recordset). To obtain the return value, use the command method. First, there are two return values. One is to directly return a value in the stored procedure, just like the return values of C and VB functions; the other is to return multiple values, the variable names that store these values must be specified in the call parameters first. 　　4. Special stored procedure-triggers 　　1. Concepts and functions of triggers A trigger is a special type of stored procedure, which is different from the stored procedure we described earlier. A trigger is executed when an event is triggered, and a stored procedure can be directly called by the stored procedure name. When performing operations such as update, insert, and delete on a table, SQL Server automatically runs the SQL statement defined by the trigger, this ensures that data processing must comply with the rules defined by these SQL statements. The trigger is mainly used to achieve complex integrity and data consistency that cannot be guaranteed by the primary key and foreign key. In addition, the trigger has many other functions: (1) Enforce Restriction) Triggers implement more complex constraints than check statements. (2) tracking change auditing changes Trigger can detect operations in the database, so that you cannot specify updates or changes in the database without permission. (3) cascaded operation ). The trigger can detect operations in the database and automatically cascade operations to affect the entire database. For example, a table trigger contains data operations (such as deletion, update, and insertion) on another table, which triggers the table trigger. (4) Stored Procedure invocation ). In response to the database update trigger, the sender can call one or more stored procedures, or even operate outside the DBMS (Database Management System) itself by calling external processes. It can be seen that triggers can solve advanced business rules or complex behavior restrictions and achieve custom records. For example, a trigger can identify the differences between the statuses of a table before and after data modification, and perform some processing based on the differences. In addition, multiple triggers of the same type (insert, update, and delete) of a table can process the same data operation in a variety of ways. In general, the trigger performance is usually relatively low. When a trigger is run, the system spends most of its processing time referring to other tables because these tables are neither in memory nor on Database devices, deleting and inserting tables are always in memory. It can be seen that the position of other tables referenced by the trigger determines the duration of the operation. 　　2. Types of triggers SQL Server 2000 supports two types of triggers: After trigger and instead trigger. The After trigger is the one introduced before SQL Server 2000. This type of trigger requires that the trigger be triggered only after an operation (insert update delete) is executed and can only be defined on the table. You can define multiple triggers for the same operation on tables. For an After trigger, you can define which trigger is first triggered and which one is last triggered. The system process sp_settriggerorder is usually used to complete this task. The instead of trigger does not execute the defined operations (insert, update, delete), but only executes the trigger itself. An instead of trigger can be defined on a table or in a view, but only one instead of trigger can be defined for the same operation.