實(shí)驗(yàn)11

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)建及使用方法?！局R(shí)要點(diǎn)】 1.用戶定義數(shù)據(jù)類型（1）為何使用用戶定義數(shù)據(jù)類型 當(dāng)一個(gè)或多個(gè)表的字段中要存儲(chǔ)同樣類型的數(shù)據(jù)，且想確保這些字段具有完全相同的數(shù)據(jù)類型、長度和是否為NULL時(shí)，可使用用戶定義數(shù)據(jù)類型。例如，學(xué)生號(hào)和教師號(hào)的數(shù)據(jù)類

2、型都是基于CHAR的有固定長度6個(gè)字符，且不為空。（2）用戶定義數(shù)據(jù)類型的特點(diǎn) 用戶定義的數(shù)據(jù)類型名稱在數(shù)據(jù)庫中必須是唯一的，但是名稱不同的用戶定義的數(shù)據(jù)類型可以有相同的定義。（3）創(chuàng)建用戶定義數(shù)據(jù)類型語句sp_addtype [@typename=] 類型， [@phystype=] 系統(tǒng)定義的數(shù)據(jù)類型 [,[@nulltype = ‘NULL|NOT NULL’]

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

4、 SQL Server2000有3種自定義函數(shù)：標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)和多語句表值函數(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、im

5、age、cursor 和 timestamp 之外的任何數(shù)據(jù)類型。不支持用戶定義數(shù)據(jù)類型和非標(biāo)量類型（如 table 或 cursor）。內(nèi)嵌表值函數(shù) 內(nèi)嵌表值函數(shù)返回table。沒有函數(shù)主體；表是單個(gè)SELECT語句的結(jié)果集。多語句表值函數(shù) 多語句表值函數(shù)返回table，在 BEGIN...END 塊中定義的函數(shù)主體包含 TRANSACT-SQL 語句，這些語句可生成行并將行插入將返回的表中。,,實(shí)驗(yàn)11 用戶定義

6、數(shù)據(jù)類型與自定義函數(shù),（3）創(chuàng)建用戶定義函數(shù)語句標(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    函

7、數(shù)體    RETURN 標(biāo)量表達(dá)式END內(nèi)嵌表值函數(shù)CREATE FUNCTION [ 屬主名.] 函數(shù)名    ( [ { @parameter_name [AS] 標(biāo)量參數(shù)數(shù)據(jù)類型 [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH [ [,] ...n ] ] [ AS ]RETU

8、RN [ ( ) select語句 ],,實(shí)驗(yàn)11 用戶定義數(shù)據(jù)類型與自定義函數(shù),多語句表值函數(shù)CREATE FUNCTION [屬主名.] 函數(shù)名    ( [ { @parameter_name [AS] 標(biāo)量參數(shù)數(shù)據(jù)類型 [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE [ WITH [ [,] ...n ]

9、 ] [ AS ]BEGIN    函數(shù)體    RETURNEND ::=    { ENCRYPTION | SCHEMABINDING } ::=     ( { column_definition | table_constraint } [ ,...n

10、 ] ) 【實(shí)驗(yàn)?zāi)康摹繉W(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語句創(chuàng)建一用戶定義的數(shù)據(jù)類型Idnum。要求：系統(tǒng)數(shù)據(jù)類型為char，長度為6，不為空。用于學(xué)號(hào)，教師號(hào)字段的數(shù)據(jù)類型；用企業(yè)管理器創(chuàng)建一用戶定義的數(shù)據(jù)類型Namep

11、erson。要求：系統(tǒng)數(shù)據(jù)類型char，長度為11，不為空。用于學(xué)生姓名，教師姓名字段的數(shù)據(jù)類型。,【實(shí)驗(yàn)步驟】 1.創(chuàng)建和使用一用戶定義的數(shù)據(jù)類型Idnum（1）用SQL語句創(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,在查詢窗口中輸入下面程

12、序，創(chuàng)建一個(gè)學(xué)生數(shù)據(jù)表STUDENT和一個(gè)教師表TEACHER：USE jxskCREATE 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））,用戶定義數(shù)據(jù)類型的

13、使用,,實(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和教師表中的教師姓名類型為Nameperso

14、n。,在查詢窗口中輸入下面程序，修改學(xué)生數(shù)據(jù)表STUDENT和教師表TEACHER ：USE jxskALTER TABLE STUDENTALTER COLUMN SN NamepersonALTER TABLE TEACHERALTER COLUMN TN Nameperson,用戶定義數(shù)據(jù)類型的使用,,實(shí)驗(yàn)11.2 刪除數(shù)據(jù)類型與自定義函數(shù),,【實(shí)驗(yàn)?zāi)康摹空莆帐褂孟到y(tǒng)存儲(chǔ)過程刪除用戶定義的數(shù)據(jù)類型；掌握使用企業(yè)管

15、理器刪除用戶定義的數(shù)據(jù)類型。【實(shí)驗(yàn)內(nèi)容】使用系統(tǒng)存儲(chǔ)過程刪除用戶定義的數(shù)據(jù)類型Nameperson；使用企業(yè)管理器刪除用戶定義的數(shù)據(jù)類型Idnum?！緦?shí)驗(yàn)步驟】 1.使用系統(tǒng)存儲(chǔ)過程刪除用戶定義的數(shù)據(jù)類型,在查詢窗口中輸入下面程序，先解除對(duì)用戶定義的數(shù)據(jù)類型Nameperson的使用，然后再刪除用戶定義的數(shù)據(jù)類型Nameperson： USE jxskALTER TABLE student ALTER COLUMN S

16、NO CHAR（6） NOT NULLALTER TABLE teacher ALTER COLUMN TNO CHAR（6） NOT NULLEXEC Sp_droptype NamepersonGO,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)嵌表值函數(shù)的方法；掌握創(chuàng)建多語句表值函數(shù)的方法。【實(shí)驗(yàn)內(nèi)容】創(chuàng)建一個(gè)標(biāo)量函數(shù)S

17、core_FUN，根據(jù)學(xué)生姓名和課程名，查詢成績；創(chuàng)建一個(gè)內(nèi)嵌函數(shù)S_Score_FUN，根據(jù)學(xué)生姓名，查詢?cè)撋羞x課的成績；創(chuàng)建一個(gè)多語句函數(shù)ALL_Score_FUN，根據(jù)課程名，查詢所有選擇該課程學(xué)生的成績信息，包括：學(xué)號(hào)SNO，姓名SN，性別SEX，成績SCROE。結(jié)果按成績降序排列。,,實(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è)

18、標(biāo)量函數(shù)Score_FUN,用戶定義函數(shù)命令,,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),在窗口“文本” 框中輸入下面SQL語句。CREATE FUNCTION Score_FUN( @SNAME_IN CHAR(11), @CNAME_IN CHAR(11) )RETURNS TINYINTASBEGIN DECLARE @SCORE_OUT TINYINT SELECT @SCORE_OUT=sco

19、re FROM sc,s,c WHERE S.sno=Sc.sno and S.cno=Sc.cno and Sn=@SNAME_IN and Cn=@CNAME_IN RETURN(@SCORE_OUT)END,用戶定義的函數(shù)對(duì)象,,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),（2）使用用戶定義的函數(shù)Score_FUN，查詢學(xué)生錢爾的編譯原理課程的成績,執(zhí)行用戶定義的函數(shù),,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶

20、定義函數(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é)生錢爾所有課程的成績,執(zhí)行函數(shù)查詢成績,,實(shí)驗(yàn)11.3 創(chuàng)建和使用用戶定義函數(shù),3.用SQL創(chuàng)建一個(gè)多語句函數(shù)（1）用SQL創(chuàng)建一個(gè)多語句函數(shù)ALL_Score_FUN （2）使用用戶定義的函數(shù)ALL_Score_FUN，查詢選擇微機(jī)原理課程的學(xué)生的成績,執(zhí)行函數(shù)查詢成

21、績,用SQL創(chuàng)建函數(shù),用戶定義函數(shù)命令,,實(shí)驗(yàn)11.4 修改用戶定義函數(shù),【實(shí)驗(yàn)?zāi)康摹空莆帐褂闷髽I(yè)管理器修改用戶定義的函數(shù)方法；掌握使用SQL修改用戶定義的函數(shù)方法。【實(shí)驗(yàn)內(nèi)容】用企業(yè)管理器修改函數(shù)Score_FUN，將成績轉(zhuǎn)換為等級(jí)輸出；用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對(duì)應(yīng)成績的等級(jí) ?！緦?shí)驗(yàn)步驟】 1.用企業(yè)管理器修改函數(shù) （1）用企業(yè)管理器修改 函數(shù)Score_FUN,在用戶

22、定義的函數(shù)的屬性窗口中的“文本”輸入框輸入下面T-SQL語句：CREATE FUNCTION Score_FUN( @SNAME_IN CHAR(10),@CNAME_IN CHAR(10) )RETURNS CHAR(8)ASBEGIN DECLARE @SCORE_OUT CHAR(8) SELECT @SCORE_OUT=CASEWHEN SCORE IS NULL THEN ‘未考’WHEN SCOR

23、E =60 AND SCORE =70 AND SCORE =80 AND SCORE = 90 THEN‘優(yōu)秀’ ENDFROM 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,,實(shí)驗(yàn)11.4 修改用戶定義函數(shù),（2）使用用戶定義的函數(shù)Score_FUN

24、，查詢學(xué)生錢爾的編譯原理課程的成績,執(zhí)行用戶定義函數(shù),,實(shí)驗(yàn)11.4 修改用戶定義函數(shù),2.用SQL修改函數(shù)（1）用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對(duì)應(yīng)成績的等級(jí),修改函數(shù)定義,（2）使用用戶定義的函數(shù)S_Score_FUN，查詢學(xué)生錢爾所有課程的成績,執(zhí)行函數(shù)查詢成績,,實(shí)驗(yàn)11.5 刪除用戶定義函數(shù),【實(shí)驗(yàn)?zāi)康摹空莆帐褂闷髽I(yè)管理器刪除用戶定義的函數(shù)方法；掌握使用SQL刪除用戶定義的函數(shù)方法?！緦?shí)驗(yàn)內(nèi)容】

25、用企業(yè)管理器刪除函數(shù)Score_FUN；用SQL刪除函數(shù)S_Score_FUN?！緦?shí)驗(yàn)步驟】1.用企業(yè)管理器刪除函數(shù)Score_FUN2.用SQL刪除函數(shù)S_Score_FUN,,實(shí)驗(yàn)11 習(xí)題,【實(shí)驗(yàn)題】實(shí)驗(yàn)內(nèi)容和要求 針對(duì)教學(xué)數(shù)據(jù)庫jiaoxuedb進(jìn)行如下實(shí)驗(yàn)：1.用SQL語句創(chuàng)建一用戶定義的數(shù)據(jù)類型AgeType。要求：系統(tǒng)數(shù)據(jù)類型為Tinyint，可為空，取值范圍：0～100，用于學(xué)生表和教師表中的年齡

26、字段和選課表中成績字段的數(shù)據(jù)類型。2.用企業(yè)管理器創(chuàng)建數(shù)據(jù)類型NameType。要求：系統(tǒng)數(shù)據(jù)類型為Varchar，長度10個(gè)字節(jié)，不為空，用于教師名、學(xué)生名、職稱字段的數(shù)據(jù)類型。3.創(chuàng)建一個(gè)函數(shù)，要求：根據(jù)學(xué)生姓名和課程名查詢?cè)撋撜n程的成績。4.創(chuàng)建一個(gè)函數(shù)，要求：根據(jù)教師姓名查詢?cè)摻處熕陶n程名、學(xué)生人數(shù)、平均成績、最高成績、最低成績。5.創(chuàng)建一個(gè)函數(shù)，要求：統(tǒng)計(jì)各系各職稱的總?cè)藬?shù)、男女人數(shù)、平均年齡。,【思考題】 1.