任务8.4 创建自定义函数

vSQL Server系统允许用户为满足其特殊需要，自定义函数。用户自定义函数，不能用于执行改变数据库状态的操作，可以像系统函数那样在查询或更新数据的程序段中使用。

v1．用户自定义函数的特点v

(1)重复使用编程代码，减少编程开发时间，提高编程效率。v

(2)隐藏编程开发的细节，把SQL程序开发的繁琐工作留给数据库开发专业人员。

v

(3)维修集中化，可以集中做业务上的逻辑修改，使修改自动应用到相关程序中。

v(4)可以在其他SQL语句中直接调用。v(5)函数有必须返回值(一个标量值或一个表格)。

  

 2．用户自定义函数的类型

v(1) 标量函数用RETURNS子句返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型(text、ntext、image、cursor、timestamp、table类型除外)。函数体语句定义在BEGIN…END语句内。

v (2) 内联表值函数返回的函数值为一个表。内联表值函数的定义不使用BEGIN…END语句括起来的函数体。v   

(3) 多语句表值函数可以看作是一个标量函数和内嵌表值函数的结合体。其函数值也是一个表，定义使用BEGIN…END语句括起来的函数体，返回值中的数据由函数体中的语句插入。

v用户自定义函数中可以使用的有效语句类型如下：

v(1)DECLARE语句，用于定义函数局部的数据变量和游标。

v(2)为函数局部对象赋值，例如，使用SET语句给标量和表局部变量赋值。

v(3)游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。

v(4)控制语句。

(5)SELECT语句，包含带有表达式的选择列表，表达式将值赋予函数的局部变量。v

(6)INSERT、UPDATE和DELETE语句，这些语句仅能修改函数的局部TABLE变量。

(7)EXECUTE语句，该语句调用扩展存储过程。

v用户自定义函数是由用户创建并能完成某一特定功能(如查询用户所需数据等)的子程序。

1. 使用可视化方式创建自定义函数

v【例】用可视化方式为学生管理数据库创建一个用户自定义函数，用于查询每个学生各门功课的平均成绩。v(1)在对象资源管理器中，选择要创建存储过程的数据库，展开其中的“可编程性/函数”选项，右击其中的“标量值函数”选项，在弹出的快捷菜单中，选择“新建标量值函数”命令，如图所示。

  

2．用命令方式创建用户自定义函数

v(1)创建标量值用户自定义函数v

CREATE  FUNCTION [所有者名.] 函数名  

  ([{＠参数名 [AS] 数据类型[=default]}[,…n]])   

 RETURNS 返回值数据类型   

 [WITH {ENCRYOTION|SCHEMABINDING}[[,]…n]]   

 [AS]    

BEGIN    

T-SQL语句序列 

 RETURN 标量表达式v

END

v

(2)在出现的创建标量值函数的查询编辑器窗口中，编辑相关的T-SQL命令即可。v

(3)命令编辑完毕，至此一个用户自定义的标量值函数建立成功，单击“保存”按钮，屏幕出现“文件另存为”对话框，在文件名文本框中输入新建的函数名，单击“保存”按钮，即可保存已建好的标量值函数；单击“     ”按钮可运行新建的标量值函数。

v（4）数据类型。可以是TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP、用户定义数据类型、CURSOR和TABLE类型之外的其他标量数据类型。v

（5）返回值数据类型。可以是TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE类型之外的其他数据类型。

v

（6）标量表达式。

v格式中各关键字、参数选项说明如下：

v①所有者名。指定用户自定义函数的所有者。

v②函数名。指定用户自定义函数的名称，函数名称必须符合标识符的规则。

v③＠参数名。可以定义一个或多个参数，最多可以定义1024个参数，每个参数前用“＠”符号标明。

v（7）T-SQL语句序列。即函数体，由一组T-SQL语句组成，他们决定了函数的返回值。v

（8）ENCRYPTION选项，表示对系统表中有关CREATE FUNCTION的语句文本进行加密。

v

（9）SCHEMABINDIN选项，表示用该项创建的函数所引用的数据库对象，能被删除或修改，除非该函数被删除或去掉此选项。

【例】为教务数据库创建一个成绩转换函数，实现百分制成绩与优秀、良好、中等、及格、不及格五个等级的转换。

USE 教务数据库

vGO

vCREATE  FUNCTION  转换_f(＠成绩 INT)v

RETURNS CHAR(10) 

vAS

BEGIN

v

DECLARE ＠等级 CHAR(8)v  

SELECT ＠等级= 

  CASEv  WHEN ＠成绩>=9O THEN ’优秀’v  

WHEN ＠成绩>=8O AND ＠成绩<9O THEN ’良好’v  

WHEN ＠成绩>=7O AND ＠成绩<8O THEN ’中等’v  

WHEN ＠成绩>=6O AND ＠成绩<7O THEN ’及格’v  

WHEN ＠成绩<=6O  THEN ’不及格’v  

WHEN ＠成绩 IS NULL THEN ’还没考式’v

  END

v

RETURN(＠等级)

END

调用示例：

USE 教务数据库

vGO

（1）SELECT  学号,课程号,成绩,dbo.转换_ff(成绩)  AS 成绩等级  FROM  成绩表

（2）SELECT   姓名,a.学号,a.课程号,课程名,dbo.转换_ff(成绩)  成绩等级

 FROM  成绩表  a, 学生表 b,  课程表  c

WHERE  a.学号=b.学号  AND   a.课程号=c.课程号

  vCREATE FUNCTION [所有者名.] 函数名v([{＠参数名 

[AS] 数据类型[=default]}[,…n]])

vRETURNS TABLE

v[WITH {ENCRYOTION|SCHEMABINDING}[[,]…n]]v

[AS]

vRETURN [(]select-stmt[)]

v格式中各关键字、参数选项说明如下：

v ①TABLE表示指定表值型函数的返回值为表。

②select-stmt是定义表值型函数返回值的单个SELECT语句。v   

③其他参数作用与“创建标量值用户自定义函数”的参数相同。

【例】为学生管理数据库创建一个内联表值型函数，返回其中学生名册的信息。

vUSE 学生管理

vGOv

CREATE  FUNCTION  学生名册_f

(＠成绩 INT)

vRETURNS TABLE v

AS

RETURN

( vSELECT 学号,姓名,性别,入学成绩,家庭住址v  FROM 学生名册v

WHERE 入学成绩 >= ＠成绩 )

调用内联表值型函数，查询入学成绩在600分以上的学生：

USE [学生管理]

GO

SELECT   *  FROM   [dbo].[学生名册_f] (600)

GO 

v(3)创建多语句表值型函数

vCREATE FUNCTION [所有者名.] 函数名v([{＠参数名 

[AS] 数据类型[=default]}[,…n]])v

RETURNS ＠表变量 TABLE 

({字段名,数据类型[列规则|表约束]}[,…n])

v[WITH {ENCRYOTION|SCHEMABINDING}[[,]…n]]v[AS]

vBEGINvT

-SQL语句序列

vRETURN v

END

v格式中各关键字、参数选项说明如下：

v①表变量。用于存储作为函数值返回的数据行。

v②T-SQL语句序列。由一组T-SQL语句组成，这种组合不会产生副作用，他们决定了函数的返回值。

v③其他参数作用与“创建标量值用户自定义函数”的参数相同。

【例】为学生管理数据库创建一个多语句表值型函数，返回指定班级的开课信息。

USE 学生管理

GO

CREATE FUNCTION 开课表_f(＠班级名 CHAR(12))

RETURNS ＠开课表 TABLE(课程名 varchar(18),

上课教室 char(6),选课号 char(12),课程号 char(12),

上课周数 int,已选人数 int )

AS

BEGIN

INSERT ＠开课表

SELECT K.课程名,X.上课教室,X.班级,X.上课周数,K.已选人数

FROM 课程表 K,选课表 X

WHERE K.课程号 = X.课程号  AND  班级 = ＠班级名

RETURN

END

注:课程表、选课表必须包含有需要的字段属性。

【例】为[教务数据库]创建一个表值型函数，返回指定成绩高于某一要求的学生学习信息。使用到三个表的数据.

USE  教务数据库

GO

CREATE FUNCTION  综合成绩表(@成绩  int)

RETURNS  

TABLE

AS

  RETURN  

( SELECT   姓名,a.学号,a.课程号,课程名,成绩, dbo.转换_ff(成绩)  成绩等级,专业班级

 FROM  成绩表  a, 学生表 b,  课程表  c

    WHERE  a.学号=b.学号  AND   a.课程号=c.课程号  AND  成绩>=@成绩 )

调用示例:

USE [教务数据库]

GO

SELECT * FROM [dbo].[综合成绩表] (80)

GO

执行用户自定义函数

引用或调用用户自定义函数时，应指定函数名。如果有参数，可在括号内指定表达式，提供传递给参数的数据，调用函数时，必须提供所有参数的参数值，并且必须以CREATE FUNCTION语句定义参数的相同序列指定参数值。

【例】调用用户创建的“转换_f”函数，按五个等级显示每个学生所选课程的成绩。

vSELECT X.学号,姓名,K.课程号,K.课程名,DBO.转换_f(成绩) vAs ’成绩等级’v

FROM 选课表 X,学生名册 XS,课程表 K 

vWHERE XS.学号 = X.学号v AND X.课程号 = K.课程号

修改和删除用户自定义函数

1．用可视化方式修改和删除用户自定义函数

【例7.83】用可视化方式修改和删除已建好的“学生名册_f”函数。

(1)在对象资源管理器中，选择要修改和删除的“学生名册_f”函数所在的学生管理数据库，展开其中的 “可编程性/函数/表值函数”选项，右击其中的“学生名册_f”用户自定义函数，在弹出的快捷菜单中，可以选择“修改”命令，如图所示。

(2)对出现在查询编辑器窗口中“学生名册_f”函数的T-SQL语句进行修改即可，如图所示。

2．用命令方式修改和删除用户自定义函数

v(1)修改用户自定义函数v用ALTER FUNCTION语句可以修改用户自定义函数。

v(2)删除用户自定义函数v  DROP FUNCTION {[所有者名.] 函数名}[,…n]

v

【例】用命令方式修改和删除已建好的“学生名册_f”函数。

ALTER FUNCTION学生名册_f(＠成绩 INT)

RETURNS TABLE

AS

RETURN ( SELECT 学号,姓名,性别,入学成绩,家庭住址

FROM 学生名册  

WHERE 入学成绩 >= ＠成绩 )

  DROP FUNCTION 学生名册_f

一填空题

1．T-SQL语言的三种类型分别是____、数据操作语言和数据控制语言。

2．在SQL Server中，变量可分为两类：_____和局部变量。当声明局部变量后，可用    或_____语句为其赋值。

3．常规标识符是指不需要使用_____或_____等定界符进行分隔的标示符。

4．比较运算能够进行除_____型、_____型和_____型之外的其他数据类型表达式的比较操作。

5．在Transact-SQL中局部变量用_____语句来声明。

二选择题

1．&、*、l和OR这四个运算符中优先级最高的是(     )。  

A．&    B．*C. l    D．OR

2．SELECT语句属于T-SQL的语言类型是(     )。

A．数据定义语言    B．数据操纵语言C．数据控制语言    D．流程控制语言

3．下列常量中属于字符串常量的是(     )。  

A．‘你好’    B．'What"s this'  

C.NNEW'    D."What's your name.

4．下面选项中，属于T-SQL不支持的流程控制语句是(     )。

A. IF…THEIN…ELSE

B. BEGIN…END

C. DO CASE

D. DO WHILE

5．聚合函数(     )可实现平均数的求取。

A. SUM      B. AVG      C. MIN    D. MAX

上机实践题：

对教务数据库，

(1)找出最高分数的学生学号、姓名、课程名、成绩;

(2)找出最低分数的学生学号、姓名、课程名、成绩; 

(3)找出高于平均分数的学生学号、姓名、课程名、成绩,及人数;vv

USE  教务数据库

vGOv

DECLARE @AVG real,@MAX  real,@MIN  real

vSELECT @MAX=MAX(成绩)  FROM 成绩表v

SELECT @MIN=MIN(成绩)  FROM  成绩表

vSELECT @AVG=AVG(成绩)  FROM 成绩表 

vSELECT @MAX AS '最高分',@MIN AS '最低分', @AVG AS '平均分‘vv

SELECT '最高分学生情况：'  AS  '分类一'v

SELECT  S.学号,S.姓名,G.课程号,K.课程名,G.成绩

FROM 成绩表  G,学生表 S,课程表   K

WHERE   G.成绩>=@MAX AND S.学号=G.学号AND G.课程号=K.课程号

vvSELECT '最低分学生情况：' AS '分类二'v

SELECT  S.学号,S.姓名,G.课程号,K.课程名,G.成绩  FROM 成绩表  G,学生表  S,课程表 K

v

WHERE   G.成绩<=@MIN AND G.学号=S.学号AND G.课程号=K.课程号

vvSELECT '高于平均分学生情况：' AS '分类三'v

SELECT  COUNT(学号) AS '学生人次'  FROM  成绩表 

WHERE  成绩>=@AVG

vSELECT  S.学号,S.姓名,G.课程号,K.课程名,G.成绩  FROM 成绩表  G,学生表  S,课程表  Kv   WHERE   G.成绩>=@AVG AND G.学号=S.学号AND G.课程号=K.课程号