SQL数据库教程C7_2_2014

1、7.3 函数函数在在T-SQLT-SQL语言中，函数被用来执行一些特殊的运算以支持语言中，函数被用来执行一些特殊的运算以支持SQL SQL ServerServer的标准命令。的标准命令。SQL ServerSQL Server上使用的函数有两种类型：上使用的函数有两种类型：l系统函数：由开发商开发，由系统维护，不属于具体的数系统函数：由开发商开发，由系统维护，不属于具体的数据库。可在当前服务器实例中被调用。据库。可在当前服务器实例中被调用。 比如在某脚本中比如在某脚本中或在某数据库的存储过程、触发器和用户自定义函数的代或在某数据库的存储过程、触发器和用户自定义函数的代码中被调用。码中被调用。

2、l用户自定义函数：指用户通过用户自定义函数：指用户通过CREATE FUNCTIONCREATE FUNCTION命令创建命令创建的函数。它通常属于具体的数据库，可以通过数据库的的函数。它通常属于具体的数据库，可以通过数据库的“用户自定义函数用户自定义函数”节点查看其属性。调用方法与系统函节点查看其属性。调用方法与系统函数相似。数相似。 仅择要介绍其中的几个类别和最常用的函数。仅择要介绍其中的几个类别和最常用的函数。7.4 系统函数系统函数7.4 系统函数系统函数（1)1)字符串函数字符串函数l字符串函数可以对二进制数据、字符串和表达字符串函数可以对二进制数据、字符串和表达式执行某些运算式执行

3、某些运算. .l大多数字符串函数只能用于大多数字符串函数只能用于charchar和和varcharvarchar数据数据类型，少数也可以用于类型，少数也可以用于binarybinary和和varbinaryvarbinary数据数据类型类型. .l某些字符串函数还能够处理某些字符串函数还能够处理texttext、ntextntext、imageimage数据类型的数据数据类型的数据. . 7.5 系统函数系统函数（1)1)字符串函数字符串函数 p291p291表表A-5A-5基本字符串函数：基本字符串函数：UPPER(UPPER(大写大写) )、LOWER(LOWER(小写小写) )、SPAC

4、E(SPACE(空空格格) )、REPLICATE(REPLICATE(重复重复) )、STUFF(STUFF(子串处理子串处理) )、REVERSE(REVERSE(反反序序) )、LTRIM(LTRIM(删左空格删左空格) )、RTRIM(RTRIM(删右空格删右空格) )。字符串查找函数：字符串查找函数：CHARINDEX(CHARINDEX(返回匹配字串位置返回匹配字串位置) )、PATINDEX(PATINDEX(用通配符匹配用通配符匹配) )。长度和分析函数：长度和分析函数：DATALENGTH(DATALENGTH(字串长度字串长度) )、SUBSTRING(SUBSTRING(

5、定定位截取子串位截取子串) )、RIGHT(RIGHT(右起截取子串右起截取子串) )。转换函数：转换函数：ASCII(ASCII(首字符首字符ASCIIASCII码码) )、CHAR(CHAR(转为字符转为字符) )、STR(STR(数值转字符数值转字符) )、SOUNDEX(SOUNDEX(取字符串辅音，用于比较读取字符串辅音，用于比较读音相似性音相似性) )、7.3 系统函数系统函数（2)2)P293表表A-9 日期和时间函数用于对日期和时间数据进行日期和时间函数用于对日期和时间数据进行处理和运算，返回值通常为字符串、数值、日处理和运算，返回值通常为字符串、数值、日期、时间。期、时间。7

6、.4 用户自定义函数用户自定义函数7.4 用户自定义函数用户自定义函数 用用T-SQL创建自定义函数创建自定义函数说明：说明： 自定义函数必须在当前数据库中定义。自定义函数必须在当前数据库中定义。 “函数名函数名”必须符合标识符命名规则，在数据库中名称必须唯一，必须符合标识符命名规则，在数据库中名称必须唯一，“所有者所有者”无说明时，默认为数据库拥有者（无说明时，默认为数据库拥有者（dbodbo）。）。 “ 参数名参数名”指用局部变量定义的形参，用于接收函数调用时传入指用局部变量定义的形参，用于接收函数调用时传入的实参。的实参。 默认值必须是常量，如果设定了默认值，函数调用时有实参传入默认值必

7、须是常量，如果设定了默认值，函数调用时有实参传入用实参，否则以默认值替代形参。用实参，否则以默认值替代形参。 RETURNSRETURNS指定返回值类型，指定返回值类型，RETURNRETURN指定返回值，注意这两个关键指定返回值，注意这两个关键字的区别。字的区别。 自定义函数的调用与系统标准函数的调用相同。自定义函数的调用与系统标准函数的调用相同。调用自定义函数调用自定义函数“工龄工龄”，实参为，实参为“工作时间工作时间”和和“2008”进货表进货表2006：新进数量和进价：新进数量和进价商品一览表：原平均进价和库存量商品一览表：原平均进价和库存量调用函数调用函数“均价均价”的实参来自两个表

8、的实参来自两个表调用函数调用函数“均价均价”7.4 用户自定义函数用户自定义函数 用用SQL语句修改、删除自定义函数语句修改、删除自定义函数 与函数创建语句基本相同，以修改后的函数覆盖已有同名函数与函数创建语句基本相同，以修改后的函数覆盖已有同名函数7.4 用户自定义函数用户自定义函数 用用SQL语句修改、删除自定义函数语句修改、删除自定义函数 7.4 用户自定义函数用户自定义函数 用企业管理器创建编辑自定义函数用企业管理器创建编辑自定义函数 7.4 用户自定义函数用户自定义函数 用企业管理器创建编辑自定义函数用企业管理器创建编辑自定义函数 7.4 用户自定义函数用户自定义函数 用企业管理器创

9、建编辑自定义函数用企业管理器创建编辑自定义函数 7.4 用户自定义函数用户自定义函数 用企业管理器创建编辑自定义函数用企业管理器创建编辑自定义函数 7.5 游标游标 如何把数据表中的某分量从表或其子集中提取出来？如何把数据表中的某分量从表或其子集中提取出来？-使用游标使用游标7.5 游标游标 7.5 游标游标 用用DECLAREDECLARE语句定义游标语句定义游标 选选SCROLL表示该游标可以在表示该游标可以在FETCH语句中任意指定数语句中任意指定数据的提取顺序，省略该项则表示该游标仅支持据的提取顺序，省略该项则表示该游标仅支持NEXT顺序顺序提取方式。提取方式。 子句子句SELECT用

10、来指定该游标使用的结果集，但不允许使用来指定该游标使用的结果集，但不允许使用用COMPUTE或或INTO子句。子句。 READ ONLY READ ONLY表示该游标中的数据只读不允许修改，比如不表示该游标中的数据只读不允许修改，比如不允许在允许在UPDATEUPDATE或或DELETEDELETE语句中引用该游标。语句中引用该游标。 UPDATA OF UPDATA OF 字段名字段名 , , n n 表示可以通过该游标更新表示可以通过该游标更新基本表的指定字段；如省略字段名列表，表示可以更新所基本表的指定字段；如省略字段名列表，表示可以更新所有字段。有字段。7.5 游标游标 用用DECLA

11、REDECLARE语句定义游标语句定义游标 STATIC与与INSENSITIVE 含义相同，在系统含义相同，在系统tempdb数据数据库中创建临时表存储游标使用的数据，即游标使用数据集库中创建临时表存储游标使用的数据，即游标使用数据集不随基本表变化，同时也无法通过游标来更新基本表。不随基本表变化，同时也无法通过游标来更新基本表。 KEYSET 指定在游标中的列顺序，并且在指定在游标中的列顺序，并且在tempdb内建立内建立一个一个KEYSET表，基本表数据修改时能反映到游标数据集中。表，基本表数据修改时能反映到游标数据集中。7.5 游标游标 用用DECLAREDECLARE语句定义游标语句定

12、义游标 使用使用OPTIMISTIC参数，表示游标数据集优先。当游标参数，表示游标数据集优先。当游标中的数据发生变化时，不允许更新或删除游标数据。中的数据发生变化时，不允许更新或删除游标数据。 使用使用TYPE_WARNING参数，当出现修改游标数据类型参数，当出现修改游标数据类型的事件时，向客户端发送警告。的事件时，向客户端发送警告。7.5 游标游标 用用DECLAREDECLARE语句定义游标语句定义游标 7.5 游标游标 用用OPEN语句打开游标语句打开游标 打开游标后，可以通过全局变量打开游标后，可以通过全局变量ERRORERROR显示游标是否打显示游标是否打开成功（开成功（0:0:打

13、开成功，否则打开失败）。打开成功，否则打开失败）。 可以通过可以通过CURSOR_ROWSCURSOR_ROWS返回游标中当前记录行的情况，返回游标中当前记录行的情况，其返回值为：其返回值为： 0 0：表无符合条件的记录或该游标已经关闭或释放：表无符合条件的记录或该游标已经关闭或释放 -1-1：表该游标为动态，记录行经常变动无法确定：表该游标为动态，记录行经常变动无法确定 n n：表指定结果集总共：表指定结果集总共n n条记录已从表中全部读入条记录已从表中全部读入 -m-m：表指定结果集并未全部读入，目前游标中有：表指定结果集并未全部读入，目前游标中有m m条记录条记录7.5 游标游标 用用F

14、ETCH语句从游标中提取数据语句从游标中提取数据 FETCH后的参数为提取记录的方式：后的参数为提取记录的方式： NEXT顺序向下提取下一行，并将顺序向下提取下一行，并将CURSOR定位此行；当定位此行；当处理完最后一行再用处理完最后一行再用FETCH NEXT， CURSOR指向最后一指向最后一行之后，此时行之后，此时FETCH_STATUS的值为的值为-1。 PRIOR顺序向前提取当前记录的前一行，并将顺序向前提取当前记录的前一行，并将CURSOR定定位此行；当第一次用位此行；当第一次用FETCH PRIOR时，没有记录返回，时，没有记录返回，CURSOR仍指向第一行之前。仍指向第一行之前

15、。 FIRST提取游标集首条记录，并将提取游标集首条记录，并将CURSOR定位此行定位此行 。 LAST提取游标集尾记录，并将提取游标集尾记录，并将CURSOR定位此行定位此行 。7.5 游标游标 用用FETCH语句从游标中提取数据语句从游标中提取数据 ABSOLUTE n|nvar ABSOLUTE n|nvar 按绝对位置提取游标结果集的第按绝对位置提取游标结果集的第n n或第或第nvarnvar条记录，并将条记录，并将CURSORCURSOR定位此行定位此行 。n n为整数，为整数，nvarnvar为整数类型变量。若为整数类型变量。若n n或或nvarnvar为负，则提取尾行之前为负，则

16、提取尾行之前倒数第倒数第n n或第或第nvarnvar条记录。条记录。 RELATIVE n|nvar RELATIVE n|nvar 按相对位置提取当前记录之后（正按相对位置提取当前记录之后（正值）或之前（负值）的第值）或之前（负值）的第n n或第或第nvarnvar条记录，并将条记录，并将CURSORCURSOR定位此行定位此行 。 FROM用于指定游标，用于指定游标，global用于指定全局游标，省略为用于指定全局游标，省略为局部游标。局部游标。 INTO指定将从游标结果集中分离出的数据存入指定的局指定将从游标结果集中分离出的数据存入指定的局部变量中。变量名列表的个数、类型必须与游标结果集中部变量中。变量名列表的个数、类型必须与游标结果集中记录的字段的个数、类型相匹配。记录的字段的个数、类型相匹配。7.5 游标游标 用用FETCH语句从游标中提取数据语句从游标中提取数据 7.5 游标游标 用用CLOSE语句关闭游标语句关闭游标 7.5 游标游标 用用DEALLOCATE语句释放游标语句释放游标 7.5 游标游标KEYSETKEYSET选项用于固定游标结果集