数据库原理与应用(SQL Server)笔记 第九章 存储过程和触发器(上)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 数据库原理与应用(SQL Server)笔记 第九章 存储过程和触发器

前言


本章内容将介绍数据库中的存储过程和触发器,并将通过定义以及使用和验证其功能。


注:以下皆通过T-SQL语句进行操作,另一种图形界面自行翻阅书籍或网上搜索。


一、存储过程


(一)存储过程的定义


简单的来说,存储过程就是一串T-SQL语句集合,通过定义后,可以使用该存储过程。


(二)存储过程的特点


它有以下特点:

1、可以快速执行。相较于批处理语句,存储过程可以更快的执行,省去了重新分析、重新编译、重新优化等步骤。

2、具有安全特性。

3、可以减少网络通信流量。

4、可-被重复调用任意次,且可以保证功能的一致性。


(三)存储过程的分类


可以分为三类,即用户存储过程、系统存储过程、扩展存储过程,这里简单介绍这三种类型:


1、用户存储过程


用户存储过程即用户自己创建的存储过程,其中包括T-SQL存储过程和CLR存储过程(CLR存储过程这里不重点讲述),T-SQL存储过程即使用T-SQL语句创建的语句集合,通过接收和返回用户输入的参数执行该存储过程。


2、系统存储过程


系统存储过程是由系统本身所定义的存储过程,可以作为命令来执行,系统存储过程一般定义在系统数据库master中,都是以前缀sp_开头的,如下图。

1667036867815.jpg


3、扩展存储过程


扩展存储过程是指允许使用编程语言来创建自己的外部例程,添加至系统后也是按照存储过程的方法执行。


(四)存储过程的创建


语句格式如下:

CREATE PROC/PROCEDURE <存储过程的名称>
(@参数的名称 类型...)
/*定义参数的类型*/
AS
SQL语句...


上面的定义参数,会在后面的带参数的存储过程的使用中详细讲解。


例1、根据以下所给信息,在数据库Sales中,创建一个存储过程proc_1:其功能是显示商品信息表中出库存量最少的5条商品信息。

1667036898627.jpg

sql语句:

USE Sales GO CREATE PROC proc_1 AS SELECT TOP 5* FROM Product ORDER BY stocks ASC

可以发现命令完成后,在数据库Sales的可编编程性中的存储过程中找到所创建的存储过程dbo.proc_1。

1667036919363.jpg

注:当我们创建一个存储过程执行后,再点击执行会无法执行报错“已存在名为…的对象”,若我们不想再通过代码来修改存储过程的内容,可以在左侧对象资源管理器中找到相应的存储过程名右键删除,此时再点执行,执行已修改的存储过程。

1667036932558.jpg


(五)存储过程的执行


通过使用EXEC或EXECUTE命令来执行所创建的存储过程。

语句格式如下:

EXEC <已创建的存储过程名称>


例2、执行例1中创建的存储过程proc_1。

sql语句:

USE Sales GO EXEC proc_1

1667036962422.jpg


(六)带参数的存储过程的使用


存储过程带参数可以在存储过程和用户之间交换数据,有两种传递参数的方式,一是按位置传递参数,即采用实参列表方式,使传递参数和定义时的参数顺序一致;二是通过参数名称来传递参数,即参数=值的方式来传递,这钟方式的每个参数的顺序可以任意排列。


1、带默认参数的存储过程

在创建存储过程时,可以为带参数的存储过程设置为默认值,默认值必须为常量或NULL。当用户调用存储过程时,如果未指定对应的实参值,则自动使用对应的默认值进行替代。


2、带输入参数的存储过程

要定义带输入参数的存储过程,就必须要在创建语句中声明一个或多个变量及其类型。

语句格式如下:

<@参数名> 类型 ... 
/*定义参数时*/
WHERE <@参数名>=名称 ... 
/*WHERE赋值参数时*/
EXEC <已创建的存储过程名称> <@参数名>/<@参数名>='输入值' ...
/*在执行存储过程时*/


例3、根据以下所给信息,在数据库Sales中,创建一个带默认参数的存储过程proc_2,其功能是:输入销售员的工号,输出指定销售员工号的销售员信息,指定默认工号为“S01”。

1667037057456.jpg

这里把创建存储过程和执行存储过程放在一起,为了方便执行,同时在执行存储过程中输入的参数采用的是按位置传递参数方式。

首先我们查询工号为“S02”的员工信息,,结果如下:

sql语句:

USE Sales GO CREATE PROC proc_2 @SaleID char(3)='S01' AS SELECT * FROM Seller WHERE SaleID=@SaleID GO EXEC proc_2 S02

1667037074676.jpg

因为事先已指定默认参数为“S01”,若我们不指定输入值,输出结果则会为工号为“S01”的员工,这里也是在执行存储过程中输入的参数采用的是按位置传递参数方式,结果如下:

sql语句:


USE Sales GO CREATE PROC proc_2 @SaleID char(3)='S01' AS SELECT * FROM Seller WHERE SaleID=@SaleID GO EXEC proc_2

1667037088797.jpg


例4、根据以下所给信息,在数据库Sales中,创建一个带默认参数的存储过程proc_3,其功能是:输入销售员的工号和性别,输出指定销售员工号的信息,指定默认工号为“S01”、默认性别为“男”。

1667037109241.jpg

> 这里我们定义了两个参数,分别是@SaleID和@Sex,在执行存储过程中输入的参数采用的是通过参数名传递参数方式,指定工号未“S08”,而未指定性别,所以采用的是默认性别。

sql语句:

USE Sales GO CREATE PROC proc_3 @SaleID char(3)='S01', @Sex char(2)='男' AS SELECT * FROM Seller WHERE SaleID=@SaleID AND Sex=@Sex GO EXEC proc_3 @SaleID='S08'

1667037124646.jpg

而当我们要查询“工号为“S03”的女销售员李芳时,由于默认指定性别为男,所以没有结果。

1667037137055.jpg


3、带输出参数的存储过程

定义输出参数可以从存储过程中返回一个或多个值到用户,这里要注意,在定义带输出参数的存储过程的时,在创建语句和执行语句中必须要使用OUTPUT关键字。

语句格式如下:

<@参数名> 类型 OUTPUT... 
/*定义参数时*/
WHERE <@参数名>=名称 ... 
/*WHERE赋值参数时*/
DECLARE <@形参名> 类型...
EXEC <已创建的存储过程名称> <@参数名> OUTPUT...
/*在定义形参为输出参数和执行存储过程时*/


例5、根据以下所给信息,在数据库Sales中,创建一个带有输入参数和输出参数的存储过程proc_4:其功能是通过一个给定的销售员工号,查询出该销售员销售的商品总量及销售总金额,并通过输出参数进行返回。

1667037161062.jpg

首先为了得到查询出该销售员销售的商品总量及总金额,要定义了三个参数,分别是一个输入参数:代表销售员的工号、两个输出参数:分别代表销售商品总量及销售总金额。同时在SELECT语句中使用SUM()函数对多个销售商品数量和销售金额进行合计。然后在执行语句中通过DECLARE语句定义输出参数,并输出。

sql语句:

USE Sales GO CREATE PROC proc_4 @saleID char(3), @quantity int OUTPUT, @total money OUTPUT AS SELECT @quantity=SUM(quantity),@total=SUM(total) FROM orders,OrderDetail WHERE Orders.OrderID=OrderDetail.OrderID AND Orders.SaleID=@saleID GO DECLARE @quantity int DECLARE @total money EXEC proc_4 S03,@quantity output,@total output PRINT '该销售员销售总量为:'+ str(@quantity)+ ',总金额为:'+str(@total)

1667037187475.jpg


4、带返回值的存储过程

存储过程执行后会返回整型状态,若返回代码为0,表示成功执行;若返回代码为-1~-99之间的整数,表示没有成功执行,可使用RETURN关键字进行返回。


例6、根据以下所给信息,在数据库Sales中,创建一个带有返回值的存储过程proc_5:其功能是通过一个给定的商品编号,查询该商品的库存情况,库存量大于等于500返回1,小于500返回0。

1667037199553.jpg

sql语句:

USE Sales GO CREATE PROC proc_5 @ProductID char(6) AS declare @x int select @x=stocks FROM Product WHERE ProductID=@ProductID IF @x>=500 return 1 ELSE return 0 GO DECLARE @s int EXEC @s = proc_5 'P01001' PRINT str(@s)


(七)存储过程的修改


使用ALTER PROC语句修改已存在的存储过程,其与创建存储过程相同,这里不再累述。


(八)存储过程的删除


使用DROP PROC语句可以删除创建的存储过程。

语句格式如下:

DROP PROC <已创建的存储过程名称>...
相关实践学习
使用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
相关文章
|
8天前
|
存储 SQL 数据库
数据库sql语句-----游标和存储过程
数据库sql语句-----游标和存储过程
16 1
|
2天前
|
SQL 存储 安全
SQL接口如何保护数据库免受未经授权的访问?
【5月更文挑战第21天】SQL接口如何保护数据库免受未经授权的访问?
17 3
|
4天前
|
SQL Oracle 关系型数据库
一些SQL数据库工具的介绍
【5月更文挑战第19天】一些SQL数据库工具的介绍
17 4
|
7天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
16 0
|
8天前
|
关系型数据库 MySQL 数据处理
轻松入门MySQL:数据库之触发器,自动守护你的数据宝库(17)
轻松入门MySQL:数据库之触发器,自动守护你的数据宝库(17)
|
8天前
|
关系型数据库 MySQL
Mysql基础第二十八天,使用触发器
Mysql基础第二十八天,使用触发器
33 0
Mysql基础第二十八天,使用触发器
|
8天前
|
SQL 关系型数据库 MySQL
Mysql 触发器
Mysql 触发器
19 1
|
8天前
|
SQL 关系型数据库 MySQL
mysql触发器 更新大数据
【4月更文挑战第18天】
|
8天前
|
存储 SQL 关系型数据库
【MySQL 数据库】10、MySQL 的触发器
【MySQL 数据库】10、MySQL 的触发器
25 0
|
8天前
|
SQL 存储 关系型数据库
MySQL技能完整学习列表8、触发器、视图和事务——1、触发器(Triggers)的使用——2、视图(Views)的创建和使用——3、事务(Transactions)的管理
MySQL技能完整学习列表8、触发器、视图和事务——1、触发器(Triggers)的使用——2、视图(Views)的创建和使用——3、事务(Transactions)的管理
42 0

热门文章

最新文章