SQL Server基础之<存储过程>

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
密钥管理服务KMS,1000个密钥,100个凭据,1个月
简介: 原文:SQL Server基础之  简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
原文: SQL Server基础之<存储过程>

  简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。

:存储过程概述

SQL Server中的存储过程是使用T_SQL编写的代码段。它的目的在于能够方便的从系统表中查询信息,或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下,一些代码会被开发者重复编写多次,如果每次都编写相同功能的代码,不但繁琐,而且容易出错,而且由于SQL Server逐条的执行语句会降低系统的运行效率。

 简而言之,存储过程就是SQL Server为了实现特定任务,而将一些需要多次调用的固定操作语句编写成程序段,这些程序段存储在服务器上,有数据库服务器通过程序来调用。

存储过程的优点:

  1. 存储过程加快系统运行速度,存储过程只在创建时编译,以后每次执行时不需要重新编译。
  2. 存储过程可以封装复杂的数据库操作,简化操作流程,例如对多个表的更新,删除等。
  3. 可实现模块化的程序设计,存储过程可以多次调用,提供统一的数据库访问接口,改进应用程序的可维护性。
  4. 存储过程可以增加代码的安全性,对于用户不能直接操作存储过程中引用的对象,SQL  Server可以设定用户对指定存储过程的执行权限。
  5. 存储过程可以降低网络流量,存储过程代码直接存储于数据库中,在客户端与服务器的通信过程中,不会产生大量的T_SQL代码流量。

存储过程的缺点:

  1. 数据库移植不方便,存储过程依赖与数据库管理系统, SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
  2. 不支持面向对象的设计,无法采用面向对象的方式将逻辑业务进行封装,甚至形成通用的可支持服务的业务逻辑框架.
  3. 代码可读性差,不易维护。不支持集群。

二:存储过程分类

1.系统存储过程

  系统存储过程是 SQL Server系统自身提供的存储过程,可以作为命令执行各种操作。

  系统存储过程主要用来从系统表中获取信息,使用系统存储过程完成数据库服务器的管理工作,为系统管理员提供帮助,为用户查看数据库对象提供方便,系统存储过程位于数据库服务器中,并且以sp_开头,系统存储过程定义在系统定义和用户定义的数据库中,在调用时不必在存储过程前加数据库限定名。例如:sp_rename系统存储过程可以修改当前数据库中用户创建对象的名称,sp_helptext存储过程可以显示规则,默认值或视图的文本信息,SQL SERVER服务器中许多的管理工作都是通过执行系统存储过程来完成的,许多系统信息也可以通过执行系统存储过程来获得。

  系统存储过程创建并存放在与系统数据库master中,一些系统存储过程只能由系统管理员使用,而有些系统存储过程通过授权可以被其它用户所使用。

2.用户存储过程(自定义存储过程)

  自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求,在用户数据库中编写的T_SQL语句集合,自定义存储过程可以接受输入参数、向客户端返回结果和信息,返回输出参数等。创建自定义存储过程时,存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时,表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。

用户定义的存储过程分为两类:T_SQL CLR

T_SQL:存储过程是值保存的T_SQL语句集合,可以接受和返回用户提供的参数,存储过程也可能从数据库向客户端应用程序返回数据。

CLR存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程,可以接受和返回用户提供的参数,它们在.NET Framework程序集是作为类的公共静态方法实现的。

3.扩展存储过程

 扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的,可以加载到SQL SERVER实例运行的地址空间中执行,扩展存储过程可以用SQL SERVER扩展存储过程API编程,扩展存储过程以前缀"xp_"来标识,对于用户来说,扩展存储过程和普通话存储过程一样,可以用相同的方法来执行。 

三:创建存储过程

工欲善其事,必先利其器,准备数据如下:

use sample_db;
--创建测试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,'司马迁');

1.创建无参存储过程

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
    drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

2.修改存储过程

alter procedure dbo.getAllBooks 
as
select book_auth from books;

3.删除存储过程

drop procedure getAllBooks;

4.重命名存储过程

sp_rename getAllBooks,proc_get_allBooks;

5.创建带参数的存储过程

 存储过程的参数分为两种:输入参数和输出参数

输入参数:用于向存储过程传入值,类似java语言或则c中的值传递。

输出参数:用于调用存储过程后,参会结果,类似java语言的按引用传递。

值传递和引用传递区别:

  • 基本数据类型赋值属于值传递;引用类型之间赋值属于引用传递。
  • 值传递传递的是实实在在的变量值;引用传递传递的是对象的引用地址。
  • 值传递后,两个变量改变的是各自的值;引用传递后,两个引用改变的是同一个对象的状态

(1)带一个参数存储过程

if (exists (select * from sys.objects where name = 'searchBooks'))
    drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2)带2个参数存储过程

if (exists (select * from sys.objects where name = 'searchBooks1'))
    drop proc searchBooks1
go
create proc searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3)创建有返回值的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4)创建带通配符的存储过程

if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec  charBooks    '孔%','论%';

(5)加密存储过程

  with encryption子句对用户隐藏存储过程的文本.下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息.

if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6).不缓存存储过程

--with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';

(7).创建带游标参数的存储过程

if (object_id('book_cursor', 'P') is not null)
    drop proc book_cursor
go
create 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 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('book_page', 'P') is not null)
    drop proc book_page
go
create 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 book_page 'books','*','book_id','',3,1,0;

--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第18天】SQL Server 存储过程具有提高性能、增强安全性、代码复用和易于维护等优点。它可以减少编译时间和网络传输开销,通过权限控制和参数验证提升安全性,支持代码共享和复用,并且便于维护和版本管理。然而,存储过程也存在可移植性差、开发和调试复杂、版本管理问题、性能调优困难和依赖数据库服务器等缺点。使用时需根据具体需求权衡利弊。
|
28天前
|
存储 SQL 缓存
SQL Server存储过程的优缺点
【10月更文挑战第22天】存储过程具有代码复用性高、性能优化、增强数据安全性、提高可维护性和减少网络流量等优点,但也存在调试困难、移植性差、增加数据库服务器负载和版本控制复杂等缺点。
|
1月前
|
存储 SQL 数据库
Sql Server 存储过程怎么找 存储过程内容
Sql Server 存储过程怎么找 存储过程内容
31 1
|
1月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第17天】SQL Server 存储过程是预编译的 SQL 语句集,存于数据库中,可重复调用。它能提高性能、增强安全性和可维护性,但也有可移植性差、开发调试复杂及可能影响数据库性能等缺点。使用时需权衡利弊。
|
1月前
|
存储 SQL 数据库
SQL Server 临时存储过程及示例
SQL Server 临时存储过程及示例
49 3
|
1月前
|
存储 SQL 安全
|
1月前
|
存储 SQL 数据库
使用SQL创建视图和存储过程
使用SQL创建视图和存储过程
16 0
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
存储 SQL 安全
【数据库高手的秘密武器:深度解析SQL视图与存储过程的魅力——封装复杂逻辑,实现代码高复用性的终极指南】
【8月更文挑战第31天】本文通过具体代码示例介绍 SQL 视图与存储过程的创建及应用优势。视图作为虚拟表,可简化复杂查询并提升代码可维护性;存储过程则预编译 SQL 语句,支持复杂逻辑与事务处理,增强代码复用性和安全性。通过创建视图 `high_earners` 和存储过程 `get_employee_details` 及 `update_salary` 的实例,展示了二者在实际项目中的强大功能。
41 1
|
3月前
|
JSON 数据格式 Java
化繁为简的魔法:Struts 2 与 JSON 联手打造超流畅数据交换体验,让应用飞起来!
【8月更文挑战第31天】在现代 Web 开发中,JSON 成为数据交换的主流格式,以其轻量、易读和易解析的特点受到青睐。Struts 2 内置对 JSON 的支持,结合 Jackson 库可便捷实现数据传输。本文通过具体示例展示了如何在 Struts 2 中进行 JSON 数据的序列化与反序列化,并结合 AJAX 技术提升 Web 应用的响应速度和用户体验。
114 0