在SQL Server中,表值函数是一种返回表类型数据的函数。本文将介绍两种类型的表值函数:内联表值函数(Inline Table-Valued Functions)和多语句表值函数(Multi-Statement Table-Valued Functions,简称MSTVF)。在尝试创建这些函数之前,请确保SQL Server已经进行了适当的备份。
首先,将创建一些虚拟数据,这些数据将用于创建表值函数。
CREATE DATABASE schooldb;
USE schooldb;
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
DOB datetime NOT NULL,
total_score INT NOT NULL
);
INSERT INTO student VALUES
(1, 'Jolly', 'Female', '12-JUN-1989', 500),
(2, 'Jon', 'Male', '02-FEB-1974', 545),
(3, 'Sara', 'Female', '07-MAR-1988', 600),
(4, 'Laura', 'Female', '22-DEC-1981', 400),
(5, 'Alan', 'Male', '29-JUL-1993', 500),
(6, 'Kate', 'Female', '03-JAN-1985', 500),
(7, 'Joseph', 'Male', '09-APR-1982', 643),
(8, 'Mice', 'Male', '16-AUG-1974', 543),
(9, 'Wise', 'Male', '11-NOV-1987', 499),
(10, 'Elis', 'Female', '28-OCT-1990', 400);
内联表值函数返回单个SELECT语句的结果。在创建这类函数时,不需要使用BEGIN和END语句,因为它们只包含一条语句。下面是一个创建内联表值函数的示例。
CREATE FUNCTION BornBefore (@DOB DATETIME)
RETURNS TABLE
AS
RETURN
SELECT * FROM student WHERE DOB < @DOB;
在这个示例中,创建了一个名为"BornBefore"的函数,它接受一个名为@DOB的参数。这个函数返回所有出生日期小于@DOB参数值的学生记录。
要查看这个函数在数据库中的位置,可以转到对象资源管理器 -> 数据库 -> schooldb -> 程序性 -> 函数 -> 表值函数。在这里,将找到刚刚创建的函数。如果展开该函数,将看到函数接受的参数。
执行内联表值函数非常简单,只需要使用模式名称调用它即可。下面是一个查询示例,它通过"BornBefore"函数检索学生的姓名、性别和出生日期。
USE schooldb;
SELECT name, gender, DOB FROM dbo.BornBefore('1980-01-01') ORDER BY DOB;
在上面的脚本中,调用了"BornBefore"函数,并为函数传递了参数'1980-01-01'。这意味着函数将返回所有出生日期在1980年1月1日之前的学生记录。
要修改现有函数,使用ALTER关键字而不是CREATE。下面的脚本演示了如何修改"BornBefore"函数,使其接受两个datetime类型的参数,并返回出生日期在这两个参数值之间的学生记录。
ALTER FUNCTION BornBefore (@YearAfter DATETIME, @YearBefore DATETIME)
RETURNS TABLE
AS
RETURN
SELECT * FROM student WHERE DOB BETWEEN @YearAfter AND @YearBefore;
现在,当调用这个函数时,需要为两个datetime参数传递值。
USE schooldb;
SELECT name, gender, DOB FROM dbo.BornBefore('1980-01-01', '1990-12-31') ORDER BY DOB;
与内联表值函数不同,多语句表值函数可以返回多个复杂查询的结果。这些函数非常重要,因为可以在函数内执行多个复杂查询,并返回这些查询的聚合结果。
在创建MSTVF之前,先创建另一个名为"teacher"的表,并添加一些虚拟数据。
CREATE TABLE teacher (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
DOB datetime NOT NULL
);
INSERT INTO teacher VALUES
(1, 'Rick', 'Male', '05-APR-1965'),
(2, 'Shack', 'Male', '03-JUN-1972'),
(3, 'Zack', 'Male', '04-MAR-1969'),
(4, 'Elis', 'Female', '28-NOV-1959'),
(5, 'Mint', 'Female', '29-DEC-1971');
现在,有两个表:student和teacher,每个表中都包含一些虚拟数据。将创建一个MSTVF,该函数将从这两个表中检索记录。函数将有两个datetime类型的参数。从student和teacher表中检索的记录的出生日期将在传递给函数的参数值之间。
CREATE FUNCTION GetBornBetween (@YearAfter DATETIME, @YearBefore DATETIME)
RETURNS @People TABLE (
Name VARCHAR(MAX),
Gender VARCHAR(MAX),
DOB DATETIME,
Job VARCHAR(10)
)
AS
BEGIN
INSERT INTO @People
SELECT name, gender, DOB, 'student' FROM student WHERE DOB BETWEEN @YearAfter AND @YearBefore;
INSERT INTO @People
SELECT name, gender, DOB, 'teacher' FROM teacher WHERE DOB BETWEEN @YearAfter AND @YearBefore;
RETURN;
END;
在上面的脚本中,创建了一个名为"GetBornBetween"的函数和一个名为"@People"的表变量,该表变量包含四个字段:Name、Gender、DOB和Job。然后,使用SELECT语句将student表中的记录插入到@People表变量中。接下来,执行了另一个SELECT语句,这次从teacher表中检索记录,并将它们存储在@People变量中。第四列(Job)的值是硬编码的。在student表的SELECT语句中,将这个值设置为"student"。在teacher表的SELECT语句中,这个值被设置为"teacher"。最后,使用RETURN关键字返回@People变量中的值。
USE schooldb;
SELECT * FROM dbo.GetBornBetween('1960-01-01', '1985-12-31');
Name | Gender | DOB | Job |
---|---|---|---|
Jon | Male | 1974-02-02 00:00:00.000 | student |
Laura | Female | 1981-12-22 00:00:00.000 | student |
Kate | Female | 1985-01-03 00:00:00.000 | student |
Joseph | Male | 1982-04-09 00:00:00.000 | student |
Mice | Male | 1974-08-16 00:00:00.000 | student |
Rick | Male | 1965-04-05 00:00:00.000 | teacher |
Shack | Male | 1972-06-03 00:00:00.000 | teacher |
Zack | Male | 1969-03-04 00:00:00.000 | teacher |
Mint | Female | 1971-12-29 00:00:00.000 | teacher |