计算机三级数据库技术第8章数据库后台编程技术

本文链接：https://blog.csdn.net/qq_25662827/article/details/118443955

8.1 存储过程

8.1.1 基本概念

使用T-SQL语言编写代码时，有两种方式存储和执行代码：

（1）在客户端存储代码，通过客户端程序或SQL命令向DBMS发出操作请求，由DBMS将结果返回给用户程序。

（2）以子程序的形式将程序模块存储在数据库中，供有权限的用户通过调用反复执行。

存储过程：即存储在数据库中供所有用户程序调用的子程序。

存储过程分为三类：系统存储过程、用户自定义存储过程、扩展存储过程。

用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。本节将详细介绍用户自定义的存储过程。

扩展存储过程是SQL Server可以动态装载并执行的动态链接库（DLL）。扩展存储过程使您得以使用像C这样的编程语创建自己的外部例程。对用户来说,扩展存储过程与普通存储过程一样，执行方法也相同。

存储过程的优点：

①极高的执行效率。

②增强代码的重用性和共享性。

③使用存储过程可以减少网络流量。

④使用存储过程保证安全性。

⑤在大型数据库中,应用程序访问数据库的最主要方式就是存储过程。

⑥存储过程可以在系统启动时自动执行。

8.1.2 创建、执行和删除存储过程

存储过程定义包含两个主要组成部分：

①过程名称及其参数的说明;

②过程的主体（其中包含执行过程操作的Transact SQL语句）。创建存储过程的语法格式如下：

创建存储过程：

CREATE PROCEDURE procedure_name [;number] /*定义过程名 
[{@parameter data_type} /*定义参数的类型 
[VARYING][ = default][OUTPUT]] /*定义参数的属性 
[,...n1] 
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}] 
[FOR REPUCATION] /*执行的操作 
AS sql_statement[,...n2]

执行存储过程：

EXEC[UTE] 
{ [@return status = ] 
{ procedure_name[;number] | @procedure_name_var} 
[ @parameter = ]{ value | @variable[OUTPUT] | [DEFAULT]} 
[,...n] 
[WITH RECOMPILE] 
}

[实例1]建立查询某个指定地区购买了单价高于指定价格

商品的顾客购买信息,列出顾客姓名,购买商品名,单价,购买日期,会员积分，其中默认地区是“长沙岳麓区"

CREATE PROCEDURE p_custbuy 
@area varchar(20)='长沙岳麓区', 
@Price money 
AS SELECT......FROM......JOIN...... 
WHERE Address=@area AND SaleUnitPrice>@Price 
执行：EXEC p_custbuy @Price=1000

[实例2]建立统计某个指定地区和指定性别的顾客人数和平均年龄的存储过程，并将统计结果作为输出参数返回。

CREATE PROCEDURE p_custcout 
@area varchar(20)，@sex char @count int output 
@avg_age int output 
AS 
SELECT @count=COUNT(*), 
@ave_age= AVG(YEAR(GETDATE())-YEAR(BIRTHDATE)) 
FROM Table_Customer 
WHERE Address=@area AND Sex=@sex 
执行: DECLARE @X int,@y int EXEC p_custcount'长沙岳麓区','F',@x output, @y output SELECT @x AS人数,@y AS 平均年龄

删除存储过程：

DROP PROCEDURE

实例:

DROP PROCEDURE p_custbuy DROP PROCEDURE p_custcout DROP PROCEDURE p_update

8.2 用户定义函数

用户定义函数：

类似于编程语言中的函数，其结构与存储过程类似，但函数必须有一个RETURN子句，用于返回函数值。

两类用户定义函数：

标量函数和表值函数。前者返回单个数据值，表值函数返回一个表。

8.2.1 创建和调用标量函数

定义标量函数:

CREATE FUCTION ......
RETURNS return_data_type 
AS BEGIN [函数体] 
RETURN scalar_expression 
END

[实例]

创建查询指定商品类别的商品种类数的标量函数。

CREATE FUCTION dbo.f_GoodsCount(@class varchar(10)) 
    RETURN int 
AS 
BEGIN 
    DECLARE @x int 
    SELECT @x=count(*)FROM Table_GoodsClass a JOIN Table_Goods b 
    ON a.GoodsClassID=b.GoodsClassID 
    WHERE GoodsClassName=@class 
    RETURN @x 
END

调用标量函数：

注意：

调用时需要提供函数拥有者名和函数名；

可以在任何出现表达式的SQL语句中调用类型一致的标量函数。

[实例] 查询“服装”类商品的名称和种类数量

SELECT GoodsName AS 商品名, dbo.f_GoodsCount('服装)
AS 种类数 
FROM......
WHERE......

8.2.2 创建和调用内嵌表值函数

创建内联表值函数：

CREATE FUCTION ......RETURNS TABLE 
AS 
RETURN [select_stmt]

参数说明：select_stmt是定义内联表值函数返回值的单个SELECT语句；表值函数没有返回变量，没有函数体，只返回一个查询结果。

调用内联表值函数：

使用内联表值函数与视图类似，其作用相当于带参数的视图。

[实例] 创建查询指定类别的商品名和单价的内联表值函数

CREATE FUCTION f_GoodsInfo(@class char(10)) 
    RETURNS TABLE 
AS 
    RETURN( 
    SELECT GoodName,SaleUnitPrice FROM Table_GoodClass a JOIN Table_Goods b ON a.GoodsClasslD= B.GoodsClassID 
    WHERE GoodClassName=@class) 
调用: SELECT * FROM dbo.f_GoodsInfo('服衣')

8.2.3 创建和调用多语句表值函数

CREATE FUCTION ......RETURNS @return_variable 
TABLE<table_type definition定义返回的表结构> 
AS 
BEGIN 
[函数体:SQL语句] 
    RETURN 
END

调用建多语句表值函数:在SELECT的FROM子句中使用。

[实例]创建查询指定类别的商品名、单价、生产日期和商品种类的多语句表值函数。

CREATE FUCTION f_GoodsDatails(@class varchar(20)) 
RETURNS @f_GoodsDatails TABLE( 
商品名 varchar(50), 
单价 money, 
生产日期 datetime, 
种类数 int) 
AS 
BEGIN 
    INSERT INTO @f_GoodsDatails 
    SELECT GoodName,SaleUnitPrice,ProductionDate dbo.f_GoodsDatails(@class) 
    FROM Table_GoodClass a JOIN Table_Goods b ON a.GoodsClassID=B.GoodsClassID WHERE GoodClassName=@class) 
    RETURN 
END 
调用: SELECT * FROM dbo.f_GoodsDatails('服装')

8.2.4 删除用户自定义函数

DROP FUNCTION

实例

DROP FUNCTION f_GoodsCount 
DROP FUNCTION f_GoodsInfo 
DROP FUNCTION f_GoodsDatails

8.3 触发器

8.3.1 基本概念

触发器：特殊存储过程,在对表中的数据进行UPDATE、INSERT、DELETE操作时自动触发执行，常用于保证业务规则和数据完整性，增强数据完整性约束能力。

SQL Server 2008支持三种类型的触发器：DML、DDL登录触发器。

适用场合：

①完成比CHECK（只能实现同一表列之间取值约束）约束更复杂的数据约束。

②保证数据库性能而维护的非规范化数据。

③可实现复杂的商业规则。

④评估数据修改前后的表状态，并采取对策。

8.3.2 创建触发器

CREATE TRIGGER trigger_name ON 
{ table|view } 
WITH ENCRYPTION] 
{FOR|AFTER|INSTEAD OF} 
{[INSERT][,][UPDATE][,] 
[DELETE]} 
AS 
sql_statement[...n]

参数说明：

FOR或AFTER：后触发型，操作、约東检查完成后触发。

INSTEAD OF：前触发型，数据操作语句最多定义一个触发器。执行触发器而非引发语句。若满足完整性约束则需要重新执行这些数据操作。

注意：

①一个表可建多个触发器，每个触发器可由三个操作触发。ALTER类型同操作上建立多个触发器，INSTEAD OF类型同一操作上建立一个触发器。

②所有建立和更改数据库以及数据库对象的语句、DROP语句不允许砸在触发器中用。

③触发器不要返回任何结果。

[实例1]维护不同列的取值完整性的触发器。保证"商品表中单价列值与商品价格变动表"中单价列值一致。

CREATE TRIGGER UnitPriceConsistent 
ON Table_PriceHistory FOR INSERT,UPDATE 
AS 
DECLARE @NewPrice money 
SELECT @NewPrice=SaleUnitPrice FROM inserted 
UPDATE Table_Goods SET SaleUnitPrice=@NewPrice 
WHERE GoodsID IN(SELECT GoodsID FROM inserted)

[实例2]创建只允许删除会员卡积分低于500分的顾客记录的触发器。

CREATE TRIGGER DeleteCust 
ON Table_Customer INSTEAD OF DELETE 
AS 
IF NOT EXISTS(SELECT * FROM deleted WHERE CardID IN 
(SELECT CardID FROM Table_Card WHERE Score>=500)) 
DELETE FROM Table_Customer WHERE CardID IN (SELECT CardID FROM deleted)

8.3.3 删除触发器

DROP TRIGGER

实例

DROP TRIGGER OperateCon 
DROP TRIGGER UnitPriceConsistent 
DROP TRIGGER DeleteCust

8.4 游标

游标：实现对SELECT结果集的逐行处理。

8.4.1 游标的组成

游标结果集（SELECT返回结果集）与游标当前行指针（指向结果集中某行）

特点：定位特定行;从当前位置检索一行或多行；支持当前行数据修改；对修改结果提供不同级别的可见性支持

8.4.2 使用游标

（1）声明游标

ISO标准语法：

DECLEAR cursor_name[1] CURSOR FOR select_statement[2]

参数说明:

[1]INSENSTITIVE：创建临时副本,，对临时表操作，否则对基本表; SCROLL：范围，否则只支持NEXT；

[2]READ ONLY：禁止更新 UPDATE 更新列指定列或所有。

（2）打开游标

OPEN cursor_name

（3）提取数据

FETCH [1]FROM cursor_name INTO @variable_name[,...n]]

(4)关闭游标

CLOSE cursor_name

可以再次打开。

(5)释放游标

DEALLOCATE cursor_name

释放分配给游标的所有资源。

8.4.3 游标示例

对Table_Customer表，定义一个查询”长沙岳麓区“姓“王”的顾客姓名和邮箱的游标，并输出游标结果。

DECLARE @cn VARCHAR(10 @cn VARCHAR(50) 
DECLARE Cname_cursor CURSOR FOR 
SELECT Cname,Email FROM Table_Custom 
WHERE Cname LIKE '王%'AND Address LIKE '长沙岳麓区' 
OPEN Cname_cursor 
FETCH NEXT FROM Cname_cursor INFO @cn,@Email 
WHILE @@ FETCH_STATUS=0 
BEGIN 
PRINT'顾客姓名'+@cn+',邮箱：'+ @Email 
FETCH NEXT FROM Cname_cursor INFO @cn,@Email 
END 
CLOSE Cname_cursor 
DEALLOCATE Cname_cursor