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已被刪除；