quoted

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

  数据库系列文章请见专栏：数据库基础_疯狂学习GIS的博客-CSDN博客。

  系列文章中示例数据来源于《SQL Server实验指导（2005版）》一书。尊重版权，因此遗憾不能将相关示例数据一并提供给大家；但是依据本系列文章的思想与对操作步骤、代码的详细解释，大家用自己手头的数据，可以将相关操作与分析过程加以完整重现。

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

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

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

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

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

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

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

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

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

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

USE jxskGOCREATE TABLE STUDENT(SNO IDNUM,SN CHAR(11),SSEX CHAR(2),SAGE TINYINT)GOCREATE TABLE 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 jxskGOALTER TABLE STUDENT ALTER COLUMN SN NAMEPERSONGOALTER TABLE TEACHER ALTER COLUMN TN NAMEPERSONGO

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

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

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

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

USE jxskGOALTER TABLE STUDENT ALTER COLUMN SN CHAR(10) NOT NULLGOALTER TABLE TEACHER ALTER COLUMN TN CHAR(10) NOT NULLGOEXEC sp_droptype NAMEPERSONGO

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

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

6 交互式删除数据类型Idnum

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

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

USE jxskGOALTER TABLE STUDENT ALTER COLUMN SNO CHAR(10) NOT NULLGOALTER TABLE TEACHER ALTER COLUMN TNO CHAR(10) NOT NULLGO

结果如下；

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

7 交互式创建标量函数Score_FUN

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

-- ================================================-- Template generated from Template Explorer using:-- Create Scalar Function (New Menu).SQL---- U 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date, ,>-- Description: <Description, ,>-- =============================================CREATE FUNCTION <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>ASBEGIN -- 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>ENDGO

（2） 将上述语句改为：

CREATE FUNCTION SCORE_FUN(@SNAME_IN CHAR(8),@CNAME_IN CHAR(10))RETURNS TINYINTASBEGINDECLARE @SCORE_OUT TINYINTSELECT @SCORE_OUT=SCORE FROM SC,S,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_INRETURN(@score_out)END

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

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

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

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

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

USE jxskGODECLARE @S_SCORE TINYINTEXEC @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 jxskGOCREATE FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))RETURNS TABLEASRETURN (SELECT CN,SCORE FROM S,SC,CWHERE 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 jxskGOSELECT*FROM S_SCORE_FUN('钱尔')GO

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

11 用SQL创建多语句函数ALL_Score_FUN

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

USE jxskGOCREATE FUNCTION ALL_SCORE_FUN(@CNAME_IN CHAR(10))RETURNS @ALL_SCORE_TAB TABLE(SNO CHAR(2) PRIMARY KEY,SN CHAR(8) NOT NULL,SEX CHAR(2),SCORE TINYINT)ASBEGININSERT @ALL_SCORE_TABSELECT S.SNO,SN,SEX,SCOREFROM S,SC,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND CN=@CNAME_INRETURNENDGO

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

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

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

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

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

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

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

USE jxskGOSELECT*FROM ALL_SCORE_FUN('数据库')GO

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

13 交互式修改函数Score_FUN

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

USE [jxsk]GO/****** Object: UrDefinedFunction [dbo].[SCORE_FUN] Script Date: 05/21/2019 19:34:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[SCORE_FUN](@SNAME_IN CHAR(8),@CNAME_IN CHAR(10))RETURNS TINYINTASBEGINDECLARE @SCORE_OUT TINYINTSELECT @SCORE_OUT=SCORE FROM SC,S,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_INRETURN(@score_out)END

（2） 将上述语句改为

ALTER FUNCTION SCORE_FUN(@SNAME_IN CHAR(10),@CNAME_IN CHAR(10))RETURNS CHAR(8)ASBEGINDECLARE @SCORE_OUT CHAR(8)SELECT @SCORE_OUT=CASEWHEN SCORE IS NULL THEN '未考'WHEN SCORE<60 THEN '不及格'WHEN SCORE>=60 AND SCORE<70 THEN '及格'WHEN SCORE>=70 AND SCORE<80 THEN '中'WHEN SCORE>=80 AND SCORE<90 THEN '良好'WHEN SCORE>=90 THEN '优秀'ENDFROM SC,S,CWHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN AND CN=@CNAME_INRETURN(@SCORE_OUT)END

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

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

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

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

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

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

15 用SQL修改函数S_Score_FUN

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

USE JXSKGOALTER FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))RETURNS TABLEASRETURN (SELECT CN,SCORE,LEVER=CASEWHEN SCORE IS NULL THEN '未考'WHEN SCORE<60 THEN '不及格'WHEN SCORE>=60 AND SCORE<70 THEN '及格'WHEN SCORE>=70 AND SCORE<80 THEN '中'WHEN SCORE>=80 AND SCORE<90 THEN '良好'WHEN SCORE>=90 THEN '优秀'ENDFROM S,SC,CWHERE 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 jxskGOSELECT*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 jxskDROP FUNCTION S_SCORE_FUNGO

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

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