SQL summary stored procedure

Concept

Stored Procedure: One or more SQL statements that have been precompiled into an executable procedure.

Create stored procedure syntax

CREATE proc | procedure procedure_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements
go

Comparison of stored procedures and SQL statements

Advantages:

1. Improve performance
SQL statements are analyzed and compiled when creating procedures. Stored procedures are precompiled. When a stored procedure is run for the first time, the query optimizer analyzes and optimizes it, and gives a storage plan that is eventually stored in the system table. This way, this overhead can be saved when executing the procedure.
2. Reduce network overhead
When calling a stored procedure, you only need to provide the stored procedure name and necessary parameter information, which can reduce network traffic.
3. Facilitate code transplantation
Database professionals can modify the stored procedure at any time, but it will have no impact on the application source code, thus greatly improving the portability of the program.
4. Stronger security
1) System administrators can restrict permissions on a certain stored process being executed to avoid unauthorized users from accessing data
2) When calling a process through the network, only the execution process is called is visible. Therefore, malicious users cannot see table and database object names, embed their own Transact-SQL statements, or search for critical data.
3) Using procedure parameters helps avoid SQL injection attacks. Because parameter inputs are treated as literal values ​​rather than executable code, it is more difficult for an attacker to insert commands into Transact-SQL statements within the procedure and compromise security.
4) The process can be encrypted, which helps to obfuscate the source code.

Disadvantages:

1. Stored procedures require specialized database developers to maintain, but the actual situation is that program developers are often part-time workers

2. Design logic changes and modifying stored procedures are not as flexible as SQL

Why in practice? In applications, stored procedures are relatively rarely used?

In normal project development, stored procedures are relatively rarely used. Why is this?
The reasons are as follows:
1) There are no specific database developers, ordinary programmers perform database operations part-time
2) Programmers often only need to operate the program to complete data access, and there is no need to develop on the database
3) Project Requirements change frequently, and it is more convenient to modify SQL statements, especially when logic changes are involved.

How to choose between stored procedures and SQL statements?

Based on practical application experience, the following suggestions are given:

1. In some projects with high efficiency or high standardization requirements, it is recommended to use stored procedures
2. For general projects, it is recommended to use parameterized command methods, which are stored procedures and SQL statements are a compromise method
3. For some algorithms that have relatively high requirements and involve multiple pieces of data logic, it is recommended to use stored procedures

Specific applications of stored procedures

1. Basic query

1. Create without Stored procedure with parameters

Example: Query the total number of students

--查询存储过程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

Execution:

EXEC PROC_SELECT_STUDENTS_COUNT

2. Stored procedure with parameters

--查询存储过程，根据城市查询总数
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

Execution statement:

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

3. With wildcards

Wildcards, when assigning parameter values, Add the corresponding wildcard

--3、查询姓氏为李的学生信息，含通配符
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默认值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

Execution:

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

4. With output parameters

--根据姓名查询的学生信息，返回学生的城市及年龄
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

Execution:

--执行
declare @name nvarchar(50),
        @city nvarchar(20),
        @age int;
set @name = N'李明';
set @age = 20;
exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;
select @city, @age;

2. Use stored procedures to add, delete and modify

1. Add

Add student information

--1、存储过程：新增学生信息
IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE procedure PROC_INSERT_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)
GO

Execution:

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

2. Modify

Update student information based on student ID

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE procedure PROC_UPDATE_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@id
GO

Execution:

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

3. Delete

Delete a student record based on ID

--3、存储过程：删除学生信息
IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

Execution:

EXEC PROC_DELETE_STUDENT_BY_ID 1001

Three , Stored procedure to implement paging query

1. Use row_number function for paging

--分页查询
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp 
    WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndex
GO

Execution:

EXEC PROC_SELECT_BY_PAGE 1,10

2. Use traditional top paging

--使用TOP分页
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    SELECT TOP(@pageSize) * FROM Students 
    WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    
GO

Execution:

EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2

4. Other functions:

1. Stored procedure , recompile every time it is executed

--1、存储过程，重复编译
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE
with recompile --重复编译
AS 
    SELECT * FROM Students
GO

2. Encrypt the stored procedure

After encryption, the source script cannot be viewed and modified

--2、查询存储过程，进行加密
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION
with encryption --加密
AS 
    SELECT * FROM Students
GO

Execution:

EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION

Effect, the script cannot be viewed or exported to create the script