1、,實(shí)驗(yàn)11 用戶定義數(shù)據(jù)類型與自定義函數(shù),本實(shí)驗(yàn)包含兩方面的內(nèi)容：一個(gè)是學(xué)習(xí)掌握用戶定義數(shù)據(jù)類型的特點(diǎn)及定義方法，以及如何使用用戶定義數(shù)據(jù)類型；另一個(gè)是學(xué)習(xí)掌握用戶自定義函數(shù)的概念、創(chuàng)建及使用方法。 【知識(shí)要點(diǎn)】 1.用戶定義數(shù)據(jù)類型 （1）為何使用用戶定義數(shù)據(jù)類型 當(dāng)一個(gè)或多個(gè)表的字段中要存儲(chǔ)同樣類型的數(shù)據(jù)，且想確保這些字段具有完全相同的數(shù)據(jù)類型、長(zhǎng)度和是否為NULL時(shí)，可使用用戶定義數(shù)據(jù)類型。例如，學(xué)生號(hào)和教師號(hào)的數(shù)據(jù)類型都是基于CHAR的有固定長(zhǎng)度6個(gè)字符，且不為空。 （2）用戶定義數(shù)據(jù)類型的特點(diǎn) 用戶定義的數(shù)據(jù)類型名稱在數(shù)據(jù)庫(kù)中必須是唯一的，但是名稱不同的用戶定義的數(shù)據(jù)類型可以有相同的

2、定義。 （3）創(chuàng)建用戶定義數(shù)據(jù)類型語(yǔ)句 sp_addtype typename= 類型， phystype= 系統(tǒng)定義的數(shù)據(jù)類型 ,nulltype = NULL|NOT NULL ,owner = 屬主名,實(shí)驗(yàn)11 用戶定義數(shù)據(jù)類型與自定義函數(shù),2.用戶定義函數(shù)簡(jiǎn)介 （1）為何使用用戶定義函數(shù) 用戶定義函數(shù)可以讓你針對(duì)特定應(yīng)用程序問(wèn)題提供解決方案。這些函數(shù)可以簡(jiǎn)單到計(jì)算一個(gè)值，也可以復(fù)雜到定義和實(shí)現(xiàn)業(yè)務(wù)處理規(guī)則。定義了這些函數(shù)以后，在需要的時(shí)候調(diào)用即可，如果業(yè)務(wù)處理的規(guī)則發(fā)生變化，只需要修改相應(yīng)的函數(shù)，只要這些接口未發(fā)生變化，就可以保持原來(lái)的函數(shù)調(diào)用。 （2）用戶定義函數(shù)的類型 SQL Se

3、rver2000有3種自定義函數(shù)：標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)和多語(yǔ)句表值函數(shù)。 用戶定義函數(shù)采用零個(gè)或更多的輸入?yún)?shù)并返回標(biāo)量值或表。函數(shù)最多可以有 1124 個(gè)輸入?yún)?shù)。當(dāng)函數(shù)的參數(shù)有默認(rèn)值時(shí)，調(diào)用該函數(shù)時(shí)必須指定默認(rèn) DEFAULT 關(guān)鍵字才能獲取默認(rèn)值。用戶定義函數(shù)不支持輸出參數(shù)。 標(biāo)量函數(shù) 標(biāo)量函數(shù)返回在 RETURNS 子句中定義的類型的單個(gè)數(shù)據(jù)值。返回類型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何數(shù)據(jù)類型。不支持用戶定義數(shù)據(jù)類型和非標(biāo)量類型（如 table 或 cursor）。 內(nèi)嵌表值函數(shù) 內(nèi)嵌表值函數(shù)返回table。沒(méi)有函數(shù)主體；表

4、是單個(gè)SELECT語(yǔ)句的結(jié)果集。 多語(yǔ)句表值函數(shù) 多語(yǔ)句表值函數(shù)返回table，在 BEGIN.END 塊中定義的函數(shù)主體包含 TRANSACT-SQL 語(yǔ)句，這些語(yǔ)句可生成行并將行插入將返回的表中。,實(shí)驗(yàn)11 用戶定義數(shù)據(jù)類型與自定義函數(shù),（3）創(chuàng)建用戶定義函數(shù)語(yǔ)句 標(biāo)量函數(shù) CREATE FUNCTION 屬主名. 函數(shù)名(parameter_name AS 標(biāo)量參數(shù)數(shù)據(jù)類型 =default ,.n ) RETURNS 標(biāo)量返回?cái)?shù)據(jù)類型 WITH , .n AS BEGIN函數(shù)體RETURN 標(biāo)量表達(dá)式END 內(nèi)嵌表值函數(shù) CREATE FUNCTION 屬主名. 函數(shù)名( parame

5、ter_name AS 標(biāo)量參數(shù)數(shù)據(jù)類型 = default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( ) select語(yǔ)句 ,實(shí)驗(yàn)11 用戶定義數(shù)據(jù)類型與自定義函數(shù),多語(yǔ)句表值函數(shù) CREATE FUNCTION 屬主名. 函數(shù)名( parameter_name AS 標(biāo)量參數(shù)數(shù)據(jù)類型 = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN函數(shù)體RETURNEND := ENCRYPTION | SCHEMABINDING := ( column_definition | ta

6、ble_constraint ,.n ) 【實(shí)驗(yàn)?zāi)康摹?學(xué)習(xí)和掌握用戶定義數(shù)據(jù)類型的概念、創(chuàng)建及使用方法； 學(xué)習(xí)和掌握用戶定義函數(shù)的概念、創(chuàng)建及使用方法。,實(shí)驗(yàn)11.1 創(chuàng)建和使用用戶定義數(shù)據(jù)類型,【實(shí)驗(yàn)?zāi)康摹?掌握創(chuàng)建用戶定義數(shù)據(jù)類型的方法； 掌握用戶定義數(shù)據(jù)類型的使用。 【實(shí)驗(yàn)內(nèi)容】 用SQL語(yǔ)句創(chuàng)建一用戶定義的數(shù)據(jù)類型Idnum。要求：系統(tǒng)數(shù)據(jù)類型為 char，長(zhǎng)度為6，不為空。用于學(xué)號(hào)，教師號(hào)字段的數(shù)據(jù)類型； 用企業(yè)管理器創(chuàng)建一用戶定義的數(shù)據(jù)類型Nameperson。要求：系統(tǒng)數(shù) 據(jù)類型char，長(zhǎng)度為11，不為空。用于學(xué)生姓名，教師姓名字段的數(shù)據(jù)類型。,【實(shí)驗(yàn)步驟】 1.創(chuàng)建和使用一

7、用戶定義的數(shù)據(jù)類型Idnum （1）用SQL語(yǔ)句創(chuàng)建一個(gè)用戶定義的數(shù)據(jù)類型Idnum。,T-SQL程序設(shè)計(jì)及 執(zhí)行,用戶定義的數(shù)據(jù)類型 對(duì)象,實(shí)驗(yàn)11.1 創(chuàng)建和使用用戶定義數(shù)據(jù)類型,（2）使用用戶定義的數(shù)據(jù)類型Idnum ，創(chuàng)建一個(gè)學(xué)生表STUDENT和一個(gè)教師表TEACHER,在查詢窗口中輸入下面程序，創(chuàng)建一個(gè)學(xué)生數(shù)據(jù)表STUDENT和一個(gè)教師表TEACHER： USE jxsk CREATE TABLE STUDENT（ SNO Idnum ， SN CHAR（11）， SSEX CHAR（2）， SAGE TINYINT ） CREATE TABLE TEACHER（ TNO Idnu

8、m ， TN CHAR（11）， TSEX CHAR（2）， TAGE TINYINT ， TPROF CHAR（11） ）,用戶定義數(shù)據(jù)類型的使用,實(shí)驗(yàn)11.1 創(chuàng)建和使用用戶定義數(shù)據(jù)類型,2.創(chuàng)建一用戶定義的數(shù)據(jù)類型Nameperson （1）用企業(yè)管理器創(chuàng)建一個(gè)用戶定義的數(shù)據(jù)類型Nameperson,選擇用戶定義數(shù)據(jù)類型,創(chuàng)建用戶定義的數(shù)據(jù)類型對(duì)話框,已定義的用戶定義數(shù)據(jù)類型,實(shí)驗(yàn)11.1 創(chuàng)建和使用用戶定義數(shù)據(jù)類型,（2）使用用戶定義的數(shù)據(jù)類型Nameperson。修改學(xué)生表STUDENT中的姓名類型為Nameperson和教師表中的教師姓名類型為Nameperson。,在查詢窗口中輸

9、入下面程序，修改學(xué)生數(shù)據(jù)表STUDENT和教師表TEACHER ： USE jxsk ALTER TABLE STUDENT ALTER COLUMN SN Nameperson ALTER TABLE TEACHER ALTER COLUMN TN Nameperson,用戶定義數(shù)據(jù)類型的使用,實(shí)驗(yàn)11.2 刪除數(shù)據(jù)類型與自定義函數(shù),【實(shí)驗(yàn)?zāi)康摹?掌握使用系統(tǒng)存儲(chǔ)過(guò)程刪除用戶定義的數(shù)據(jù)類型； 掌握使用企業(yè)管理器刪除用戶定義的數(shù)據(jù)類型。 【實(shí)驗(yàn)內(nèi)容】 使用系統(tǒng)存儲(chǔ)過(guò)程刪除用戶定義的數(shù)據(jù)類型Nameperson； 使用企業(yè)管理器刪除用戶定義的數(shù)據(jù)類型Idnum。 【實(shí)驗(yàn)步驟】 1.使用系統(tǒng)存儲(chǔ)過(guò)

10、程刪除用戶定義的數(shù)據(jù)類型,在查詢窗口中輸入下面程序，先解除對(duì)用戶定義的數(shù)據(jù)類型Nameperson的使用，然后再刪除用戶定義的數(shù)據(jù)類型Nameperson： USE jxsk ALTER TABLE student ALTER COLUMN SNO CHAR（6） NOT NULL ALTER TABLE teacher ALTER COLUMN TNO CHAR（6） NOT NULL EXEC Sp_droptype Nameperson GO,2使用企業(yè)管理器刪除用戶定義的數(shù)據(jù)類型Idnum,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),【實(shí)驗(yàn)?zāi)康摹?掌握創(chuàng)建標(biāo)量函數(shù)的創(chuàng)建方法； 掌握創(chuàng)建內(nèi)嵌表

11、值函數(shù)的方法； 掌握創(chuàng)建多語(yǔ)句表值函數(shù)的方法。 【實(shí)驗(yàn)內(nèi)容】 創(chuàng)建一個(gè)標(biāo)量函數(shù)Score_FUN，根據(jù)學(xué)生姓名和課程名，查詢成績(jī)； 創(chuàng)建一個(gè)內(nèi)嵌函數(shù)S_Score_FUN，根據(jù)學(xué)生姓名，查詢?cè)撋羞x課的 成績(jī)； 創(chuàng)建一個(gè)多語(yǔ)句函數(shù)ALL_Score_FUN，根據(jù)課程名，查詢所有選擇該課 程學(xué)生的成績(jī)信息，包括：學(xué)號(hào)SNO，姓名SN，性別SEX，成績(jī)SCROE。結(jié)果按成績(jī)降序排列。,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),【實(shí)驗(yàn)步驟】 1.用企業(yè)管理器創(chuàng)建一個(gè)標(biāo)量函數(shù)Score_FUN （1）用企業(yè)管理器創(chuàng)建一個(gè)標(biāo)量函數(shù)Score_FUN,用戶定義函數(shù)命令,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),

12、在窗口“文本” 框中輸入下面SQL語(yǔ)句。 CREATE FUNCTION Score_FUN( SNAME_IN CHAR(11), CNAME_IN CHAR(11) ) RETURNS TINYINT AS BEGIN DECLARE SCORE_OUT TINYINT SELECT SCORE_OUT=score FROM sc,s,c WHERE S.sno=Sc.sno and S.cno=So and Sn=SNAME_IN and Cn=CNAME_IN RETURN(SCORE_OUT) END,用戶定義的函數(shù)對(duì)象,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),（2）使用用戶定義的函數(shù)

13、Score_FUN，查詢學(xué)生錢(qián)爾的編譯原理課程的成績(jī),執(zhí)行用戶定義的函數(shù),實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),2.用SQL創(chuàng)建一個(gè)內(nèi)嵌函數(shù) （1）用SQL創(chuàng)建一個(gè)內(nèi)嵌函數(shù)S_Score_FUN,用SQL創(chuàng)建函數(shù),（2）使用用戶定義的函數(shù)S_Score_FUN，查詢學(xué)生錢(qián)爾所有課程的成績(jī),執(zhí)行函數(shù)查詢成績(jī),實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),3.用SQL創(chuàng)建一個(gè)多語(yǔ)句函數(shù) （1）用SQL創(chuàng)建一個(gè)多語(yǔ)句函數(shù)ALL_Score_FUN （2）使用用戶定義的函數(shù)ALL_Score_FUN，查詢選擇微機(jī)原理課程的學(xué)生的成績(jī),執(zhí)行函數(shù)查詢成績(jī),用SQL創(chuàng)建函數(shù),用戶定義函數(shù)命令,實(shí)驗(yàn)11.4 修改用戶

14、定義函數(shù),【實(shí)驗(yàn)?zāi)康摹?掌握使用企業(yè)管理器修改用戶定義的函數(shù)方法； 掌握使用SQL修改用戶定義的函數(shù)方法。 【實(shí)驗(yàn)內(nèi)容】 用企業(yè)管理器修改函數(shù)Score_FUN，將成績(jī)轉(zhuǎn)換為等級(jí)輸出； 用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對(duì)應(yīng)成績(jī)的等級(jí) 。 【實(shí)驗(yàn)步驟】 1.用企業(yè)管理器修改函數(shù) （1）用企業(yè)管理器修改 函數(shù)Score_FUN,在用戶定義的函數(shù)的屬性窗口中的“文本”輸入框輸入下面T-SQL語(yǔ)句： CREATE FUNCTION Score_FUN( SNAME_IN CHAR(10),CNAME_IN CHAR(10) ) RETURNS CHAR(8) AS BEGIN

15、DECLARE SCORE_OUT CHAR(8) SELECT SCORE_OUT= CASE WHEN SCORE IS NULL THEN 未考 WHEN SCORE =60 AND SCORE =70 AND SCORE =80 AND SCORE = 90 THEN優(yōu)秀 END FROM sc,s,c WHERE s.sno=sc.sno and o=o and Sn=SNAME_IN and Cn=CNAME_IN RETURN(SCORE_OUT) END,實(shí)驗(yàn)11.4 修改用戶定義函數(shù),（2）使用用戶定義的函數(shù)Score_FUN，查詢學(xué)生錢(qián)爾的編譯原理課程的成績(jī),執(zhí)行用戶定義函

16、數(shù),實(shí)驗(yàn)11.4 修改用戶定義函數(shù),2.用SQL修改函數(shù) （1）用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對(duì)應(yīng)成績(jī)的等級(jí),修改函數(shù)定義,（2）使用用戶定義的函數(shù)S_Score_FUN，查詢學(xué)生錢(qián)爾所有課程的成績(jī),執(zhí)行函數(shù)查詢成績(jī),實(shí)驗(yàn)11.5 刪除用戶定義函數(shù),【實(shí)驗(yàn)?zāi)康摹?掌握使用企業(yè)管理器刪除用戶定義的函數(shù)方法； 掌握使用SQL刪除用戶定義的函數(shù)方法。 【實(shí)驗(yàn)內(nèi)容】 用企業(yè)管理器刪除函數(shù)Score_FUN； 用SQL刪除函數(shù)S_Score_FUN。 【實(shí)驗(yàn)步驟】 1.用企業(yè)管理器刪除函數(shù)Score_FUN 2.用SQL刪除函數(shù)S_Score_FUN,實(shí)驗(yàn)11 習(xí)題,【實(shí)驗(yàn)題】 實(shí)驗(yàn)內(nèi)容和要求 針對(duì)教學(xué)數(shù)據(jù)庫(kù)jiaoxuedb進(jìn)行如下實(shí)驗(yàn)： 1.用SQL語(yǔ)句創(chuàng)建一用戶定義的數(shù)據(jù)類型AgeType。要求：系統(tǒng)數(shù)據(jù)類型為T(mén)inyint，可為空，取值范圍：0100，用于學(xué)生表和教師表中的年齡字段和選課表中成績(jī)字段的數(shù)據(jù)類型。 2.用企業(yè)管理器創(chuàng)建數(shù)據(jù)類型NameType。要求：系統(tǒng)數(shù)據(jù)類型為Varc