實驗11

1、,實驗11 用戶定義數(shù)據類型與自定義函數(shù),,本實驗包含兩方面的內容：一個是學習掌握用戶定義數(shù)據類型的特點及定義方法，以及如何使用用戶定義數(shù)據類型；另一個是學習掌握用戶自定義函數(shù)的概念、創(chuàng)建及使用方法?！局R要點】 1.用戶定義數(shù)據類型（1）為何使用用戶定義數(shù)據類型 當一個或多個表的字段中要存儲同樣類型的數(shù)據，且想確保這些字段具有完全相同的數(shù)據類型、長度和是否為NULL時，可使用用戶定義數(shù)據類型。例如，學生號和教師號的數(shù)據類

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

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

4、 SQL Server2000有3種自定義函數(shù)：標量函數(shù)、內嵌表值函數(shù)和多語句表值函數(shù)。 用戶定義函數(shù)采用零個或更多的輸入參數(shù)并返回標量值或表。函數(shù)最多可以有 1124 個輸入參數(shù)。當函數(shù)的參數(shù)有默認值時，調用該函數(shù)時必須指定默認 DEFAULT 關鍵字才能獲取默認值。用戶定義函數(shù)不支持輸出參數(shù)。標量函數(shù) 標量函數(shù)返回在 RETURNS 子句中定義的類型的單個數(shù)據值。返回類型可以是除 text、ntext、im

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

6、數(shù)據類型與自定義函數(shù),（3）創(chuàng)建用戶定義函數(shù)語句標量函數(shù)CREATE FUNCTION [ 屬主名.] 函數(shù)名    ([{@parameter_name [AS] 標量參數(shù)數(shù)據類型 [=default]} [ ,...n ] ] ) RETURNS 標量返回數(shù)據類型[ WITH [ [,] ...n] ] [ AS ]BEGIN    函

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

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

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

10、 ] ) 【實驗目的】學習和掌握用戶定義數(shù)據類型的概念、創(chuàng)建及使用方法；學習和掌握用戶定義函數(shù)的概念、創(chuàng)建及使用方法。,,實驗11.1 創(chuàng)建和使用用戶定義數(shù)據類型,【實驗目的】掌握創(chuàng)建用戶定義數(shù)據類型的方法；掌握用戶定義數(shù)據類型的使用?！緦嶒瀮热荨坑肧QL語句創(chuàng)建一用戶定義的數(shù)據類型Idnum。要求：系統(tǒng)數(shù)據類型為char，長度為6，不為空。用于學號，教師號字段的數(shù)據類型；用企業(yè)管理器創(chuàng)建一用戶定義的數(shù)據類型Namep

11、erson。要求：系統(tǒng)數(shù)據類型char，長度為11，不為空。用于學生姓名，教師姓名字段的數(shù)據類型。,【實驗步驟】 1.創(chuàng)建和使用一用戶定義的數(shù)據類型Idnum（1）用SQL語句創(chuàng)建一個用戶定義的數(shù)據類型Idnum。,T-SQL程序設計及執(zhí)行,用戶定義的數(shù)據類型對象,,實驗11.1 創(chuàng)建和使用用戶定義數(shù)據類型,（2）使用用戶定義的數(shù)據類型Idnum ，創(chuàng)建一個學生表STUDENT和一個教師表TEACHER,在查詢窗口中輸入下面程

12、序，創(chuàng)建一個學生數(shù)據表STUDENT和一個教師表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ù)據類型的

13、使用,,實驗11.1 創(chuàng)建和使用用戶定義數(shù)據類型,2.創(chuàng)建一用戶定義的數(shù)據類型Nameperson（1）用企業(yè)管理器創(chuàng)建一個用戶定義的數(shù)據類型Nameperson,選擇用戶定義數(shù)據類型,創(chuàng)建用戶定義的數(shù)據類型對話框,已定義的用戶定義數(shù)據類型,,實驗11.1 創(chuàng)建和使用用戶定義數(shù)據類型,（2）使用用戶定義的數(shù)據類型Nameperson。修改學生表STUDENT中的姓名類型為Nameperson和教師表中的教師姓名類型為Nameperso

14、n。,在查詢窗口中輸入下面程序，修改學生數(shù)據表STUDENT和教師表TEACHER ：USE jxskALTER TABLE STUDENTALTER COLUMN SN NamepersonALTER TABLE TEACHERALTER COLUMN TN Nameperson,用戶定義數(shù)據類型的使用,,實驗11.2 刪除數(shù)據類型與自定義函數(shù),,【實驗目的】掌握使用系統(tǒng)存儲過程刪除用戶定義的數(shù)據類型；掌握使用企業(yè)管

15、理器刪除用戶定義的數(shù)據類型?！緦嶒瀮热荨渴褂孟到y(tǒng)存儲過程刪除用戶定義的數(shù)據類型Nameperson；使用企業(yè)管理器刪除用戶定義的數(shù)據類型Idnum?！緦嶒灢襟E】 1.使用系統(tǒng)存儲過程刪除用戶定義的數(shù)據類型,在查詢窗口中輸入下面程序，先解除對用戶定義的數(shù)據類型Nameperson的使用，然后再刪除用戶定義的數(shù)據類型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ù)據類型Idnum,,實驗11.3 創(chuàng)建和使用用戶定義函數(shù),【實驗目的】掌握創(chuàng)建標量函數(shù)的創(chuàng)建方法；掌握創(chuàng)建內嵌表值函數(shù)的方法；掌握創(chuàng)建多語句表值函數(shù)的方法?！緦嶒瀮热荨縿?chuàng)建一個標量函數(shù)S

17、core_FUN，根據學生姓名和課程名，查詢成績；創(chuàng)建一個內嵌函數(shù)S_Score_FUN，根據學生姓名，查詢該生所有選課的成績；創(chuàng)建一個多語句函數(shù)ALL_Score_FUN，根據課程名，查詢所有選擇該課程學生的成績信息，包括：學號SNO，姓名SN，性別SEX，成績SCROE。結果按成績降序排列。,,實驗11.3 創(chuàng)建和使用用戶定義函數(shù),【實驗步驟】 1.用企業(yè)管理器創(chuàng)建一個標量函數(shù)Score_FUN（1）用企業(yè)管理器創(chuàng)建一個

18、標量函數(shù)Score_FUN,用戶定義函數(shù)命令,,實驗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ù)對象,,實驗11.3 創(chuàng)建和使用用戶定義函數(shù),（2）使用用戶定義的函數(shù)Score_FUN，查詢學生錢爾的編譯原理課程的成績,執(zhí)行用戶定義的函數(shù),,實驗11.3 創(chuàng)建和使用用戶

20、定義函數(shù),2.用SQL創(chuàng)建一個內嵌函數(shù)（1）用SQL創(chuàng)建一個內嵌函數(shù)S_Score_FUN,用SQL創(chuàng)建函數(shù),（2）使用用戶定義的函數(shù)S_Score_FUN，查詢學生錢爾所有課程的成績,執(zhí)行函數(shù)查詢成績,,實驗11.3 創(chuàng)建和使用用戶定義函數(shù),3.用SQL創(chuàng)建一個多語句函數(shù)（1）用SQL創(chuàng)建一個多語句函數(shù)ALL_Score_FUN （2）使用用戶定義的函數(shù)ALL_Score_FUN，查詢選擇微機原理課程的學生的成績,執(zhí)行函數(shù)查詢成

21、績,用SQL創(chuàng)建函數(shù),用戶定義函數(shù)命令,,實驗11.4 修改用戶定義函數(shù),【實驗目的】掌握使用企業(yè)管理器修改用戶定義的函數(shù)方法；掌握使用SQL修改用戶定義的函數(shù)方法?！緦嶒瀮热荨坑闷髽I(yè)管理器修改函數(shù)Score_FUN，將成績轉換為等級輸出；用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對應成績的等級 。【實驗步驟】 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,,實驗11.4 修改用戶定義函數(shù),（2）使用用戶定義的函數(shù)Score_FUN

24、，查詢學生錢爾的編譯原理課程的成績,執(zhí)行用戶定義函數(shù),,實驗11.4 修改用戶定義函數(shù),2.用SQL修改函數(shù)（1）用SQL修改函數(shù)S_Score_FUN，要求增加一輸出列對應成績的等級,修改函數(shù)定義,（2）使用用戶定義的函數(shù)S_Score_FUN，查詢學生錢爾所有課程的成績,執(zhí)行函數(shù)查詢成績,,實驗11.5 刪除用戶定義函數(shù),【實驗目的】掌握使用企業(yè)管理器刪除用戶定義的函數(shù)方法；掌握使用SQL刪除用戶定義的函數(shù)方法?！緦嶒瀮热荨?/p>

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

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