计算机三级数据库技术备考笔记（八）

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

• 存储过程 PROCEDURE
  • 基本概念
    • 定义：实际上是存储在数据库中供所有用户程序调用的子程序
    • 用途
      • 接收输入参数并以输出参数的形式将多个值返回给调用过程；
      • 包含执行数据库操作（包括调用其他存储过程）的编程语句
      • 向调用过程返回状态值，已表明执行成功或失败（以及失败的原因）
    • 使用存储过程的好处
      • 允许模块化程序设计
      • 改善性能
      • 减少网络流量
      • 增强应用程序的安全性
  • 创建、执行和删除存储过程
    • 1. 创建存储过程
      • 创建存储过程的SQL语句为CREATE PROCEDURE。其语法格式为:

        

        
        其中各参数说明如下:
        schema_name：过程所属的架构名
        proeedure_name：存储过程名,该名称在架构中必须唯一。建议不要定义使用前缀sp_的过程名,因为该前缀由SQLServer使用,用以指定系统存储过程。
        @parameter：存储过程的参数。在创建存储过程时可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数,否则用户在调用存储过程时必须为每个声明的参数提供值。一个存储过程最多可以有2100个参数。
        [type_schema_name.]data_type：参数以及所属架构的数据类型。所有数据类型都可以用作存储过程的参数。
        default：参数的默认值。如果定义了delaut值,则在调用存储过程时可以不指定此参数的值而执行过程。默认值必须是常量或NULL。如果存储过程使用带LIKE关键字的参数,则可包含通配符 %、 _、[ ]和 [^]。
        OUTPUT：指示参数是输出参数。使用OUTPUT参数将值返回给过程的调用方。
        ​RECOMPILE：指示数据库引擎不缓存该存储过程的计划,该存储过程在运行时将被重新编译。
        <sql_statement>：将要包含在存储过程中的一个或多个T-SQL语句。
    • 2. 执行存储过程
      • 执行存储过程可以使用T-SQL的EXECUTE语句。如果存储过程是批处理中的第一条语句,那么不使用EXECUTE关键字也可以执行存储过程。EXECUTE语句的语法格式为:

        

        

        各参数说明如下:
        @return_status：可选的整型变量,存储过程的返回状态。这个变量在用于EXECUTE语句前必须被声明过
        proc_name：要调用的存储过程名。
        @parameter：存储过程的参数,必须与存储过程中定义的相同。参数名前必须加上符号(@)。在与@parameter_name=value格式一起使用时,不要求参数名和常量的顺序与存储过程中定义的顺序一致。如果对任何参数使用了@parameter_name=value格式,则对所有后续参数都必须使用此格式。
        默认情况下,参数可为空值。
        @variable：是用来存储参数或返回参数的变量
        DEFAULT：使用定义存储过程时为参数指定的默认值。如果存储过程没有为参数指定默认值,而在执行存储过程时又使用了DEFAULT关键字,则会出现错误。
    • 3. 删除存储过程
      • 删除存储过程使用DROP PROCEDURE语句,该语句可从当前数据库中删除一个或多个存储过程。其语法格式为:
        DROP [ PROC | PROCEDURE { | [schema_name. ] procedure } [ ,…n ]
• 用户定义函数 FUNCTION
  • 定义：用户定义函数与编程语言中的函数类似,其结构与存储过程类似,但函数必须有个RETURN子句,用于返回函数值。函数说明要指定函数名、结果的类型,以及参数类型等,分为标量函数和表值函数
  • 创建和调用标量函数
    标量函数是返回单个数据值的函数
    • 定义标量函数的语法格式为:

      

      

      
      各参数说明如下:
      schema_name：用户定义函数所属架构的名称。
      ​function_name：用户定义函数的名称,该名称必须符合有关标识符的规则,并且在数据库中以及对其架构来说是唯一的。
      ​@ parameter_name：用户定义函数中的参数。可声明一个或多个参数。一个函数最多可以有2100个参数。执行函数时,如果未定义参数的默认值,则用户必须为每个已声明参数提供值。
      [type_schema_name.] parameter_data_type：参数的数据类型及其所属的架构,后者为可选项。对于T-SQL函数,允许使用除timestamp数据类型之外的所有数据类型。如果未指定type_schema_name,则数据库引擎将按以下顺序查找parameter_data_type:
      *包含SQL Server系统数据类型名称的架构
      *当前数据库中当前用户的默认架构。
      *当前数据库中的dbo架构
      [=default ]：参数的默认值。如果定义了default 值,则在执行函数时可不指定此参数的值。如果希望在调用函数时使用参数的默认值,则必须指定关键字DEFAULT。
      return_data_type：用户定义函数的返回值类型。可以是除timestamp类型之外的所有数据类型
      function_body：定义函数值的一系列T-SQL语句。
      scalar_expression：指定标量函数返回的标量值。
    • 调用标量函数
      当调用标量函数时,必须提供至少由两部分组成的名称:函数拥有者名和函数名。可在任何允许出现表达式的SQL语句中调用标量函数,只要类型一致。
  • 创建和调用内联表值函数
    内联表值函数的返回值是一个表,该表的内容是一个查询语句的结果
    • 定义内联表值函数的语法为:

      

      
      其中,selectsm是定义内联表值函数返回值的单个SELECT语句。其他各参数含义同标量函数。
      在内联表值函数中,通过单个SELECT语句定义TABLE返回值。内联表值函数没有相关联的返回变量,也没有函数体。
    • 调用内联表值函数
      对内联表值函数的使用与视图非常类似,需要放置在查询语句的FROM子句部分,它的作很像是带参数的视图。
  • 创建和调用多语句表值函数
    多语句表值函数的功能是视图和存储过程的组合,可以利用多语句表值函数返回一个表,表中的内容可由复杂的逻辑和多条SQL语句构建(类似于存储过程)。可以在SELECT语句的FROM 子句中使用多语句表值函数(同视图)。
    • 定义多语句表值函数的语法：

      

      
      各参数说明如下:
      function_body：是一系列T-SQL语句,这些语句用于填充 TABLE 返回变量。table_type_definiton：定义返回的表的结构,该表结构的定义同创建表的语句。在表结构定义中,可以包含列定义、列约束定义、计算列以及表约束定义。
  • 删除用户自定义函数

    

    
    删除函数使用DROP FUNCTION语句实现,它从当前数据库中删除一个或多个用户定义函数，其语法格式为:
• 触发器 TRIGGER
  • 基本概念
    • 定义：触发器是一种特殊的存储过程,其特殊性在于它不需要由用户来直接调用,而是在对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性,其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则,增强了数据完整性约束的功能。
    • 使用场合：
      • 完成比CHECK约束更复杂的数据约束。
      • 为保证数据库性能而维护的非规范化数据。
      • 可实现复杂的商业规则。触发器可使业务的处理任务自动进行
      • 触发器也可以评估数据修改前后的表状态,并根据其差异采取对策
    • 分类：
      • MDL触发器：如果用户要通过数据操作语言(DML)事件编辑数据,则执行DML触发器。DML事件是针对表或视图的INSERT、UPDATE或DELETE语句。
      • DDL触发器：DDL触发器用于响应各种数据定义语言(DDL)事件,这些事件主要对应T-SQL中的CREATE、ALTER和DROP语句,以及执行类似DDL操作的某些系统存储过程。
      • 登录触发器：登录触发器在遇到LOGON事件时触发,LOGON事件是在建立用户会话时引发的。
  • 创建触发器
    • 建立DML触发器的SQL语句为CREATE TRIGGER,其语法格式为:

      

      
      各参数说明如下:
      schema_name：触发器所属架构的名称。
      trigger_name：触发器名称。该名称必须遵循标识符规则,而且不能以#或##开头。
      ​table | view：与触发器相关联的表或视图,有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。在视图上只能定义INSTEAD OF触发器。
      FOR | AFTER：指定触发器只有在引发的SQL语句中指定的操作都已成功执行,并且所有的约束检查也成功完成后,才执行此触发器。如果仅指定FOR关键字,则AFTER为默认值不能在视图上定义AFTER触发器。
      INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句,从而替代触发语句的操作。
      INSERT、DELETE和UPDATE：引发触发器执行的操作,若同时指定多个操作,则各操作之间用逗号分隔。
      创建触发器时,需要注意如下几点:。在一个表上可以建立多个名称不同、类型各异的触发器,每个触发器可由所有三个操作来引发。对于AFTER型的触发器,可以在同一种操作上建立多个触发器:对于INSTEADOF型的触发器,在同一种操作上只能建立一个触发器。
      大部分T-SOL语句都可用在触发器中,但也有一些限制。例如,所有的建立和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。
      在触发器定义中,可以使用IF UPDATE子句来测试INSERT和UPDATE语句是否对指定字段有影响。如果将一个值赋给指定字段或更改了指定字段,则这个子句就为真·通常不要在触发器中返回任何结果。
    • 创建后触发型触发器
      • 使用FOR或AFTER选项定义的触发器为后触发型触发器,即只有在引发触发器执行的语句中的操作都已成功执行,并且所有的约束检査也成功完成后,才执行触发器。

        

    • 创建前触发型触发器
      • 使用INSTEAD OF选项定义的触发器为前触发型触发器。在这种模式的触发器中,指定执行触发器而不是执行引发触发器执行的SQL语句,从而替代引发语句的操作。在表或视图上,每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。

        

  • 删除触发器
    • 删除触发器使用DROP TRIGCER语句实现,它从当前数据库中删除一个或多个触发器。其语法格式为:

      

• 游标   CURSOR 
  • 相关概念
    • 结果集：关系数据库中的操作是基于集合的操作,即对整个行集产生影响,由SELECT语句返回的行集包括所有满足条件子句的行,这一完整的行集被称为结果集。
    • 游标：游标是提供用户需要对结果集中的每一行或部分行进行单独的处理时这种机制的结果集扩展,它使人们可以逐行处理结果集
  • 游标的组成

    

    • 游标结果集:指定义游标的SELECT语句返回的结果的集合。
    • 游标当前行指针:指向该结果集中的某一行的指针。
    • 游标的特点：
      • 允许定位结果集中的特定行。
      • 允许从结果集的当前位置检索一行或多行。
      • 支持对结果集中当前行的数据进行修改。
      • 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。
  • 使用游标
    • 使用游标的典型过程

      

      • 声明游标
        • 声明游标实际是定义服务器端游标的特性,例如游标的滚动行为和用于生成游标结果集的查询语句。SQLServer支持两种格式的声明游标语句:一种是基于ISO标准的语法,另一种是使用T-SQL扩展的语法。这里只介绍ISO标准语法的声明游标语句。

          

          
          各参数含义如下:
          cursor_name：所定义的服务器游标名
          INSENSITIVE：定义一个游标,以创建将由该游标使用的数据的临时复本
          SCROLL：指定所有的提取选项(FIRST、LAST、PRIOR、NEXTRELATIVE、ABSOLUTE)均可用。如果未在DECLARECURSOR中指定SCROLL,则NEXT是唯一支持的提取选项。如果指定了FAST FORWARD,则不能指定SCROLL
          select_statement：定义游标结果集的标准SELECT语句。
          READ ONLY：禁止通过该游标更新数据。在UPDATE或DELETE语的WHERE CURRENT OF子句中不能引用该游标。
          UPDATE [OF eolumn_name [,...n]]：定义游标中可更新的列。如果指定了 OF eolumn_name[,…n],则只允许修改所列出的列。如果指定了UPDATE,但未指定column_name[,…n]则可以更新所有的列。
      • 打开游标
        • 打开游标的语句是OPEN,其语法格式为：

          

      • 提取数据
        • 游标被声明和打开之后,游标的当前行指针就位于结果集中的第一行位置,可以使用FETCH语从游标结果集中按行提取数据。其语法格式如下:

          

          
          各参数的含义如下:
          NEXT：返回紧跟在当前行之后的数据行,并且当前行递增为结果行。如果FETCH NEXT是对游标的第一次提取操作,则返回结果集中的第一行。NEXT为默认选项
          ​PRIOR：返回紧临当前行前面的数据行,并且当前行递减为结果行。如果FETCHPRIOR为对游标的第一次提取操作,则不返回任何结果并将游标当前行置于第一行之前。
          ​FIRST：返回游标中的第一行并将其作为当前行。
          LAST：返回游标中的最后一行并将其作为当前行。
          ​ABSOLUTE n：如果n为正数,返回从游标第一行开始的第n行,并将返回的行变成新的当前行。如果"为负数,则返回从游标最后一行开始之前的第"行,并将返回的行变成新的当前行。如果"为0,则不返回任何结果。"必须为型常量。
          ​RELATIVE n：如果n为正数,则返回当前行之后的第n行,并将返回的行成为新的当前行。如果"为负数,则返回当前行之前的第"行,并将返回的行成为新的当前行。如果"为0,则返回当前行。如果对游标的第一次提取操作时将FETCH RELATIVE的n指定为负数或0,则不返回任何结果。"必须为整型常量。
          cursor_name：要从中进行提取数据的游标名。
          INTO@variable_name[,…n]：将提取的列数据保存到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列对应。各变量的数据类型必须与相应的结果列的数据类型匹配。变量的数目必须与游标选择列表中的列的数目一致。
      • 关闭游标
        • 关闭游标使用CLOSE语,其语法格式为:

          

          
          在使用CLOSE语句关闭游标后,系统并没有完全释放游标的资源,并且也没有改变游标的定义,当再次使用OPEN语句时可以重新打开此游标。
      • 释放游标
        • 释放游标是释放分配给游标的所有资源。释放游标使用 DEALLOCATE语句,其语法格式为: