SQL-Server程序设计课件

11.SQLServer用户自定义函数7/25/202311数据库的游标是类似于C语言指针一样的语言结构。SELECT语句是针对数据库中表的所有数据进行处理的，如果需要对某一行数据进行处理，使用SELECT语句就显得力不从心。游标正好弥补了这一缺陷，它类似于一个指针，可以定位于SELECT语句结果集合中的某指定行，从而对该行的数据进行操作。

游标7/25/20232双证班爱拼才会赢工作室游标的使用过程依次为：声明游标打开游标使用游标关闭游标、释放游标

游标7/25/20233双证班爱拼才会赢工作室声明游标使用游标的第一步骤就是声明游标，声明游标用于指定游标的特性、游标对其进行操作的结果集。[实例11.11]定义游标，打开学生信息表。

7/25/20234双证班爱拼才会赢工作室操作步骤：(1)启动查询分析器，输入如下SQL语句DECLAREXS_CURSCROLLCURSORFORSELECT*FROM学生信息OPENXS_CURSELECT当前游标记录个数=@@CURSOR_rowsgo声明游标7/25/20235双证班爱拼才会赢工作室如下图所示：声明游标7/25/20236双证班爱拼才会赢工作室归纳分析：

上面的例子首先通过DECLARE关键字来定义游标，然后选择一个表，打开游标，就可以查询到当前表中记录的个数。其中@@cursor_row表示当前游标记录数目是一个全局变量。由此，我们给出打开游标的命令格式：

DECLARE游标名称[INSENSITIVE][SCROLL]CURSORFORSELECT语句

[FORREADONLY|UPDATE[OF列名1，列名2，列名3…]]声明游标7/25/20237双证班爱拼才会赢工作室命令说明：游标名：为声明游标所起的名字。INSENSITIVE：使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库里创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。SCROLL：使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能：FIRST：取第一行数据；LAST：取最后一行数据；PRIOR：取前一行数据；NEXT：取下一行数据；RELATIVE：按相对位置取数据；ABSOLUTE：按绝对位置取数据。

声明游标7/25/20238双证班爱拼才会赢工作室如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT功能。SELECT语句：一些标准的SELECT语句，主要用来定义游标所要进行处理的结果集在声明游标的SELECT语句中，不允许使用如COMPUTE，COMPUTEBY和INTO等关键字。FORREADONLY：声明只读光标。不允许通过只读光标进行数据的更新。UPDATE[OF列名1，列名2，列名3…]：定义在这个游标里可以更新的列。如果定义了[OF列名1，列名2，列名3…]，那么只有列在表中的列可以被更新；如果没有定义[OF列名1，列名2，列名3…]，那么游标里的所有列都可以被更新。声明游标7/25/20239双证班爱拼才会赢工作室

打开游标在可以使用游标之前，必须首先打开游标，开游标的语法如下：OPENCURSOR_NAME

使用关键字OPEN来打开游标，CURSOR_NAME为打开游标的名字。7/25/202310双证班爱拼才会赢工作室使用游标在打开游标以后，就可以打游标FETCH语句提取数据了。【实例11.12】根据例11.11使用绝对位置以及相对位置来提取信息。操作步骤：（1）启动查询分析器，输入如下SQL语句：-使用当前位置的第2行记录，就是游标的第3行记录-fetchrelative2from信息_cur-使用绝对位置返回游标所指的记录-fetchabsolute3from信息_cur7/25/202311双证班爱拼才会赢工作室（2）按F5键或单击工具栏“执行查询”图标执行，执行结果如图：

使用游标7/25/202312双证班爱拼才会赢工作室归纳分析：FETCH语句的目的是从T-SQL服务器游标中检索特定的记录。使用游标提取数据的语法如下：FETCH[[NEXT|PRIOR|FIRST|ABSOLUTE{N|@nvar}|IRELATIVE{n|@nvar}FROM]{{游标名称INTO变量使用游标7/25/202313双证班爱拼才会赢工作室命令说明：NEXT:从当前记录立即返回结果集合以及对返回的记录增加当前记录。如果FETCINEXT是通过游标第一次提取，返回结果集合的第一条记录。NEXT是提取游标选项的默认选项，在处理当前记录之前立即返回记录以及减少返回记录的当前记录数目。PRIOR：读取当前游标指针所指向行的上一行数据，并将游标指向该上一行。如果FETCHPRIOR是通过游标第一次提取，没有记录返回并且光标停留在第一行。FIRST:返回第一条记录并且作为当前记录。LAST：返回最后一条记录并作为当前记录。ABSOLUTE{n|@nvar}:如果n或者@nvar正数，返回从前面游标起的第n记录，并将其作为当前记录；如果为负数，那么返回当前游标前面的第n条记录；如果是0，则没有返回记录。RELATIVE{n|@nvar}:相对当前游标位置。使用游标7/25/202314双证班爱拼才会赢工作室【实例11.13】修改第5行的班级内容：启动查询分析器，输入如下语句，执行结果如图所示。

Fetchabsolute5from信息_curUpdate学生信息Select班级=’123’Wherecurrentof信息_curGoSelect*from学生信息使用游标7/25/202315双证班爱拼才会赢工作室从结果可以看出，只对游标所指的第5行的班级列进行了修改。使用游标7/25/202316双证班爱拼才会赢工作室关闭与释放游标在打开游标后,SQLServer服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。关闭游标的语法：

CLOSECURSOR_NAME

关闭游标游标后可以再次打开游标，在一个批处理中也可以多次打开和关闭游标。而游标本身也占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，一个将游标释放。释放游标的语法是：

DEALLOCATECURSOR_NAME

当释放完游标后，如果要重新使用这个游标必须重新执行声明游标的语句。7/25/202317双证班爱拼才会赢工作室关闭与释放游标注意一下关闭游标与释放游标的区别：

关闭游标并不改变游标的定义，一个游标关闭后，不需要再次声明，就可以重新打开并使用它。但一个游标释放后，就释放了与该游标有关的一切资源，也包括游标的声明，游标释放后就不能再使用该游标了，如需再次使用游标，就必须重新定义。7/25/202318双证班爱拼才会赢工作室用户自定义函数在SQLServer2000中，为了使逻辑编程更为简洁清晰，设计了函数的功能。有两类函数，系统内置函数和用户自定义函数。系统内置函数包括数学函数(如max、min、count等)、字符串函数(如len、left等)、日期函数(如year、getdate等)、系统函数(如user)，这些系统内置函数。除了可以使用系统提供的内置函数外，用户可以定义自己的函数，来补充和扩展系统支持的内置函数。用户自定义函数可以像系统函数一样在查询或存储过程中使用，也可以像存储过程一样通过EXECUTE命令来执行。7/25/202319双证班爱拼才会赢工作室创建和调用用户自定义函数例题：

创建一个函数，根据是否为团员返回不同的数据，如果输入1，即为团员则返回该团员的信息，否则返回非团员的信息。7/25/202320双证班爱拼才会赢工作室操作步骤：

createfunctionee(@yongBIT)returns@infotable(学号char(7),

姓名char(8),

期末成绩int)asbeginif@yong=1insert@infoselect学生信息.学号,姓名,期末成绩

from学生信息,学生成绩

where是否团员=1and学生信息.学号=学生成绩.学号创建和调用用户自定义函数elseinsert@infoselect学生信息.学号,姓名,期末成绩

from学生信息,学生成绩

where是否团员=0and学生信息.学号=学生成绩.学号returnendgouse学生select*fromdbo.ee(1)go7/25/202321双证班爱拼才会赢工作室其效果图如下所示：创建和调用用户自定义函数7/25/202322双证班爱拼才会赢工作室归纳分析：由上例，首先定义了一个函数的名称ee，给出了自定义函数的参数@yong，接下来通过RETURNS关键字来定义返回的表结构赋给自定义的表的变量名@info，然后通过BEGIN。。。END语句完成对定义的表的插入工作。最后通过dbo.ee(1)来调用为团员的学生的信息。我们可以给出创建函数的命令格式：创建和调用用户自定义函数Createfunction[所有者]函数的名称（[{自定义函数的参数[as]定义函数参数的数据类型}]）Returns函数返回值的数据类型[with<函数选项>[[,]…n]][as]Begin函数体Return标量值表达式End<function_option>∷={encryption|schemabinding}7/25/202323双证班爱拼才会赢工作室命令说明如下：ENCRYPTION:加密选项，避免将函数作为SQLServer复制的一部分发布。

SCHEMABINDING：指定将函数绑定到它所使用的数据库对象。如果指定了此项，则函数所涉及的数据库对象从此将不能被删除或修改，除非函数被删除或是除掉了此选项，注意绑定的数据库对象必须与该函数在统一数据库中。创建和调用用户自定义函数7/25/202324双证班爱拼才会赢工作室修改和删除用户自定义函数

1.修改用户自定义函数需要使用ALTERFUNCTION语句。2.删除用户自定义的函数的语法是：

DORPFUNCTION{[owner_name.]function_name}

{,...n}命令说明：（1）DROPFUNCTION:删除函数命令的关键字。

（2）owner_name：删除表的权力仅属于表的拥有者，且不能转移。

（3）function_name：函数的名称。7/25/202325双证班爱拼才会赢工作室[实例]将对前面定义的ee函数进行修改，修改为如果为团员则输出期末成绩大于80的学生信息。修改和删除用户自定义函数

输入下列SQL语句：alterfunctionee(@yongbit)returns@infotable(学号char(7),姓名char(8),

期末成绩int)asbeginif@yong=1insert@infoselect学生信息,学号,姓名,期末成绩from学生信息,学生成绩where是否团员=1and学生信息.学号=学生成绩.学号and期末成绩>80elseinsert@infoselect学生信息.学号,姓名,期末成绩from学生信息,学生成绩where是否团员=0and学生信息.学号=学生成绩.学号returnendgouse学生select*fromdbo.ee(1)go7/25/202326双证班爱拼才会赢工作室效果图如下：修改和删除用户自定义函数

7/25/202327双证班爱拼才会赢工作室课后习题1.声明游标可以用（）A.CREATECURSOR

B.ALTERCURSORC.SETCURSOR

D.DECLARECURSORD7/25/202328双证班爱拼才会赢工作室2.从游标中检索行的语句是()A.SELECT

B.DECLARE

C.FETCH

D.DEALLOCATE课后习题c7/25/202329双证班爱拼才会赢工作室3.游标关闭后不能对其进行的操是()A.提取

B.修改

C.删除

D.以上都是D课后习题7/25/202330双证班爱拼才会赢工作室

4.打开一个游标用()A.OPEN

B.DEALLOCATE

C.DECLARE

D