SQL Server實戰七:自定義數據類型、標量值、內嵌表值、多語句表值函數的操作

来源:https://www.cnblogs.com/fkxxgis/p/18188783
-Advertisement-
Play Games

本文介紹基於Microsoft 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語句,如下圖;

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

  至此,大功告成。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 首先來看下什麼是漏桶演算法和令牌桶演算法 Nginx並不直接實現漏桶演算法或令牌桶演算法,但這些演算法在控制網路流量和請求速率方面非常有用。這些演算法通常在網路編程、API服務、負載均衡等領域中使用,以確保系統的穩定性和性能。 漏桶演算法(Leaky Bucket): * 漏桶演算法用於限制數據的傳輸速率。它可以將 ...
  • 文件IO 筆試題 作業:設計程式,獲取當前系統時間,把時間轉換為特定格式”yy年mm月dd日 星期x tt:mm:ss”,並每隔1s寫入到本地磁碟中一個叫做log.txt的文本中,如果文本不存在則創建。 代碼: /******************************************* ...
  • 目錄標準IO練習題題目:分析:代碼展示結果展示總結知識擴展time()函數localtime()函數 標準IO練習題 題目: 設計程式,獲取當前系統時間,把時間轉換為特定格式”yy年mm月dd日 星期x tt:mm:ss”,並每隔1s寫入到本地磁碟中一個叫做log.txt的文本中,如果文本不存在則創 ...
  • 目錄文件操作介面說明標準IO標準IO函數介紹打開文件:fopen()fopen使用相關知識補充關閉文件:fclose讀取數據字元讀取(fgetc)按行讀取按塊讀取寫入文件字元寫入字元串寫入按塊寫入讀取文件位置設置位移獲取位移格式訪問 文件操作介面說明 Linux系統為了簡化不同類型文件的操作流程,在 ...
  • 目錄電腦的組成部分硬體系統軟體系統文件系統Linux系統的目錄根文件系統說明目錄的結構分析Linux 的文件類型 電腦的組成部分 硬體系統 電腦的硬體主要是由五部分組成:控制器、運算器、存儲器、輸入設備、輸出設備。 運算器和控制器的總稱是中央處理器(CPU),指的是電腦中對信息進行高速運算處 ...
  • 目錄一、文本搜索工具--grep1、簡介2、工作原理3、語法格式4、選項介紹5、實例測試5.1、-i選項5.2、-v選項5.3、-n選項5.4、-c選項5.5、-o選項5.6、-B選項5.7、-A選項5.8、-C選項5.9、-w選項5.10、-E選項5.11、-e選項二、流編輯器--sed1、簡介2 ...
  • 目錄主要使用函數原型:實現過程中幾個易錯細節小結函數:每次讀寫一個字元函數:每次讀寫一行字元函數:每次讀寫一個塊字元 主要使用函數原型: 1.每次讀寫一個字元: int fgetc(FILE *stream); int fputc(int c, FILE *stream); 2.每次讀寫一行字元: ...
  • 問題現象 ps -ef | grep yas 查看無yasom和yasagent進程,且在{資料庫安裝目錄}/om/{資料庫名稱}的目錄下沒有conf、data、log等目錄,確定資料庫不是用yasboot安裝,是用腳本安裝的 問題的風險及影響 非yasboot安裝,ycm無法完成托管,無法監控 問 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...