【计算机三级数据库技术】第8章数据库后台编程技术--附思维导图

短命女 2022-11-04 01:00 148阅读 0赞

### 目录 ###

*  1 存储过程
 *   *  1.1 创建
     *  1.2 执行
     *  1.3 删除
 *  2 用户定义函数
 *   *  2.1 创建和调用标量函数
     *  2.2 创建和调用内联表值函数
     *  2.3 创建和调用多语句表值函数
     *  2.4 删除用自定义函数
 *  3 触发器
 *   *  3.1 基本概念
     *  3.2 创建触发器删除触发器
 *  4 游标
 *   *  4.1 游标的组成
     *  4.2 特点
     *  4.3 使用游标
     *  4.4 示例

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTY5Ng_size_16_color_FFFFFF_t_70_pic_center]

# 1 存储过程 #

## 1.1 创建 ##

（1）传参的存储过程

Create PROC [shema_name] proc_name
    @变量名1 as varchar(20)
    @变量名2 int 
    AS 
    select ....
    where Address = @变量名1

（2）带返回值的存储过程

Create PROC [shema_name] proc_name
    @变量名1 int
    @变量名2 int output
    AS 
    select ....
    Set  @变量名2 = 。。。

## 1.2 执行 ##

Declare @res int
    Exec Proc_name  5,7,@res output
    print @ res

## 1.3 删除 ##

Drop PROC proc_name

# 2 用户定义函数 #

## 2.1 创建和调用标量函数 ##

（1）标量函数是返回单个数据值的函数

Create Function schema.function_name
    (@cubelengh int,@cubewidth int ,@cubeheight int)
    Return int
    AS 
    Begin
    RETURN (@cubelength,@cubewidth,@cubeheight)
    end

（2）调用：Select dbo.function\_name(4,6,8)%dbo是架构名

## 2.2 创建和调用内联表值函数 ##

（1）内联表函数的返回值是一个表，该表的内容是一个查询语句的结果

Create Function schema.function_name
    (@cubelengh int,@cubewidth int ,@cubeheight int)
    Return Table
    AS 
    RETURN (select ....)

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

（1）基本概念  
多语句表值函数的功能是视图和存储过程的组合，可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条SQL语句构建（类似于存储过程）可以在select 语句的From子句中使用多语句表值函数（同视图）

Create Function schema.function_name
    (@cubelengh int,@cubewidth int ,@cubeheight int)
    Returns @return_variable Table
    AS 
    BEGIN
    ....
    RETURN
    END

## 2.4 删除用自定义函数 ##

Drop FUNCTION schema.function_name

# 3 触发器 #

## 3.1 基本概念 ##

是一种特殊的存储过程，在于不需要由用户来直接调用，而是在对表中的数据进行UPdata、INsert、Delete操作时自动触发执行的。触发器是用于保证业务规则和数据完整性，其主要优点是用于可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性月约束的功能

## 3.2 创建触发器删除触发器 ##

（1）创建

FOR|After
    				指定触发器只有在引发的SQL语句中指定的操作都已经成功执行，并且所有的约束检查也成功完成后，才执行此触发器（后触发器）
    		Instead Of
    				指定执行触发器而不是执行执行触发器执行的SQL，从而代替出发语句的操作(前触发器)
    		INSERT | UPDATE | DELETE
    				引发触发器执行的操作，若同时指定多个操作，则各操作之间用逗号分隔
    	注意：After触发器，可以在同一个中操作上间建立多个触发器。但是对于Instead OF前触发器，只能建立一个触发器

*  创建后触发器

Create Trigger OperateCon
    ON Table_sale For INSERT
    AS
    IF ....sql
    Begin
    ROLLBACK
    END
    ELSE 
    ....sql

*  创建前触发器

Create Trigger OperateCon
    ON Table_sale INSTEAD OF INSERT
    AS
    IF ....sql

（2）两个临时工作表

*  DELETED表  
    用于存储Delete和UPdate语句所影响的行的副本，在执行Delete操作，被删除的数据被保存到Delete表中，在执行Update操作时，对于被修改操作影响的所有数据行，将更改前的数据保存到Delete表中，Deleted表和执行操作的基本表通常没有相同的数据行
 *  INSERTED表  
    用于存储INsert和UPdate语句所影响的行的副本，在执行INsert操作时，新插入的数据同时被保存到INserted表中，在执行Update操作时，对被修改操作影响的所有数据行，将更改后的数据保存到INSerted表中。INserted表中的内容是执行操作的基本表中新数据行的副本
 *  注意：UPdate操作类似于在删除之后执行插入：首先在执行操作的基本表中删除更新前的行，并将这些行复制到Deleted表中，然后将更新后的新行插入到执行操作的基本表和INSERTED表中

（3）删除

DROP TRigger trigger_name

# 4 游标 #

## 4.1 游标的组成 ##

游标结果集  
游标当前指针

## 4.2 特点 ##

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

## 4.3 使用游标 ##

（1）声明游标

declare cursor_name [INSENSITIVE][SCROLL] CURSOR 
    FOR select_sql
    [FOR {Read ONLY |UPDATE of column_name}]
    				INSENSITIVE：定义一个游标，以创建将由游标使用的数据的临时副本
    				SCROLL：指定所有的提取选项均可用

（2）打开游标

open cursor_name

（3）提取数据

NEXT:返回紧跟当前行之后的数据行
    			PRIOR：返回紧邻当前行前面的数据行，并且当前行递减为结果行
    			FIRST：返回游标中第一行并将其作为当前行
    			LAST：返回游标中的最后一行并将其作为当前行
    			ABSOLUTE n:n为正数，返回从游标开始第一行开始的第n行。n为负数，返回从游标最后一行开始之前的第n行
    			RELATIVE n:如果n为正数，则返回当前行之后的第n行，并将返回的行称为新的当前行。为负数，返回当前行之前的n行

注意：在对游标数据进行提取的过程中，可以使用@@FETCH\_STATUS全局变量判断数据提取的状态

（4）关闭游标

CLOSE cursor_name

（5）释放游标

DEALLOCATE CS_cursor

## 4.4 示例 ##

Declare CS_cursor SCROLL CURSOR FOR
    select Cname,BirthDate FROM Table_customer
    Join SC ON S.Sno = SC.Sno
    where Address Like' 北京市海淀区'
    Order By BirthDdate DESC
    Open CS_cursor
    FETCH LAST FROM CS_cursor  --提取游标中的最后一行数据
    FETCH ABSOLUTE 4 FROM CS_cursor --提取游标中的第4行数据
    CLOSE CS_cursor
    DEALLOCATE CS_cursor

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTY5Ng_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/9814e3feec93404888e4b222d9c56f83.png