您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
首页- 博文- sqlserver- sqlserver存储过程

sqlserver存储过程

bubuko 2022/1/25 19:06:50 sqlserver 字数 25779 阅读 969 来源 http://www.bubuko.com/infolist-5-1.html

一、概述 1、概念 存储过程是指为了完成特定的功能由一条或多条sql语句组成的集合，经系统进行编译后存储到数据库的服务器中，用户通过指定存储过程名称与参数，调用该存储过程并且执行。在调用的过程中允许用户声明变量，设置条件，以便增强程序设计的能力。 2、优点 运行速度快：存储过程只在创建时进行一次编译 ...

一、概述

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;--总记录数。

 

sqlserver存储过程

原文：https://www.cnblogs.com/qtiger/p/14784404.html


如果您也喜欢它,动动您的小指点个赞吧

除非注明，文章均由 laddyq.com 整理发布，欢迎转载。

转载请注明：
链接：http://laddyq.com
来源：laddyq.com
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

联系我

联系我

602392714

清零编程清零编程群

置顶