存储过程的具体应用
一、基础查询
1、创建不带参数的存储过程
例子:查询学生总数
--查询存储过程 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
执行:
EXEC PROC_SELECT_STUDENTS_COUNT
2、带参数的存储过程
--查询存储过程,根据城市查询总数 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
执行语句:
EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'
3、带有通配符
通配符,在参数值赋值时,加上相应的通配符
--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
执行:
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%' EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'
4、带有输出参数
--根据姓名查询的学生信息,返回学生的城市及年龄 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
执行:
--执行 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;
二、使用存储过程进行增删改
1、新增
新增学生信息
--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
执行:
EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'
2、修改
根据学生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
执行:
EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'
3、删除
根据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
执行:
EXEC PROC_DELETE_STUDENT_BY_ID 1001
三、存储过程实现分页查询
1、使用row_number函数分页
--分页查询 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
执行:
EXEC PROC_SELECT_BY_PAGE 1,10
2、使用传统的top分页
--使用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
执行:
EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2
四、其他功能:
1、存储过程,每次执行都进行重新编译
--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、对存储过程进行加密
加密后,不能查看和修改源脚本
--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
执行:
EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION
效果,无法查看脚本或者导出创建脚本