SQL Server實戰七：自定義資料型別、標量值、內嵌表值、多語句表值函式的操作

疯狂学习GIS發表於2024-05-13
SQLServer資料型別函式

  本文介紹基於Microsoft SQL Server軟體，實現資料庫使用者自定義資料型別的建立、使用與刪除，以及標量值、內嵌表值、多語句表值函式等使用者定義函式的建立、使用、刪除方法。

目錄
  • 1 用SQL語句建立一個使用者定義的資料型別Idnum
  • 2 使用Idnum建立學生表STUDENT與教師表TEACHER
  • 3 互動式建立一個使用者定義的資料型別Nameperson
  • 4 使用資料型別Nameperson修改資料庫表資料型別
  • 5 使用系統儲存過程刪除資料型別Nameperson
  • 6 互動式刪除資料型別Idnum
  • 7 互動式建立標量函式Score_FUN
  • 8 使用標量函式Score_FUN查詢資料庫表中資訊
  • 9 用SQL建立內嵌表值函式S_Score_FUN
  • 10 使用內嵌表值函式S_Score_FUN查詢資料庫表中資訊
  • 11 用SQL建立多語句函式ALL_Score_FUN
  • 12 使用多語句函式ALL_Score_FUN查詢資料庫表中資訊
  • 13 互動式修改函式Score_FUN
  • 14 使用函式Score_FUN查詢資料庫表中資訊
  • 15 用SQL修改函式S_Score_FUN
  • 16 使用函式S_Score_FUN查詢資料庫表中資訊
  • 17 互動式刪除函式Score_FUN
  • 18 用SQL刪除函式S_Score_FUN

  系列文章中示例資料來源於《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語句，如下圖；

image

（6） 在“物件資源管理器”中選擇“資料庫”→“jxsk”→“可程式設計性”→“型別”→“使用者定義資料型別”，可在其中看到資料型別Idnum已經存在，如下圖；

2 使用Idnum建立學生表STUDENT與教師表TEACHER

（1） 單擊螢幕上方 “工具欄”選單中的“新建查詢”按鈕，開啟“查詢編輯器”視窗，並在“查詢編輯器”視窗中輸入以下T-SQL語句：

USE jxsk
GO
CREATE TABLE STUDENT(
SNO IDNUM,
SN CHAR(11),
SSEX CHAR(2),
SAGE TINYINT)
GO
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
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, ,>
-- =============================================
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>
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
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)
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
ALTER FUNCTION [dbo].[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

（2） 將上述語句改為

ALTER FUNCTION SCORE_FUN(@SNAME_IN CHAR(10),@CNAME_IN CHAR(10))
RETURNS CHAR(8)
AS
BEGIN
DECLARE @SCORE_OUT CHAR(8)
SELECT @SCORE_OUT=
CASE
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 '優秀'
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
ALTER FUNCTION S_SCORE_FUN(@SNAME_IN CHAR(8))
RETURNS TABLE
AS
RETURN (SELECT CN,SCORE,
LEVER=
CASE
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 '優秀'
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已被刪除；

  至此，大功告成。

相關文章