9.3.2 创建和执行用户定义函数

和存储过程一样，在SQL Server 2012中创建用户定义的方法有两种：一种是使用图形化的SQL Server Management Studio来创建存储过程，另一种是使用T-SQL语句来创建存储过程。第一种方法比较适合初学者，第二种方法比较适合SQL Server程序员。

1. 使用SQL Server Management Studio创建和执行用户定义函数

(1) 启动SQL Server Management Studio，在对象资源管理器中，连接到 SQL Server数据库引擎实例，再展开该实例。

(2) 展开“数据库”，选择要创建函数的数据库，展开“可编程性”，右键选择“函数”，右键选择要创建的函数类型，再新建函数，显示模板，如图9-34所示。

(3) 在查询编辑器中，使用过程语句替换SELECT语句，如图9-35所示。

①若要测试语法，则在“查询”菜单上选择“分析”。

②若要执行函数程序，则在“查询”菜单上选择“执行”。

③若要保存脚本，则在“文件”菜单上单击“保存”按钮。接受该文件名或将其替换为新的名称，再单击“保存”按钮。

【例9-25】定义一个计算长方体体积的函数，编写程序。

解：程序如下。

CREATE FUNCTION cub

(

-- 定义长、宽、高的参数

@w int,

@l int,

@h int

)

RETURNS int

AS

BEGIN

-- return the result of the function

RETURN (@w*@h*@l)

END

GO

(4) 运行存储过程：在工具栏上单击“新建查询”，在查询窗口中，输入执行语句，如图9-36所示。

2. 用SQL语句创建和执行标量函数

语法格式如下：

CREATE FUNCTION [ schema_name. ] function_name

( [ {@parameter_name [ AS ][ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]

]

)

RETURNS return_data_type

[ WITH ＜function_option＞ [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

[ ; ]

参数说明和存储过程的类似，这里不详细说明。

(1) schema_name：用户定义函数所属架构的名称。

(2) function_name：用户定义函数的名称。

(3) @parameter_name：用户定义函数中的参数，可声明一个或多个参数，一个函数最多可以有2100个参数。

(4) [ type_schema_name.] parameter_data_type：参数的数据类型及其所属的架构。

(5) [ =default ]：参数的默认值。如果定义了 default 值，则无须指定此参数的值即可执行函数。

(6) READONLY：指示不能在函数定义中更新或修改参数。如果参数类型为用户定义的表类型，则应指定READONLY。

(7) function_body：指定一系列定义函数值的T-SQL语句。

(8) scalar_expression：指定标量函数返回的标量值。

【例9-26】定义一个求数的立方的用户定义函数，编写程序。

解：程序如下。

USE students;

GO

IF OBJECT_ID(N'dbo.Meters', N'FN') IS NOT NULL--标量函数的判断

DROP FUNCTION dbo.Meters;

GO

CREATE FUNCTION Meters(@x INT)

RETURNS INT

AS

BEGIN

SET @x=@x*@x*@x

RETURN @x

END

函数创建成功后，可以在T-SQL语句中允许使用标量表达式的任何位置调用返回标量值（与标量表达式的数据类型相同）的用户定义函数。必须使用至少由两部分组成名称的函数来调用标量值函数，即架构名.对象名。

例9-26的调用为select dbo. Meters (8)，具体执行结果如图9-37所示。

3. 用SQL语句创建和执行表值函数

表值函数就是返回 table 数据类型用户定义函数，它分为内联表值函数和多语句表值函数。对于内联表值函数，没有函数主体，表是单个 SELECT 语句的结果集。

1）创建内联表值函数

语法格式如下：

CREATE FUNCTION [ schema_name. ] function_name

( [ {@parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]

]

)

RETURNS TABLE

[ WITH ＜function_option＞ [ , …n ] ]

[ AS ]

RETURN [ ( ] select_stmt [ ) ]

[ ; ]

参数说明与存储过程的类似，这里不详细说明。

(1) TABLE：指定表值函数的返回值为表。只有常量和@local_variables可以传递到表值函数。

①在内联表值函数中，TABLE返回值是通过单个SELECT语句定义的。内联函数没有关联的返回变量。

②在多语句表值函数中，@return_variable是TABLE变量，用于存储和汇总应作为函数值返回的行。

(2) select_stmt：定义内联表值函数返回值的单个SELECT语句。

【例9-27】查询学生表（student）中不同性别的学生的信息，编写程序。

解：程序如下。

USE student;

GO

IF OBJECT_ID (N'f1', N'FN') IS NOT NULL——内联表值函数的判断

DROP FUNCTION stunumsex

GO

CREATE FUNCTION stunumsex (@a char(2))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM student WHERE sex=@a

)

GO

例9-27的调用为select * from stunumsex('男')，具体执行结果如图9-38所示。

注意：调用时不需指定架构名。

2）创建多语句表值函数

语法格式如下：

CREATE FUNCTION [ schema_name. ] function_name

( [ {@parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [READONLY] }

[ ,…n ]

]

)

RETURNS @return_variable TABLE ＜table_type_definition＞

[ WITH ＜function_option＞ [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

[ ; ]

【例9-28】按学号查询某个同学的各科成绩，编写程序。

解：程序如下。

USE students;

GO

IF OBJECT_ID (N's_course',N'TF') IS NOT NULL——多语句表值函数的判断

DROP FUNCTION s_course

GO

CREATE FUNCTION s_course (@sno char(10))

RETURNS @course table

(course_no char(10),

course_name char(20),

score int)

AS

BEGIN

INSERT @course

SELECT sc.cno,c.cname,grade

FROM enroll sc,course c

WHERE sc.cno=c.cno AND sc.sno=@sno

RETURN

END

GO

例9-28的调用为select * from s_course ('95001')，具体的执行结果如图9-39所示。