第11章 函数和游标.ppt

第11章 函数和游标 函数是由一个或多个SQL语句组成的子程序，它可以用于封装代码提供代码共享的功能。 内置函数 用户自定义函数 如果需要对结果集中的数据进行逐行、逐列的处理，就需要游标。 11.1 用户自定义函数 11.1.1 基本概念 11.1.2 创建和调用标量函数 11.1.3 创建和调用内联表值函数 11.1.4 创建和调用多语句表值函数 11.1.5 查看和修改用户自定义函数 11.1.6 删除用户自定义函数 1. 基本概念 可以扩展数据操作的功能， 在概念上类似于一般的程序设计语言中定义的函数。 SQL Server 支持三种用户自定义函数： 标量函数 内联表值函数 多语句表值函数 示例 例10．定义查询指定系的学生姓名、性别和年龄类型的多语句表值函数，其中年龄类型列的值为：如果该学生的年龄超过该系学生平均年龄2岁，则为“偏大年龄”；如果在-1和+2范围内，则为“正常年龄”；如果小于平均年龄-1，则为“偏小年龄”。 例10 CREATE FUNCTION f_SType(@dept varchar(20)) RETURNS @retSType table(Sname char(10),Sex char(2),SType char(8)) AS BEGIN DECLARE @AvgAge int SET @AvgAge = (SELECT AVG(Sage) FROM Student WHERE Dept = @dept) INSERT INTO @retSType SELECT Sname, Sex, CASE WHEN Sage @AvgAge+2 THEN 偏大年龄 WHEN Sage BETWEEN @AvgAge-1 AND @AvgAge+2 THEN 正常年龄 ELSE 偏小年龄 END FROM Student WHERE Dept = @dept RETURN END 11.2 游标 11.2.1 游标概念 11.2.2 使用游标 11.2.3 游标示例 概述 由SELECT语句返回的行集被称为结果集。 在执行SELECT语句进行查询时，就可以得到这个结果集。 关系数据库中的操作会对整个行集起作用。 但有时用户需要对结果集中的每一行或部分行进行单独的处理，这在SELECT的结果集中是无法实现的。 游标就是提供这种机制的结果集扩展，它使我们可以逐行处理结果集。 作为面向集合的DBMS和面向行的程序设计间的桥梁，使这两种处理方式得以有效沟通。 游标特点 允许定位结果集中的特定行。 允许从结果集当前位置检索一行或多行。 支持对结果集中当前行的数据进行修改。 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中用于访问结果集中的数据的 T-SQL 语句。 2.打开游标 OPEN cursor_name 只能打开已声明但还没有打开的游标。 */50 3．提取数据 FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n } | RELATIVE { n } ] FROM ] cursor_name [ INTO @variable_name [,...n ] ] */50 提取数据参数说明 NEXT：默认选项。返回紧跟在当前行之后的数据行，且当前行递增为结果行。如果FETCH NEXT 是对游标的第一次提取操作，则返回结果集中的第一行。 PRIOR：返回紧临当前行前面的数据行，且当前行递减为结果行。如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并将游标当前行置于第一行之前。 FIRST：返回游标中的第一行并将其作为当前行 LAST：返回游标中的最后一行并将其作为当前行 */50 提取数据参数说明（续） ABSOLUTE n：如果n为正数，返回从游标第n行并将返回行变成新当前行。如果n为负数，则返回从游标最后一行开始之前的第 n 行并将返回的行变成新当前行。如果n为0，则没有行返回。 RELATIVE n：如果n为正数，则返回当前行之后的第n行并将返回的行成为新的当前行。 INTO @variable_name [，...n]：将提取的列数据存放到局部变量中。 */50 @@FETCH_STATUS 使用@@FETCH_STATUS全局变量判断数据提取的状态。 @@FETCH_STATUS返回FETCH语句执行后的游标最终状态。 返回值 含义 0 FETCH语句成功。 -1 FETCH语句失败或此行不在结