一、概述
1、概念
存储过程是指为了完成特定的功能由一条或多条sql语句组成的集合,经系统进行编译后存储到数据库的服务器中,用户通过指定存储过程名称与参数,调用该存储过程并且执行。在调用的过程中允许用户声明变量,设置条件,以便增强程序设计的能力。
2、优点
- 运行速度快:存储过程只在创建时进行一次编译,由查询优化器对其进行分析,优化,并给出执行计划,之后每次调用存储过程执行时不需要重新编译。
- 降低网络流量: 编辑存储过程的代码是存储到数据库中的,当用户端向服务器端发出调用存储过程的命令时,通过网络传输的只是存储过程的调用,不会有大量的代码在网络中传输,从而极大的减少了网络流量,降低了网络负载。
- 增强数据代码安全性:对存储过程执行权限的限制,非授权用户是不可以调用存储过程的,所以对存储过程所涉及的数据访问也受到了限制,存储过程即增加了代码安全性,又保证了数据安全性。
- 提高了程序的可维护性:存储过程一旦被创建在程序中可多次使用,不必每次都重写相应的t-sql代码,程序员对存储过程的修改不会影响到应用程序源代码。
- 安全:调用者只需要知道如何调用指定的存储过程即可,而不用关心存储过程的内容,避免了SQL注入。
3、缺点
- 移植性差:存储过程依赖于数据库管理系统,在存储过程中所封装的t-sql代码不能直接移植到其他数据库管理系统中。
- 不支持集群操作。
- 过多的存储过程,优化和维护比较麻烦
- 不支持面向对象的设计,无法采用面向对象的方式将逻辑业务进行封装,甚至形成通用的可支持服务的业务逻辑框架。
二、分类
1、系统存储过程
该类存储过程通常被存放到master数据库中,存储过程名称通常以“sp_”为前缀,主要是用来完成数据库服务器的管理工作,在调用时不必在存储过程前加数据库限定名。
2、扩展存储过程
通常以“xp_”为前缀标识,在sql server系统外通过执行动态链接库,即DLL文件,来实现的功能,该存储过程经常使用API接口进行编辑。在sql server常见的扩展存储过程有:
xp_enumgroups 指定WINDOWS本地组列表在WINDOWS域中定义的全局组表
xp_findnextmsg 接受输入的邮件ID号,返回输出的邮件ID号
xp_grantlogin 给用户分配对sql server2012系统的权限
xp_logevent 把用户自定义消息输入到sql server日志文件或WINDOWS系统事件查看器中
xp_loginconfig 显示sql server 2012实例运行时登陆的安全配置
3、用户自定义存储过程
所谓自定义存储过程,是指为了完成某一段特定的功能需求,在用户数据库中利用t-sql自行编辑的语句集合。如果在存储过程名称前加了“##”符号,表示创建的存储过程是临时的全局性的;如果前面的为“#”符号,表示所创建的存储过程是临时的局部的,该存储过程只能在创建它的会话中使用。以上两种存储过程创建后都存放在tempdb数据库中。用户定义的存储过程分为两类:T_SQL 和CLR
- T_SQL存储过程:指保存的T_SQL语句集合,可以接受和返回用户提供的参数,存储过程也可能从数据库向客户端应用程序返回数据。
- CLR存储过程:指引用Microsoft.NET Framework公共语言的方法存储过程,可以接受和返回用户提供的参数,它们在.NET Framework程序集是作为类的公共静态方法实现的。
三、创建存储过程
1、准备测试数据
--创建测试books表 create table books ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10) ); --插入测试数据 insert into books (book_name,book_price,book_auth) values (‘论语‘,25.6,‘孔子‘), (‘天龙八部‘,25.6,‘金庸‘), (‘雪山飞狐‘,32.7,‘金庸‘), (‘平凡的世界‘,35.8,‘路遥‘), (‘史记‘,54.8,‘司马迁‘);
2、创建无参数存储过程
--创建无参存储过程 if (exists (select * from sys.objects where name = ‘proc_getAllBooks‘)) drop proc proc_getAllBooks go create procedure proc_getAllBooks as select * from books; --调用,执行存储过程 exec proc_getAllBooks;
3、修改存储过程
alter procedure dbo.proc_getAllBooks as select book_auth from books
4、删除存储过程
drop procedure dbo.proc_getAllBooks;
5、重命名存储过程
sp_rename proc_getAllBooks,proc_get_allBooks;
四、创建带参数的存储过程
存储过程的参数分为两种:输入参数和输出参数
输入参数:用于向存储过程传入值。
输出参数:用于调用存储过程后,输出结果。
1、带一个参数存储过程
if (exists (select * from sys.objects where name = ‘proc_searchBooks‘)) drop proc proc_searchBooks go create proc proc_searchBooks(@bookID int) as --要求book_id列与输入参数相等 select * from books where book_id=@bookID;
--执行proc_searchBooks exec proc_searchBooks 1;
2、带2个参数存储过程
if (exists (select * from sys.objects where name = ‘proc_searchBooks1‘)) drop proc searchBooks1 go create proc proc_searchBooks1( @bookID int, @bookAuth varchar(20) ) as --要求book_id和book_Auth列与输入参数相等 select * from books where book_id=@bookID and book_auth=@bookAuth; exec proc_searchBooks1 1,‘金庸‘;
3、创建有返回值的存储过程
if (exists (select * from sys.objects where name = ‘proc_getBookId‘)) drop proc proc_getBookId go create proc proc_getBookId( @bookAuth varchar(20),--输入参数,无默认值 @bookId int output --输入/输出参数 无默认值 ) as select @bookId=book_id from books where book_auth=@bookAuth --执行getBookId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec proc_getBookId ‘孔子‘,@id output select @id as bookId;--as是给返回的列值起一个名字
4、创建带通配符的存储过程
if (exists (select * from sys.objects where name = ‘proc_charBooks‘)) drop proc proc_charBooks go create proc proc_charBooks( @bookAuth varchar(20)=‘%‘, @bookName varchar(20)=‘%‘ ) as select * from books where book_auth like @bookAuth and book_name like @bookName; --执行存储过程proc_charBooks exec proc_charBooks ‘孔%‘,‘论%‘;
5、加密存储过程
with encryption子句对用户隐藏存储过程的文本.下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。
if (object_id(‘proc_books_encryption‘, ‘P‘) is not null) drop proc proc_books_encryption go create proc proc_books_encryption with encryption as select * from books; --执行此过程books_encryption exec proc_books_encryption; exec sp_helptext ‘proc_books_encryption‘;--控制台会显示"对象 ‘proc_books_encryption‘ 的文本已加密。"
6、不缓存存储过程
--with recompile不缓存 if (object_id(‘proc_book_temp‘, ‘P‘) is not null) drop proc proc_book_temp go create proc proc_book_temp with recompile as select * from books; go exec proc_book_temp; exec sp_helptext ‘proc_book_temp‘;
7、创建带游标参数的存储过程
if (object_id(‘proc_book_cursor‘, ‘P‘) is not null) drop proc proc_book_cursor go create proc proc_book_cursor @bookCursor cursor varying output as set @bookCursor=cursor forward_only static for select book_id,book_name,book_auth from books open @bookCursor; go --调用book_cursor存储过程 declare @cur cursor, @bookID int, @bookName varchar(20), @bookAuth varchar(20); exec proc_book_cursor @bookCursor=@cur output; fetch next from @cur into @bookID,@bookName,@bookAuth; while(@@FETCH_STATUS=0) begin fetch next from @cur into @bookID,@bookName,@bookAuth; print ‘bookID:‘+convert(varchar,@bookID)+‘ , bookName: ‘+ @bookName +‘ ,bookAuth: ‘+@bookAuth; end close @cur --关闭游标 DEALLOCATE @cur; --释放游标
8、创建分页存储过程
if (object_id(‘proc_book_page‘, ‘P‘) is not null) drop proc proc_book_page go create proc proc_book_page( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = ‘*‘, --字段名(全部字段为*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N‘‘, --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) as begin --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--总记录数语句 SET @SqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;--查询语句 -- IF (@WhereString! = ‘‘ or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ‘ where ‘+ @WhereString; SET @SqlString =@SqlString+ ‘ where ‘+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ + ltrim(str(@EndRecord)); Exec(@SqlString) END --调用分页存储过程book_page exec proc_book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,0; -- declare @totalCount int exec proc_book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,@totalCount output; select @totalCount as totalCount;--总记录数。