1、,实验11 用户定义数据类型与自定义函数,本实验包含两方面的内容：一个是学习掌握用户定义数据类型的特点及定义方法，以及如何使用用户定义数据类型；另一个是学习掌握用户自定义函数的概念、创建及使用方法。 【知识要点】1.用户定义数据类型 （1）为何使用用户定义数据类型当一个或多个表的字段中要存储同样类型的数据，且想确保这些字段具有完全相同的数据类型、长度和是否为NULL时，可使用用户定义数据类型。例如，学生号和教师号的数据类型都是基于CHAR的有固定长度6个字符，且不为空。 （2）用户定义数据类型的特点用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的定义。
2、 （3）创建用户定义数据类型语句 sp_addtype typename= 类型，phystype= 系统定义的数据类型,nulltype = NULL|NOT NULL,owner = 属主名,实验11 用户定义数据类型与自定义函数,2.用户定义函数简介 （1）为何使用用户定义函数用户定义函数可以让你针对特定应用程序问题提供解决方案。这些函数可以简单到计算一个值，也可以复杂到定义和实现业务处理规则。定义了这些函数以后，在需要的时候调用即可，如果业务处理的规则发生变化，只需要修改相应的函数，只要这些接口未发生变化，就可以保持原来的函数调用。 （2）用户定义函数的类型SQL Server2000
3、有3种自定义函数：标量函数、内嵌表值函数和多语句表值函数。用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有 1124 个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。用户定义函数不支持输出参数。 标量函数标量函数返回在 RETURNS 子句中定义的类型的单个数据值。返回类型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何数据类型。不支持用户定义数据类型和非标量类型（如 table 或 cursor）。 内嵌表值函数内嵌表值函数返回table。没有函数主体；表是单个SELECT语句
4、的结果集。 多语句表值函数多语句表值函数返回table，在 BEGIN.END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中。,实验11 用户定义数据类型与自定义函数,（3）创建用户定义函数语句 标量函数 CREATE FUNCTION 属主名. 函数名 (parameter_name AS 标量参数数据类型 =default ,.n ) RETURNS 标量返回数据类型 WITH , .n AS BEGIN 函数体 RETURN 标量表达式 END 内嵌表值函数 CREATE FUNCTION 属主名. 函数名 ( parameter_nam
5、e AS 标量参数数据类型 = default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( ) select语句 ,实验11 用户定义数据类型与自定义函数,多语句表值函数 CREATE FUNCTION 属主名. 函数名 ( parameter_name AS 标量参数数据类型 = default ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN 函数体 RETURN END:= ENCRYPTION | SCHEMABINDING := ( column_definition | table_
6、constraint ,.n ) 【实验目的】 学习和掌握用户定义数据类型的概念、创建及使用方法； 学习和掌握用户定义函数的概念、创建及使用方法。,实验11.1 创建和使用用户定义数据类型,【实验目的】 掌握创建用户定义数据类型的方法； 掌握用户定义数据类型的使用。 【实验内容】 用SQL语句创建一用户定义的数据类型Idnum。要求：系统数据类型为 char，长度为6，不为空。用于学号，教师号字段的数据类型； 用企业管理器创建一用户定义的数据类型Nameperson。要求：系统数 据类型char，长度为11，不为空。用于学生姓名，教师姓名字段的数据类型。,【实验步骤】1.创建和使用一用户定义的
7、数据类型Idnum （1）用SQL语句创建一个用户定义的数据类型Idnum。,T-SQL程序设计及 执行,用户定义的数据类型 对象,实验11.1 创建和使用用户定义数据类型,（2）使用用户定义的数据类型Idnum ，创建一个学生表STUDENT和一个教师表TEACHER,在查询窗口中输入下面程序，创建一个学生数据表STUDENT和一个教师表TEACHER： USE jxsk CREATE TABLE STUDENT（ SNO Idnum ， SN CHAR（11）， SSEX CHAR（2）， SAGE TINYINT ） CREATE TABLE TEACHER（ TNO Idnum ， T
8、N CHAR（11）， TSEX CHAR（2）， TAGE TINYINT ， TPROF CHAR（11） ）,用户定义数据类型的使用,实验11.1 创建和使用用户定义数据类型,2.创建一用户定义的数据类型Nameperson （1）用企业管理器创建一个用户定义的数据类型Nameperson,选择用户定义数据类型,创建用户定义的数据类型对话框,已定义的用户定义数据类型,实验11.1 创建和使用用户定义数据类型,（2）使用用户定义的数据类型Nameperson。修改学生表STUDENT中的姓名类型为Nameperson和教师表中的教师姓名类型为Nameperson。,在查询窗口中输入下面程序
9、，修改学生数据表STUDENT和教师表TEACHER ：USE jxsk ALTER TABLE STUDENT ALTER COLUMN SN Nameperson ALTER TABLE TEACHER ALTER COLUMN TN Nameperson,用户定义数据类型的使用,实验11.2 删除数据类型与自定义函数,【实验目的】 掌握使用系统存储过程删除用户定义的数据类型； 掌握使用企业管理器删除用户定义的数据类型。 【实验内容】 使用系统存储过程删除用户定义的数据类型Nameperson； 使用企业管理器删除用户定义的数据类型Idnum。 【实验步骤】1.使用系统存储过程删除用户定义
10、的数据类型,在查询窗口中输入下面程序，先解除对用户定义的数据类型Nameperson的使用，然后再删除用户定义的数据类型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使用企业管理器删除用户定义的数据类型Idnum,实验11.3 创建和使用用户定义函数,【实验目的】 掌握创建标量函数的创建方法； 掌握创建内嵌表值函数的方法；
11、掌握创建多语句表值函数的方法。 【实验内容】 创建一个标量函数Score_FUN，根据学生姓名和课程名，查询成绩； 创建一个内嵌函数S_Score_FUN，根据学生姓名，查询该生所有选课的 成绩； 创建一个多语句函数ALL_Score_FUN，根据课程名，查询所有选择该课 程学生的成绩信息，包括：学号SNO，姓名SN，性别SEX，成绩SCROE。结果按成绩降序排列。,实验11.3 创建和使用用户定义函数,【实验步骤】1.用企业管理器创建一个标量函数Score_FUN （1）用企业管理器创建一个标量函数Score_FUN,用户定义函数命令,实验11.3 创建和使用用户定义函数,在窗口“文本” 框
12、中输入下面SQL语句。 CREATE FUNCTION Score_FUN( SNAME_IN CHAR(11),CNAME_IN CHAR(11) ) RETURNS TINYINT AS BEGINDECLARE SCORE_OUT TINYINTSELECT 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,用户定义的函数对象,实验11.3 创建和使用用户定义函数,（2）使用用户定义的函数Score_FUN，查询
13、学生钱尔的编译原理课程的成绩,执行用户定义的函数,实验11.3 创建和使用用户定义函数,2.用SQL创建一个内嵌函数 （1）用SQL创建一个内嵌函数S_Score_FUN,用SQL创建函数,（2）使用用户定义的函数S_Score_FUN，查询学生钱尔所有课程的成绩,执行函数查询成绩,实验11.3 创建和使用用户定义函数,3.用SQL创建一个多语句函数 （1）用SQL创建一个多语句函数ALL_Score_FUN （2）使用用户定义的函数ALL_Score_FUN，查询选择微机原理课程的学生的成绩,执行函数查询成绩,用SQL创建函数,用户定义函数命令,实验11.4 修改用户定义函数,【实验目的】
14、掌握使用企业管理器修改用户定义的函数方法； 掌握使用SQL修改用户定义的函数方法。 【实验内容】 用企业管理器修改函数Score_FUN，将成绩转换为等级输出； 用SQL修改函数S_Score_FUN，要求增加一输出列对应成绩的等级 。 【实验步骤】1.用企业管理器修改函数（1）用企业管理器修改函数Score_FUN,在用户定义的函数的属性窗口中的“文本”输入框输入下面T-SQL语句： CREATE FUNCTION Score_FUN( SNAME_IN CHAR(10),CNAME_IN CHAR(10) ) RETURNS CHAR(8) AS BEGINDECLARE SCORE_OU
15、T CHAR(8)SELECT SCORE_OUT= CASE WHEN SCORE IS NULL THEN 未考 WHEN SCORE =60 AND SCORE =70 AND SCORE =80 AND SCORE = 90 THEN优秀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,实验11.4 修改用户定义函数,（2）使用用户定义的函数Score_FUN，查询学生钱尔的编译原理课程的成绩,执行用户定义函数,实验11.4 修改用户定义函数,
16、2.用SQL修改函数 （1）用SQL修改函数S_Score_FUN，要求增加一输出列对应成绩的等级,修改函数定义,（2）使用用户定义的函数S_Score_FUN，查询学生钱尔所有课程的成绩,执行函数查询成绩,实验11.5 删除用户定义函数,【实验目的】 掌握使用企业管理器删除用户定义的函数方法； 掌握使用SQL删除用户定义的函数方法。 【实验内容】 用企业管理器删除函数Score_FUN； 用SQL删除函数S_Score_FUN。 【实验步骤】 1.用企业管理器删除函数Score_FUN 2.用SQL删除函数S_Score_FUN,实验11 习题,【实验题】 实验内容和要求针对教学数据库jia
17、oxuedb进行如下实验： 1.用SQL语句创建一用户定义的数据类型AgeType。要求：系统数据类型为Tinyint，可为空，取值范围：0100，用于学生表和教师表中的年龄字段和选课表中成绩字段的数据类型。 2.用企业管理器创建数据类型NameType。要求：系统数据类型为Varchar，长度10个字节，不为空，用于教师名、学生名、职称字段的数据类型。 3.创建一个函数，要求：根据学生姓名和课程名查询该生该课程的成绩。 4.创建一个函数，要求：根据教师姓名查询该教师所教课程名、学生人数、平均成绩、最高成绩、最低成绩。 5.创建一个函数，要求：统计各系各职称的总人数、男女人数、平均年龄。,【思考题】1.是否可以使用任一个SQL Server系统数据类型来定义用户定义的数据类型 ？哪些类型不允许使用？2.用户定义的函数有几种类型，返回值各是什么？3.你知道的用户定义的函数的执行形式有哪些？,
