第八章、数据库后台编程技术

第八章、数据库后台编程技术

版权来源233网校,若有侵权联系shaoyayu0419@qq.com删除
版权地址：www.233.com

内容提要：

1、掌握存储过程的定义与使用

2、掌握用户定义函数的创建与使用

3、掌握触发器的定义与使用

4、掌握游标的定义与使用

第一节 存储过程

1、基本概念

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

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

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

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

存储过程分为三类

系统存储过程

用户自定义存储过程(如果没有说明的话默认)

扩展存储过程

用户自定义存储过程

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

以下三个存储过程即为用户自定义存储过程：

扩展存储过程

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

存储过程的优点

① 极高的执行效率。（所有的命令都是以批处理的方式处理）

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

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

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

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

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

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

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

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

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

创建存储过程

CREATE PROCEDURE procedure_name [;number] /*定义过程名
[{@parameter data_type}                   /*定义参数的类型
[VARYING][ = default][OUTPUT]]            /*定义参数的属性
[,…n1]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]                         /*执行的操作
AS sql_statement[,…n2]
/**
*RECOMPILE 是否重编译
*ENCRYPTION 是否加密
*FOR REPLICATION 是否不执行复制的存储过程
*/

执行存储过程

EXEC[UTE]
{  	//返回状态 
    [@return_status = ]
	//procedure_name,存储过程的组名 
	//
   { 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 [PROCEDURE_NAME]

实例：

DROP PROCEDURE p_custbuy
DROP PROCEDURE p_custcout
DROP PROCEDURE p_update

第二节 用户定义函数

用户定义函数：

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

两类用户定义函数：

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

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……

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.GoodsClassID = B.GoodsClassID 
WHERE GoodClassName = @class) 
/ / 调用： 
SELECT 
  * 
FROM
  dbo.f_GoodsInfo (‘服装’)

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_GoodsDatail(‘服装’)

4、删除用户自定义函数

DROP FUNCTION

实例

DROP FUNCTION f_GoodsCount

DROP FUNCTION f_GoodsInfo

DROP FUNCTION f_GoodsDatails

第三节 触发器

1、基本概念

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

SQL Server 2008支持三种类型的触发器：

　　DML(数据操纵由于)、DDL(数据定义语言)、登录触发器。

适用场合：

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

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

3. 可实现复杂的商业规则。

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

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语句不允许在触发器中用。

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

在触发器中有两种逻辑表

instead表和deleted

instead保存插入之前的数据

deleted保存的是删除之后的数据

【实例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)

E:Usershaoyayu2020-2021上学期学习材料计算机三级数据库384684441864

3、删除触发器

DROP TRIGGER

实例

DROP TRIGGER OperateCon

DROP TRIGGER UnitPriceConsistent

DROP TRIGGER DeleteCust

第四节 游标

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

1、游标的组成

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

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

2、使用游标

（1）声明游标

　　ISO标准语法:DECLARE 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

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

3、游标示例

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

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

例题

1、在SQL Server 2008中，对于更新操作的触发器，系统将产生2张逻辑工作表，其中存放更新前数据的逻辑工作表是（　　）。
答案：DELETE 
更新就是
1、删除原数据
2、插入数据

2、删除用户自定义的函数使用（　　）语句来实现。
答案：DROP FUNCTION

3、设在数据库应用系统设计与实现过程中有下列活动：
Ⅰ. 创建触发器
Ⅱ. 定义事务隔离性级别
Ⅲ. 数字签名
Ⅳ. 定义主码
上述活动中，用于数据库的完整性保护的是（　　）
A．仅Ⅰ和Ⅳ			B．仅Ⅰ和Ⅱ
C．仅Ⅲ和Ⅳ			D．仅Ⅱ和Ⅲ
答案：B

4、利用游标机制可以实现对查询结果集的逐行操作。下列关于SQL Server 2008中游标的说法中，错误的是（　　）
A． 每个游标都有一个当前行指针，当游标打开后，当前行指针自动指向结果集的第一行数据
B．如果在声明游标时未指定INSENSITIVE选项，则已提交的对基表的更新都会反映在后面的提取操作中
C．当@@FETCH_STATUS=0时，表明游标当前行指针已经移出了结果集范围
D．关闭游标之后，可以通过OPEN语句再次打开该游标
答案：C

5、在SQL Server 2008中，用于判断游标数据提取状态的全局变量是（　　）。
答案：@@FETCH_STATUS

6、设在SQL Server 2008某数据库中有按如下格式定义的存储过程首部：
CREATE PROC P1
@x int, @y int, @z int output AS ...
请补全下列调用该存储过程的语句。
DECLARE @S int
EXEC P1 20, 30, @S  （　　）
答案：output  

7、在SQL Server 2008中，设有教师表(教师号, 姓名, 所在部门号, 职称)和部门表(部门号, 部门名, 高级职称人数)。请编写满足下列要求的后触发型触发器(设触发器名字为tri_zc)。
　　每当在教师表中插入一名具有高级职称("教授"或"副教授")的教师时，或者将非高级职称教师的职称更改为高级职称时，均修改部门表中相应部门的高级职称人数。(假设一次操作只插入或更改一名教师的职称) ）

【解题思路】创建触发器的SQL语句为：CREATE TRIGGER
　　根据原题要求，insert触发器会在inserted表中添加一条刚插入的记录，update触发器会在更新数据后将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。在教师表中插入或者更新的时候，都会在inserted表中增加一条记录，所以只需在触发器查询inserted表中查询有没有"教授"或者"副教授"的记录，如果有，则触发修改相应部门的高级职称人数即可。

//答案
CREATE TRIGGER tri_zc 
ON 教师表 AFTER INSERT,
UPDATE AS 
  BEGIN
    DECLATE @zc VARCHAR (10),
    @dept VARCHAR (30) 
    SELECT 
      @dept = 所在部门号， @2 c = 职称 
    FROM
      inserted IF @zc = ′教授′ 
      OR ′副教授′ 
      UPDATE 
        部门表 
      SET
        高级职称人数 = 高级职称人数 + 1 
      WHERE 部门号 = @dept 
      END 

8、设在SQL Server 2008某数据库中有商品表和销售表，两个表的定义如下：
CREATE TABLE 商品表 (
  商品号 CHAR(10) PRIMARY KEY,
  商品名 VARCHAR (40),
  类别 VARCHAR (20),
  进货单价 INT
);
CREATE TABLE 销售表 (商品号 CHAR(10),
  销售时间 DATETIME,
  销售数量 INT,
  销售单价 INT,
PRIMARY KEY (商品号, 销售时间）)

下面是一个用户定义的多语句表值函数，它接受类别作为输入参数，返回该类别下的每种商品在2012年的销售总利润，并将结果按照销售总利润的降序输出。请补全该函数定义代码。 

CREATE FUNCTION f_Profit (@lb char(10))  【1】@ProfitTable【2】(
商品号 char(10),
总利润 int )
AS
BEGIN
  INSERT INTO @ProfitTable  
【3】
【4】
END

第一空：RETURNS
第二空：table
第三空：
a 
SELECT 
  a.商品号,
  SUM(
    销售数量 * (销售单价 - 进货单价)
  ) AS总利润 
FROM
  销售表 a JOIN商品表b 
  ON a.商品号 = b.商品号 
WHERE a.商品号 IN 
  (SELECT 
    商品号 
  FROM
    商品表 
  WHERE 类别 = @lb) 
GROUP BY a.商品号ORDER BY 总利润 DESC 

第四空：RETURN@Rrofit Table