基于SQL Server的数据库用户自定义数据类型与标量值、内嵌表值、多语句表值等用户定义函数的创建、使用及删除

疯狂学习GIS

知乎/公众号/CSDN/微博：疯狂学习GIS

数据库系列文章请见专栏： https:// blog.csdn.net/zhebushib iaoshifu/category_10838850.html 。

系列文章中示例数据来源于《 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
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
CREATE TABLE STUDENT(
SNO IDNUM,
SN CHAR(11),
SSEX CHAR(2),
SAGE TINYINT)
CREATE 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 jxsk
ALTER TABLE STUDENT ALTER COLUMN SN NAMEPERSON
ALTER TABLE TEACHER ALTER COLUMN TN NAMEPERSON
GO

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

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

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

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

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

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

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

6 交互式删除数据类型Idnum

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

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

USE jxsk




    

ALTER TABLE STUDENT ALTER COLUMN SNO CHAR(10) NOT NULL
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
SET QUOTED_IDENTIFIER ON
-- =============================================
-- 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>
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>
GO

（2）将上述语句改为：

CREATE FUNCTION SCORE_FUN(@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNS TINYINT
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
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
CREATE FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
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
SELECT*FROM S_SCORE_FUN('钱尔')
GO

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

11 用SQL创建多语句函数ALL_Score_FUN

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

USE jxsk
CREATE 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)
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
GO

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

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

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

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

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

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

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

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

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

13 交互式修改函数Score_FUN

USE [jxsk]
/****** Object:  UserDefinedFunction [dbo].[SCORE_FUN]    Script Date: 05/21/2019 19:34:43 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[SCORE_FUN](@SNAME_IN CHAR(8),
@CNAME_IN CHAR(10))
RETURNS TINYINT
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))
RETURNS CHAR(8)
BEGIN
DECLARE @SCORE_OUT CHAR(8)
SELECT @SCORE_OUT=
WHEN 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 '优秀'
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
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
ALTER FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
RETURN (SELECT CN,SCORE,
LEVER=
WHEN 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 '优秀'
FROM S,SC,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SN=@SNAME_IN)