SQL Server自定义数据类型与用户定义函数操作

  本文介绍基于Microsoft SQL Server软件，实现数据库用户自定义数据类型的创建、使用与删除，以及标量值、内嵌表值、多语句表值函数等用户定义函数的创建、使用、删除方法。

  系列文章中示例数据来源于《SQL Server实验指导（2005版）》一书。大家用自己手头的数据，可以将相关操作与分析过程加以完整重现。

1 用SQL语句创建一个用户定义的数据类型Idnum

（1） 启动Microsoft SQL Server 2008 R2软件；

（2） 在“对象资源管理器”窗格中，在“数据库”处右键，在弹出的菜单中选择“附加”选项；

（3） 选择需要加以附加的jxsk数据库物理文件，选择定位文件夹“G:\sql\chutianjia sql”并选择对应数据库jxsk的物理文件并选择“确定”按钮，再次选择“确定”即可；

（4） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
EXEC sp_addtype Idnum,'CHAR(6)','NOT NULL'
GO

（5） 单击“工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（6） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”→“用户定义数据类型”，可在其中看到数据类型Idnum已经存在，如下图；

2 使用Idnum创建学生表STUDENT与教师表TEACHER

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
CREATETABLE STUDENT(
SNO IDNUM,
SN CHAR(11),
SSEX CHAR(2),
SAGE TINYINT)
GO
CREATETABLE TEACHER(
TNO IDNUM,
TN CHAR(11),
TSEX CHAR(2),
TAGE TINYINT,
TPROF CHAR(11))
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”，选择学生表STUDENT与教师表TEACHER，看到相应字段及其定义Idnum，如下图；

3 交互式创建一个用户定义的数据类型Nameperson

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”，右击“用户定义数据类型”，在弹出的窗口中选择“新建用户定义数据类型”，如下图；

（2） 正确配置相关选项，选择正确的名称、数据类型与长度，点击“确定”；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”→“用户定义数据类型”，可看到数据类型Nameperson的定义；

4 使用数据类型Nameperson修改数据库表数据类型

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SN NAMEPERSON
GO
ALTER TABLE TEACHER ALTER COLUMN TN NAMEPERSON
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句,如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”，查看数据库表学生表STUDENT与教师表TEACHER相关列的定义已随之改变；

5 使用系统存储过程删除数据类型Nameperson

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SN CHAR(10) NOT NULL
GO
ALTER TABLE TEACHER ALTER COLUMN TN CHAR(10) NOT NULL
GO
EXEC sp_droptype NAMEPERSON
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”→“用户定义数据类型”，可看到数据类型Nameperson已经不存在；

6 交互式删除数据类型Idnum

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”→“用户定义数据类型”，右击后选择“删除”，选择“确定”；

（2） 发现删除出现问题，认为是由于数据库表中有列仍然在使用这一数据结构，故需先将上述数据结构从表中移除再进行删除操作，输入的SQL语言为：

USE jxsk
GO
ALTER TABLE STUDENT ALTER COLUMN SNO CHAR(10) NOT NULL
GO
ALTER TABLE TEACHER ALTER COLUMN TNO CHAR(10) NOT NULL
GO

结果如下；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“类型”→“用户定义数据类型”，发现数据类型Idnum已不再存在；

7 交互式创建标量函数Score_FUN

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“标量值函数”，右击并在弹出的窗口中选择“新建标量值函数”，打开的窗口包含模板语句如下：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATEFUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
-- Add the parameters for the function here
 <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>

END
GO

（2） 将上述语句改为：

CREATE FUNCTION SCORE_FUN(@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNS TINYINT
AS
BEGIN
DECLARE @SCORE_OUT TINYINT
SELECT @SCORE_OUT=SCORE FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@score_out)
END

（3） 单击“分析”对语句加以语法检查，如下图；单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（4） 第一次输入语句有误，更正后如下；

（5） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“标量值函数”，可看到已建立的标量函数Score_FUN；

8 使用标量函数Score_FUN查询数据库表中信息

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DECLARE @S_SCORE TINYINT
EXEC @S_SCORE=DBO.SCORE_FUN '钱尔','编译原理'
PRINT'钱尔的编译原理成绩是'+STR(@S_SCORE)
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 第一次语句输入有误，没有将汉语语句输入进去，从而在最终结果出现错误；

（4） 随后对语句加以更正，结果恢复正常；

9 用SQL创建内嵌表值函数S_Score_FUN

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
CREATE FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
AS
RETURN (SELECT CN,SCORE FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN)
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“表值函数”，可看到已建立的内嵌表值函数S_Score_FUN；

10 使用内嵌表值函数S_Score_FUN查询数据库表中信息

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
SELECT*FROM S_SCORE_FUN('钱尔')
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

11 用SQL创建多语句函数ALL_Score_FUN

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
CREATEFUNCTION ALL_SCORE_FUN(@CNAME_IN CHAR(10))
RETURNS @ALL_SCORE_TAB TABLE(SNO CHAR(2) PRIMARY KEY,
SN CHAR(8) NOTNULL,SEX CHAR(2),SCORE TINYINT)
AS
BEGIN
INSERT @ALL_SCORE_TAB
SELECT S.SNO,SN,SEX,SCORE
FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CN=@CNAME_IN
RETURN
END
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“表值函数”，可看到已建立的多语句函数ALL_Score_FUN；

12 使用多语句函数ALL_Score_FUN查询数据库表中信息

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
SELECT*FROM ALL_SCORE_FUN('微机原理')
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 能看到我的结果是空白。检查语句发现并没有错误，则返回原有数据库表对数据加以检查，发现我的数据库表中确实没有微机原理的相关数据，所以考虑更换语句为

USE jxsk
GO
SELECT*FROM ALL_SCORE_FUN('数据库')
GO

（4） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

13 交互式修改函数Score_FUN

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“标量值函数”，右击并在弹出的窗口中选择“新建标量值函数”，打开的窗口包含模板语句如下：

USE [jxsk]
GO
/****** Object:  UserDefinedFunction [dbo].[SCORE_FUN]    Script Date: 05/21/2019 19:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERFUNCTION [dbo].[SCORE_FUN](@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNSTINYINT
AS
BEGIN
DECLARE @SCORE_OUT TINYINT
SELECT @SCORE_OUT=SCORE FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@score_out)
END

（2） 将上述语句改为

ALTER FUNCTION SCORE_FUN(@SNAME_IN CHAR(10),@CNAME_IN CHAR(10))
RETURNSCHAR(8)
AS
BEGIN
DECLARE @SCORE_OUT CHAR(8)
SELECT @SCORE_OUT=
CASE
WHEN SCORE ISNULLTHEN'未考'
WHEN SCORE<60THEN'不及格'
WHEN SCORE>=60AND SCORE<70THEN'及格'
WHEN SCORE>=70AND SCORE<80THEN'中'
WHEN SCORE>=80AND SCORE<90THEN'良好'
WHEN SCORE>=90THEN'优秀'
END
FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_IN
RETURN(@SCORE_OUT)
END

（3） 单击“分析”对语句加以语法检查，如下图； 检查后发现语句输入有误，对其加以回顾找出所存在错误并加以修改，再次进行语法检查如下下图； 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下下下图；

（4） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“标量值函数”→“dbo.Score_FUN”→“参数”节点，查看其参数变化；

14 使用函数Score_FUN查询数据库表中信息

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
DECLARE @S_SCORE CHAR(8)
EXEC @S_SCORE=DBO.SCORE_FUN '钱尔','编译原理'
PRINT'钱尔的编译原理成绩是'+@S_SCORE
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

15 用SQL修改函数S_Score_FUN

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE JXSK
GO
ALTERFUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNSTABLE
AS
RETURN (SELECT CN,SCORE,
LEVER=
CASE
WHEN SCORE ISNULLTHEN'未考'
WHEN SCORE<60THEN'不及格'
WHEN SCORE>=60AND SCORE<70THEN'及格'
WHEN SCORE>=70AND SCORE<80THEN'中'
WHEN SCORE>=80AND SCORE<90THEN'良好'
WHEN SCORE>=90THEN'优秀'
END
FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN)
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

16 使用函数S_Score_FUN查询数据库表中信息

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
GO
SELECT*FROM S_SCORE_FUN('钱尔')
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

17 交互式删除函数Score_FUN

（1） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“标量值函数”→“dbo.Score_FUN”并右击，在弹出的窗口中选择“删除”选项；

（2） 在弹出的“删除对象”窗口中选择“确定”选项，函数Score_FUN即被删除；

18 用SQL删除函数S_Score_FUN

（1） 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮，打开“查询编辑器”窗口，并在“查询编辑器”窗口中输入以下T-SQL语句：

USE jxsk
DROP FUNCTION S_SCORE_FUN
GO

（2） 单击 “工具栏”中的“执行(x)”按钮，即可执行上述T-SQL语句，如下图；

（3） 在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“函数”→“表值函数”节点可看到函数S_Score_FUN已被删除；

  至此，大功告成。