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

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

存储过程

存储过程的优点：

• 允许模块化设计
• 改善性能
• 减少网络流量
• 增强应用程序的安全性

创建存储过程

CRAETE { PROC | PROCDURE } [schema_name.] procedure_name
[{@parameter[type_schema_name.]data_type}]
[=default] [OUT | OUTPUT]] [,…n] [WITH RECOMPILE]
AS {<sql_statement>} [;] […n] [;]

<sql_statement> ::= { [BEGIN] statements [END] }

执行存储过程

[{EXEC | EXECUTE}]
{
　[@retrun_status=]
　{proc_name}
　　[[@parameter_name] {value
　　　　　　　　　　　　| @variable [OUTPUT]
　　　　　　　　　　　　| [DEFAULT]
　　　　　　　　　　　　}
　　]
　[,…n]
　[WITH RECOMPILE]
}

其中，参数可以按照两种方式传递给存储过程：

• 按参数位置传递，例：exec proc_name 10,‘x’
• 按参数名传递，使用这种方式可以不用管参数间的相对顺序
  ，例：exec proc_name @y=‘x’,@x=10

 /**
  * 建立查询地址在海淀区的顾客的购买情况的存储过程，列出顾客姓名，购买的商品名，单价，购买日期，会员积分数
  */
  create procedure p_CustBuy1
  as 
  select CName,GoodsName,SaleUnitPrice,SaleDate,b.Score
  from Table_Customer a join Table_Card b on a.CardID=b.CardID
  join Table_SaleBill c on c.CardID=b.CardID
  join Table_SaleBillDetial d on d.SaleBillID=c.SaleBillID
  join Table_Goods e on e.GoodsID=d.GoodsID
  where Adderss='海淀区'

 /**
  * 建立查询地址在指定地区的顾客的购买情况的存储过程，列出顾客姓名，购买的商品名，单价，购买日期，会员积分数
  */
  create procedure p_CustBuy2
  @area varchar(20)
  as
  select CName,GoodsName,SaleUnitPrice,SaleDate,b.Score
  from Table_Customer a join Table_Card b on a.CardID=b.CardID
  join Table_SaleBill c on c.CardID=b.CardID
  join Table_SaleBillDetial d on d.SaleBillID=c.SaleBillID
  join Table_Goods e on e.GoodsID=d.GoodsID
  where Adderss=@area
  
  # 执行
  exec p_CustBuy2 '海淀区'

/**
  * 建立查询某个指定地区购买了单价高于指定价格的商品的顾客的购买信息，列出顾客姓名，购买的商品，单价，购买日期，
  * 会员积分数。其中默认地区为“北京市海淀区”。
  */
  create procedure p_CustBuy3
  @area varchar(20)='北京市海淀区',@Price money
  as
  select CName,GoodsName,SaleUnitPrice,SaleDate,b.score
  from Table_Customer a join Table_Card b on a.CardID=b.CardID
  join Table_SaleBill c on c.CardID=b.CardID
  join Table_SaleBillDetial d on d.SaleBillID=c.SaleBillID
  join Table_Goods e on e.GoodsID=d.GoodsID
  where Adderss=@area and SaleUnitPrice>@Price 
  
  # 执行
  exec p_CustBuy3 @Price=1000

 /** 
  * 计算两个数的乘积，并将计算结果作为输出参数返回给调用者
  */
  create procedure p_multi
  @var1 int,@var2 int,@var3 int output
  as
  set @var3=@var1*@var2
  
  # 执行
  declare @res int
  exec p_multi 5,7,@res output
  select @res

 /**
  * 建立统计指定地区和指定性别的顾客人数和平均年龄的存储过程，并将统计的结果作为输出参数返回。
  */
  create procedure  p_CustCount
  @area varchar(20),@sex char(2),
  @count int output,@avg_age int output
  as
  select @count=count(*),@avg_age=avg(year(getdate()-year(birthdate)))
  from Table_Curstomer
  where Address=@area and Sex=@sex
  
  # 执行
  declare @x int ,@y int
  exec p_CustCount  '北京市海淀区','F',@x output,@y output
  select @x as 人数,@y as 平均年龄

/**
  * 将指定商品的单价降低5%
  */
  create procedure p_Update
  @class char(10)
  as
  update Table_Goods set SaleUnitPrice=SaleUnitPrice*0.95
  where GoodsClassID in(
      select GoodsClassID from Table_GoodsClass
      where GoodsClassName=@class
  )

/**
  * 建立删除销售日期在指定年份之前的销售单据明细表中的记录
  */
   create procedure p_Delete
   @year int
   as 
   delete from Table_SaleBillDetail
   where SaleBillID in(
       select  SaleBillID from Table_SaleBill
       where year(SaleDate)<@year
   )

删除存储过程

DROP { PROC | PROCEDURE } { [ schema_name.] procedure } [,…n]

 /**
  * 删除p_CustBuy1存储过程
  */
  drop proc p_CustBuy1

函数

标量函数

返回单个数据值。

 /**
  * 创建计算立方体体积的标量函数，此函数有三个输入参数，分别为立方体的长，高，类型均为整型，函数的返回值的类型也为
  * 整型
  */
  create function dbo.CubicVolume
  (@CubeLength int,@CubeWidth int,@CubeHeight int)
  returns int
  begin
  return (@CubeLength*@CubeWidth*@CubeHeight)
  end

 /**
  * 创建计算立方体体积的标量函数，此函数有三个输入参数，分别为立方体的长，高，类型均为整型，函数的返回值的类型也为
  * 整型
  */
  create function dbo.CubicVolume
  (@CubeLength int,@CubeWidth int,@CubeHeight int)
  returns int
  begin
  return (@CubeLength*@CubeWidth*@CubeHeight)
  end

 /**
  * 创建查询指定商品类别的商品种类数的标量函数
  */
  create function dbo.f_GoodsCount(@class varchar(10))
  returns int
  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

内联表值函数

内联表值函数的返回值是一个表，直接返回查询语句。

 /** 
  * 创建查询指定类别的商品名称和单价的内联表值函数
  */
  create function f_GoodsInfo(@class char(10))
  returns table
  return (
      select GoodsName,SaleUnitPrice from Table_GoodsClass a
      join Table_Goods b on a.GoodsClassID=b.GoodsClassID
      where GoodsClassName=@class
  )

多语句表值函数

视图和存储过程的结合。先建立一个空表，然后再对表填充数据。

 /**
  * 定义查询指定类别的商品的名称、单价生产日期和新旧商品的多语句表值函数，其中新旧商品的值为：如果到目前为止此
  * 商品的生产月数超过12个月，则为“旧商品”，若生产月数在6-12个月之间，则为“一般商品”；若生产月数小于6个月，则为
  * “新商品”。
  */
  create function f_GoodsType(@class varchar(20))
  returns @f_GoodsType table(
      商品名 varchar(50),
      单价 money,
      生产日期 datetime,
      类型 varchar(10)
  )
  begin 
  insert into @f_GoodsType
  select GoodsName,SaleUnitPrice,ProductionDate,case
  when datediff(month,ProductionDate,'2007/2/10')>12 then '旧商品'
  when datediff(month,ProductionDate,'2007/2/10') between 6 and 12 then '一般商品'
  when datediff(month,ProductionDate,'2007/2/10')<6 then '新商品'
  end
  from Table_GoodsClass a join Table_Goods b
  on a.GoodsClassID=b.GoodsClassID
  where GoodsClassName=@class
  return 
  end

删除函数

 /**
  * 删除f_GoodsType函数
  */
  drop function f_GoodsType

触发器

触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

触发器使用的场合：

• 完成比check约束更复杂的数据约束。触发器可以引用其他表中的列
• 为保证数据库性能而维护的非规范化数据。
• 可实现复杂的商业规则。
• 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

触发器分为三类：

• DML触发器，针对INSERT、UPDATE、DELETE
• DDL触发器，这对CREATE、ALTER、DROP
• 登录触发器

触发器中有两个特殊的临时表：

• INSERTED：保存insert时的新数据，update时的新数据
• DELETED：保存delete时的旧数据，update时的旧数据

后触发型触发器

使用FOR或AFTER选项定义的触发器为触发型触发器，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。

 /** 
  * 当销售单据明细表的商品销售数量大于此商品的库存数量（在商品表中）时，撤销此次商品的销售并给出提示信息。如果销售
  * 数量小于库存数量，则在插入销售单据明细记录时，应同时修改此商品的库存数量。
  */
  create trigger OperateCon
  on Table_SaleBillDetail for insert
  as
  if exists(select * from inserted a
          join Table_Goods b on a.GoodsID=b.GoodsID
          where a.Quantity > b.TotalStorage)
     begin 
         rollback
         print '此商品的库存数量小于此次的销售数量'
     end
 else
     update Table_Goods set TotalStorage=TotalStorage-(select Quantity from inserted)

 /**
  * 维护不同列之间的取值完整性的触发器。保证“商品表”中单价列的值与“商品价格变动表”中单价列的值一致
  */
  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)

前触发型触发器

使用INSTEAD OF选项定义的触发器为前触发型触发器。在这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代引发语句的操作。

 /** 
  * 创建保证销售单据表中使用的会员卡是有效日期内的会员卡的触发器。
  */
  create trigger CardValid
  on Table_SaleBill instead of insert,update
  as
  if not exists(select * from inserted a
              join Table_Card b on a.CardID=b.CardID
              where SaleDate not between StartDate and EndDate)
 insert into Table_SaleBill select * from inserted

 /**
  * 创建只允许删除会员卡积分低于500分的顾客记录的触发器（即：积分大于等于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)

删除触发器

 /**
  * 删除名为CardValid的触发器
  */
  drop trigger CardVaild

游标

select语句产生的结果集不能直接对某一行进行操作，这时便可以使用游标逐行处理结果集。

游标的组成：

• 游标结果集：定义游标的select语句返回的结果集
• 游标当前行指针：指向该结果集中的某一行的指针

游标的特点：

• 允许定位结果集中的特定行
• 允许从结果集的当前位置检索一行或多行
• 支持对结果集中当前行的数据进行修改
• 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持

1. 声明游标
   DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
   FOR select_statement
   [FOR { READ ONLY | UPDATE [OF column_name [,…n]]}]

• INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。该游标不允许修改基本表数据。
• SCROLL：指定所有的提取操作（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未指定SCROLL，则NEXT是唯一可用的提取操作。
• READ ONLY：禁止通过该游标更新数据。
• UPDATE [OF column_name [,…n]]：定义游标中可更新的列。如果未指明OF，则所有列均可更新

2.打开游标
OPEN cursor_name

3.提取数据
游标被打开后，游标的当前行指针就位于结果集中的第一行的位置，可以使用FETCH语句从游标结果集中按行提取数据。

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM] cursor_name [INTO @variable_name[,…n]]

@@FETCH_STATUS返回FETCH语句执行后的游标最终状态，取值：

• 0：FETCH语句成功
• -1：FETCH语句失败或此行不存在于结果集中
• -2：提取的行不存在

4.关闭游标
CLOSE cursor_name

5.释放游标
DEALLOCATE cursor_name

 /**
  * 对Table_Customer表，定义一个查询“北京市海淀区”姓王的顾客姓名和邮箱的游标，并输出结果
  */
  declare @cn varchar(10),@Email varchar(50)
  # 1. 声明游标
  declare Cname_cursor cursor for
      select CName,Email from Table_Customer
      where CName like '王%' and Address like '北京市海淀区'
   # 2. 打开游标   
  open Cname_cursor
  # 3. 提取数据
  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
  # 4. 关闭游标
  close Cname_cursor
  # 5. 释放游标
  deallocate Cnmae_cursor  

 /**
  * 声明带SCROLL的游标，并通过绝对定位功能实现游标的当前行的任意方向的滚动。定义查询“北京市海淀区”的所有顾客
  * 姓名和出生日期的游标，并将游标内容按出生日期降序排序。显示出游标结果中的最后1行，第4行，当前行后边的第3行
  * 以及当前行前边的第2行数据。
  */
  declare CS_cursor scroll cursor for
      select Cname,BirthDate from Table_Customer
      join SC on S.Sno=SC.Sno
      where Address like'北京市海淀区'
      order by BirthDate desc
  
  open CS_cursor
  
  fetch last from CS_cursor
  fetch absolute 4 from CS_cursor
  fetch relative 3 from CS_cursor
  fetch relative -2 from CS_cursor
  
  close CS_cursor
  
  deallocate CS_cursor