Stored Procedures and triggers

Stored Procedures and triggers

Stored procedures are a set of pre-compiled SQL statements stored on the server (BinaryCode). It is a method for encapsulating repeated task operations. It supports user-provided variables and has powerful programming functions. The stored procedure is very similar to the BAT file in the DOS system. In terms of performance, because stored procedures run on servers and the servers are generally powerful, the execution time of stored procedures is much shorter than that of workstations.
Advantages of stored procedures:
A. provides a security mechanism. If a user is granted the permission to execute a stored procedure, even if the user does not have the permission to access the table or view referenced by the stored procedure, you can also execute the stored procedure. Therefore, you can create a stored procedure to complete all operations such as adding and deleting, and control the access to information in the above operations through programming. We will demonstrate this in VB or Java later.
B. Because the execution plan of the stored procedure is deployed in the cache after the first execution, you only need to call the compiled binary code of the stored procedure from the high-speed buffer memory for execution, which accelerates the execution of the stored procedure and improves the system performance.
C. Reduce the time required for network transmission. This is a very important reason for using stored procedures. If there are one thousand SQL statements that are transmitted one by one over the network between the client and the server, this transmission takes a very long time, even the most patient people in the world cannot bear it. However, if the command of these one thousand SQL statements is written as a complex Stored Procedure command, the time required for network transmission between the client and the server will be greatly reduced.
There are five types of stored procedures in SQL Server: system stored procedures, local stored procedures, temporary stored procedures, remote stored procedures, and extended stored procedures.

1. A system stored procedure is a system-provided stored procedure used as a command to execute various operations. They are stored in the master database with the prefix SP _ or XP _. The stored procedure of the system can be executed in any database. They can be used to find their respective roles.
For example. For example, sp_dropserver deletes a server from the remote server or linked server list.

1. Local Stored Procedure refers to the stored procedure created in each user's own database. This storage process is mainly used in applications.Program.

2. A temporary stored procedure is a local stored procedure. If the name of the local stored procedure is preceded by '#', the stored procedure is called a temporary stored procedure, this stored procedure can only be used in one user session (one user connection, that is, in a query analyzer). if the name of the local stored procedure is preceded '##', this stored procedure is called a global temporary stored procedure, which can be used in all user sessions.
The local temporary process can only be used by the connection that creates the process. The global temporary process can be used by all connections. The partial temporary process is automatically removed when the current session ends. As long as the creator is not disabled, the global temporary process exists. Otherwise, the global Stored Procedure disappears.
Global temporary stored procedures can be executed for any connection. The global temporary storage process does not exist until the connection used by the user who created the process is closed and the current execution version of the process used by all other connections is completed. Once the connection used to create this process is closed, the global temporary storage process cannot be started. Only connections that have started to execute the stored procedure can be used to complete the operation of the stored procedure ).

Temporary stored procedures are automatically created in the tempdb database. However, to write a temporary stored procedure in the database to be accessed, you can access the temporary stored procedure in any database. Temporary stored procedures cannot be deleted manually (neither Code nor menu operations can be performed)
Partial temporary process:
Use mybole
Go
Create procedure # myproc
As
Select * From GST
Go
-- Go must be added here; otherwise, the following statement exec # myproc is compiled into a stored procedure.
Exec # myproc

Global temporary process-the global temporary process exists in all connected tempdb.
Use mybole
Go
Create procedure # myproc
As
Select * From GST
Go
Exec ## myproc

 

3. A remote Stored Procedure refers to a stored procedure called from a remote server or a stored procedure called from a client connected to another server. Remote stored procedures are stored on non-local servers.

4. extended storage process:
The dynamic link library DLL executed outside the SQL server environment is called the extended storage process, and its prefix is XP _. Although these dynamic link libraries are outside the SQL server environment, they can be loaded into the SQL Server System and executed as stored procedures.
Example of extended stored procedure-execute operating system commands
Exec xp_cmdshell 'mkdir Hi' -- created in system32

Exec xp_cmdshell 'dir D: \ *. EXE'

Declare @ cmd sysname, @ var sysname
Set @ Var = 'dir/P'
Set @ cmd = 'echo '+ @ var +'> dir_out.txt'
Exec master .. xp_mongoshell @ cmd

Exec xp_cmdshell "net send 127.0.0.1 hello"

5. the execution process of a stored procedure includes creating a stored procedure and executing a stored procedure. When a stored procedure is executed for the first time, the execution plan of the stored procedure is placed in the high-speed buffer area of the process. The high-speed buffer area is a memory buffer area, SQL Server uses this buffer to store compiled query plans for execution of stored procedures.
After a stored procedure is created, the system checks whether the statements are correct. After checking the syntax, the system stores the stored procedure name in the system table sysobjects of the current database, and stores the Stored Procedure text in the system table syscomment of the current database.
The execution process of the stored procedure:
Syntax analysis
|
Optimization
|
Compile
|
Run
After the first execution of the stored procedure, the execution speed is faster than the first execution speed when the stored procedure is re-executed. This is the query rule that has been optimized in the high-speed buffer storage area used by the system.

6. There are three methods to create a stored procedure:
A. Use the create procedure statement in the query analyzer.
B. Use the Stored Procedure wizard
C. Create a stored procedure directly in the stored procedure of the SQL Server database

Here we will explain how to use create procedure to create a stored procedure.

Exec sp_stored_procedures: List of stored procedures in the current database
Exec sp_help: display the parameter list and its data type
Exec sp_helptext myproc display definition text of Stored Procedure

The encrypted stored procedure can only be executed. The Stored Procedure text cannot be viewed and the intellectual property rights are protected.
Create procedure my1 with encryption
As
Select * From GST where Maid = 1

7. Execute the Stored Procedure
The stored procedure can be executed only when the execution permission of the stored procedure is exceeded. There are two ways to execute a stored procedure: Execute the stored procedure directly; execute the stored procedure in the insert statement, that is, load the data returned by the SELECT statement in the stored procedure to the table.

Create proc my2
As
Select * From infobak
Go
Exec my2
Go
Insert into infobak exec my2 -- The infobak table cannot have an ID field because it cannot be edited.

8. Stored Procedures with parameters: input and output parameters are supported.
@ Parameter date_type [= default]
The parameter information of the stored procedure is stored in the system table syscolumns.

Use input parameters
If object_id ('my3') is not null
Drop proc my3
Go
Create proc my3
(
@ ID integer = NULL,
@ Username varchar (8) = 'A'
)
As
If @ ID is null
Begin
Print 'Please input id'
Return -- if return exists, the following SELECT statement is not executed; otherwise, the statement is executed.
End

Select * from info where id = @ ID and username = @ username

SQL statements are case insensitive.
If you want to omit a parameter, you must enter null, such as exec my3 1, null, 'A'
If the order of parameters to be specified is different from that in the stored procedure definition, the parameters must be passed using the parameter name.

Specify input parameters based on parameter names
Exec my3 @ ID = 2, @ username = '11'
Specify input parameters based on the parameter location
Exec my3 1, 'A'

Use the return value of the output parameter. Note that the output parameter type can be any data type except the text and image types.
Example 1: stored procedure with multiplication function
If object_id ('my4') is not null
Drop proc my4
Go
Create proc my4
(
@ M1 smallint,
@ M2 smallint,
@ Result smallint output
)
As
Set @ result = @ m1 * @ m2

Declare @ answer smallint
Exec my4 12,22, @ answer output
Print 'This result is: '+ STR (@ answer)

Example 2: a self-increasing ID number is expected after data is inserted.
If object_id ('my5') is not null
Drop proc my5
Go
Create proc my5
(
@ ID int output,
@ Username varchar (50), -- specify the size for this type; otherwise, the default value is 1 byte.
@ Gender varchar (50 ),
@ Password varchar (50 ),
@ Address varchar (50)
)
As
Select @ ID = max (ID) from Info
If @ ID = NULL
Set @ ID = 1
Else
Set @ ID = @ ID + 1
Insert into Info (username, gender, password, address) values (@ username, @ gender, @ password, @ address)

Declare @ IID int -- can be defined as @ ID
Exec my5 @ IID output, 'AB', 'cd', 'ef, 'gh'
Print @ IID

Select * from Info

9. Modify the Stored Procedure
If object_id ('my6') is not null
Drop proc my6
Go
Create proc my6
As
Begin
Select * from Info
End
Select * from Info
Note that the begin and end statements are not the start and end of the stored procedure command, and the following statements are also in the Stored Procedure command. The result is that two record sets are returned. Begin and end must contain statement blocks for a and while loops. B. The elements of the case function must contain statement blocks. C, if, or else clauses must contain statement blocks. Like a pair of braces. Therefore, the use of begin and end in the above stored procedure has no significance.

Alter procedure my6
As
Select * from info where id = 6

Note that the options used in the create procedure Statement (some SQL server options) must also be used in the alter procedure statement.

SQL notes:
Single line comment --
Multi-line comment/**/, note that multi-line comment cannot be processed across one batch
Incorrect comment
/* If object_id ('my6') is not null
Drop proc my6
Go
Create proc my6
As
Select * from Info */

Correct comment. The entire comment must be included in a batch
/* If object_id ('my6') is not null
Drop proc my6
Go */
/* Create proc my6
As
Select * from Info */

A trigger can be triggered to complete various simple or complex tasks as long as certain conditions are met.
A trigger is a special type of stored procedure. When an operation affects the data protected by the trigger, the trigger is automatically triggered. A trigger is closely related to a table. A specific trigger is a trigger of a table. When one or more of the following data modification operations are used to modify data in the specified table, the trigger will automatically trigger the execution: Update, insert, or delete. Triggers are executed after the operation is valid, so they represent the "Last Action" in the modification ". If a trigger does not allow a user to perform an operation, the SQL server system rejects data update and returns an error message to the applications that process the transaction.
The difference between a trigger and a general stored procedure is that the latter can be directly called and executed by users, and the former cannot be called and executed directly.
A trigger is inseparable from a table. A trigger cannot leave the table and can protect data in the table. Generally, there are three basic types of data operations in a table: insert, modify, and delete. Therefore, there are three types of triggers: insert, update, and delete. When data is inserted into a table, if the table has an insert trigger, the insert trigger is triggered for execution. Although the trigger has only three types, a table can have many triggers. For example, a table can have five insert-type triggers.
The trigger is executed after the operation, and the constraint is applied before the operation occurs.
Just like using CREATE procedure to create a stored procedure, we use create trigger to create a trigger.
Syntax:
Create trigger [owner_name]. trigger_name
On [owner_name]. table_name
For (insert, update, delete)
As
Trigger code

Use a trigger with a reminder message
If object_id ('info _ instri1 ') is not null
Drop trigger info_instri1
Go
Create trigger info_instri1 on info for insert
As
Print 'inserted successfully'

Insert into Info (username, gender, password, address) values ('zhang san', 'mal', '123456', '123456 ')

Use a trigger to prevent insertion
If object_id ('info _ instri2 ') is not null
Drop trigger info_instri2
Go
Create trigger info_instri2 on info for insert
As
If exists (select * from info where Password = '000000 ')
Begin
Rollback transaction -- return is useless here, because return only returns the transaction, but does not recall the previous insert statement.
Print 'data insertion failed because the password is set to 123'
End

Insert into Info (username, gender, password, address) values ('zhang san', 'mal', '123456', '123456 ')

There are two insert triggers, so these two triggers will be executed when data is inserted.

Use a trigger with a reminder email
When the titles table is changed, the following example sends an email to the specified person (Marym ).

Use pubs
If exists (Select name from sysobjects
Where name = 'reminder' and type = 'tr ')
Drop trigger reminder
Go
Create trigger reminder
On titles
For insert, update, delete
As
Exec master .. xp_sendmail 'marym ',
'Don't forget to print a report for the distributors .'
Go

Modify a trigger
Alter trigger info_instri1 on info for insert
As
If exists (select * from info where Password = '000000 ')
Begin
Rollback transaction
Print 'data insertion failed because the password is set to 123'
End
Is to change the original create to alter

Global variables: We used the declare statement to create variables (local variables), select to change variables, and replace variables with original text values or reference columns in the expression. A special variable set is maintained by the database server. These variables are collectively referred to as global variables. In some cases, you can set these variables, but you cannot create them. The values stored in global variables are available to all users. They are different from common variables because they have two @ names before them @. Note: You can create a variable starting with two @ symbols, but cannot make it a global variable. Instead, you can only name the local variable in a confusing way. For example, @ rowcount and @ version
Example print @ version
Between global variables, some are specific values of each connection, and some are the same values of each connection. For example, @ error contains the return code of the latest statement executed by the current user.

If object_id ('info _ instri2 ') is not null
Drop trigger info_instri2
Go
Create trigger info_instri2 on info for insert
As
Select * from info -- the trigger is recommended not to return the result set.
Print 'currently '+ STR (@ rowcount) + 'row'

 

 

Source code of info and infobak tables

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [info] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [info]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [infobak] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [infobak]
Go

Create Table [DBO]. [info] (
[ID] [int] identity (1, 1) not null,
[Username] [varchar] (50) Collate chinese_prc_ci_as null,
[Gender] [varchar] (50) Collate chinese_prc_ci_as null,
[Password] [varchar] (50) Collate chinese_prc_ci_as null,
[Address] [varchar] (50) Collate chinese_prc_ci_as null
) On [primary]
Go

Create Table [DBO]. [infobak] (
[Username] [varchar] (50) Collate chinese_prc_ci_as null,
[Gender] [varchar] (50) Collate chinese_prc_ci_as null,
[Password] [varchar] (50) Collate chinese_prc_ci_as null,
[Address] [varchar] (50) Collate chinese_prc_ci_as null
) On [primary]
Go

Note that some website service providers such as www.net.cn still use SQL Server7.0. Therefore, if you use 2000 to directly write the script when writing the script, an error will occur when running it at 7.0, you must specify 7.0 compatibility in the set format.

Legacy problems:
Case statement
Count the number of people who consult each course in each quarter
Select transourcecode,
Sum (case when datepart (mm, zxdate) in (1, 2, 3) then 1 else 0 end) as first quarter,
Sum (case when datepart (mm, zxdate) in (4,5, 6) then 1 else 0 end) as the second quarter,
Sum (case when datepart (mm, zxdate) in (7, 8, 9) then 1 else 0 end) as Quarter 3,
Sum (case when datepart (mm, zxdate) in (, 12) then 1 else 0 end) as Fourth Quarter
From students_transubject
Group by transourcecode

The following example shows the full name of the state in which each author belongs in the query result set:
Use pubs
Select au_fname, au_lname,
Case state
When 'CA' then 'california'
When 'ks 'then 'Kansas'
When 'tn 'then' Tennessee'
When' or 'then' Oregon'
When 'mi 'then' Michigan'
When 'in' then 'Indiana'
When 'md' then 'maryland'
When 'ut' then 'utah'
End as statename
From pubs. DBO. Authors
Order by au_lname

The case statement is similar to the standard if statement. Calculate the condition list and return one of multiple possible result expressions.

While statement
Declare @ I int
Select @ I = 0
While @ I <5
Begin
 
If @ I = 3
Begin
Break;
-- Select @ I = @ I + 1
-- Continue -- jump out of this loop and execute the next loop.
End
Print @ I
Select @ I = @ I + 1
End

Use and set variables
Declare @ username varchar (25)
Select @ username = 'A' -- use the SELECT statement to set variables.
Select @ username = username from info where id = 25
Print @ username + convert (varchar (100), @ rowcount) -- The print statement can only print values of the char or varchar type. Implicit type conversion is not provided, which requires manual conversion.

Transactions: Based on System settings, transactions can be divided into two types: transactions provided by the system and user-defined transactions. A transaction provided by the system means that a statement is a transaction when executing certain statements. All SQL statements in SQL Server are a transaction such
Update titles set price = price * 100. either modify all rows in the titles table or do not modify any rows.
In addition
Update account set price = Price + 100 Where id = 3
Update account set price = Price-100 Where id = 2
These two statements also contain transactions, and neither of them is executed.
There are three transaction processing statements:
Begin transaction starts a work unit
Commit transaction to complete a work unit
Roolback transaction roll back a unit of work

User-Defined transactions
Begin transaction
Update info set username = ' 'Where id = 6
Insert into Info (username, password, gender, address) values ('zhang man', '123', '123', '123 ')
Commit transaction

Begin transaction
Update info set username = 'zhangxue' where id = 6
Insert into Info (username, password, gender, address) values ('zhang ', '123', '123', '123 ')
Rollback transaction
The query analyzer itself has a built-in transaction. It may not have a built-in transaction in other database client tools, so we need to manually define the transaction.
/* Hide begin transaction */
Update account set price = Price + 100 Where id = 3
Update account set price = Price-100 Where id = 2
/* Hide commit transaction */

Begin tran
Save Tran I1
Update info set username1 = 'xxx' where username = 'A'
Save Tran I2
Update info set username = 'yyy' where username = 'E'
Save Tran I3
Update info set username = 'zzz' where username = 'I'
If (@ error <> 0)
Begin
Raiserror 5001 'have a error founded! '// This does not work in the query analyzer because the query analyzer has already handled Errors for you.
Rollback Tran I2 -- What is above I2 is not revoked, but under it.
End
Else
Commit tran

It is best not to nest transactions in transactions, which will occupy a large amount of system resources.

 

Use of custom functions
System built-in functions such as rtrim (), count ()
If object_id ('stufun ') is not null
Drop function stufun
Go
Create Function stufun (@ username varchar (10) returns varchar (10)
Begin
Declare @ password char (6)
Select top 1 @ Password = password from info where username like rtrim (@ username) + '%'
Return @ Password
End

Select * from Info

Declare @ password varchar (10)
Set @ Password = DBO. stufun ('zhang ')
Print @ Password

The return type is a table.
If object_id ('stufun1') is not null
Drop function stufun1
Go
Create Function stufun1 (@ username varchar (10) returns table
As -- the preceding two statements are not allowed.
Return (select password from info where username like rtrim (@ username) + '% ')

Select * From DBO. stufun1 ('zhang ')

Cursor
Statements that execute select statements or other modification records can only process one whole, but cannot separately process some of them, if you want to process a single record separately, write the corresponding SQL statement of each record to modify the corresponding record. The cursor is such a method that can process part of the result set.
The cursor can be viewed as a pointer to any position in the result set.
SQL Server2000 provides two cursor application interface methods:
TransAct SQL cursor: The cursor implemented by the SQL Server server. The control and management operations of the cursor are carried out to the server through scripts, stored procedures, or triggers. This is the cursor we will introduce here.
API (Database Application Programming Interface) cursor: by calling the API cursor function, the cursor function is implemented through ado, oledb, and ODBC. That is, the cursor we control in the program
The operation cursor has five steps:
A. Use the declare statement to create a cursor and define the cursor type and attributes.
B. Call the open statement to open and fill the cursor
C. Execute the fetch statement to extract several rows of data in the result set. The transact SQL cursor does not support multi-row read operations.
D. Use the update or delete statement to operate the cursor at the current position as needed.
E. Use the close and deallocated statements to close the release cursor. Use the former. Although you can no longer process the rows in the cursor, the cursor is still declared, while the latter not only closes the cursor, the cursor Declaration is also canceled.

There are four types of API cursors: single-entry cursors, static cursors, dynamic cursors, and keyset-driven cursors.
For details, see SQL Server help (cursor: dynamic, etc)

When a cursor is opened, the cursor is located in the first row of the result set.
Note: If you modify the cursor of this connection, it does not work because it is not closed and the cursor is stored in a connection. Therefore, you can create a new query window.

Use mybole
Declare info_cursor cursor
For select * from Info
Open info_cursor
Fetch next from info_cursor
Update info SET Password = '1' where current of info_cursor -- Update with Cursor
Delete from info where current of info_cursor -- delete with Cursor
While (@ fetch_status = 0)
-- Fetch info_cursor
Fetch next from info_cursor
-- Close info_cursor
Deallocate info_cursor