实验11.ppt

1、实验 11 用户定义数据类型与自定义函数本实验包含两方面的内容：一个是学习掌握用户定义数据类型的特点及定义方法，以及如何使用用户定义数据类型；另一个是学习掌握用户自定义函数的概念、创建及使用方法。【 知识要点 】1.用户定义数据类型（ 1）为何使用用户定义数据类型当一个或多个表的字段中要存储同样类型的数据，且想确保这些字段具有完全相同的数据类型、长度和是否为 NULL时，可使用用户定义数据类型。例如，学生号和教师号的数据类型都是基于 CHAR的有固定长度 6个字符，且不为空。（ 2）用户定义数据类型的特点用户定义的数据类型名称在数据库中必须是唯一的，但是名称不同的用户定义的数据类型可以有相同的

2、定义。（ 3）创建用户定义数据类型语句sp_addtype typename= 类型，phystype= 系统定义的数据类型,nulltype = NULL|NOT NULL,owner = 属主名 实验 11 用户定义数据类型与自定义函数2.用户定义函数简介（ 1）为何使用用户定义函数用户定义函数可以让你针对特定应用程序问题提供解决方案。这些函数可以简单到计算一个值，也可以复杂到定义和实现业务处理规则。定义了这些函数以后，在需要的时候调用即可，如果业务处理的规则发生变化，只需要修改相应的函数，只要这些接口未发生变化，就可以保持原来的函数调用。（ 2）用户定义函数的类型SQL Server20

3、00有 3种自定义函数：标量函数、内嵌表值函数和多语句表值函数。用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有 1124 个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。用户定义函数不支持输出参数。标量函数标量函数返回在 RETURNS 子句中定义的类型的单个数据值。返回类型可以是除 text、 ntext、 image、 cursor 和 timestamp 之外的任何数据类型。不支持用户定义数据类型和非标量类型（如 table 或 cursor）。内嵌表值函数内嵌表值函数返回 table。没有函数主体；表是单个 SE

4、LECT语句的结果集。多语句表值函数多语句表值函数返回 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函数体RETURNEND:= ENCRYPTION | SCHEMABINDING := ( column_definition | table_const

6、raint ,.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 jxskCREATE TABLE STUDENT（SNO Idnum ，SN CHAR（ 11），SSEX CHAR（ 2），SAGE TINYINT ）CREATE TABLE TEACHER（TNO Idnum ，TN CH

8、AR（ 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 jxskALTER TABLE STUDENTALTER COLUMN SN NamepersonALTER TABLE TEACHERALTER COLUMN TN Nameperson 用户定义数据类型的使用实验 11.2 删除数据类型与自定义函数【 实验目的 】掌握使用系统存储过程删除用户定义的数据类型；掌握使用企业管理器删除用户定义的数据类型。【 实验内容 】使用系统存储过程删除用户定义的数据类型 Nameperson；使用企业管理器删除用户定义的数据类型 Idnum。【 实验步骤 】1.使用系统存储过程删除用户定义的数据类型

10、在查询窗口中输入下面程序，先解除对用户定义的数据类型Nameperson的使用，然后再删除用户定义的数据类型 Nameperson：USE jxskALTER TABLE student ALTER COLUMN SNO CHAR（ 6） NOT NULLALTER TABLE teacher ALTER COLUMN TNO CHAR（ 6） NOT NULLEXEC Sp_droptype NamepersonGO2使用企业管理器删除用户定义的数据类型 Idnum实验 11.3 创建和使用用户定义函数【 实验目的 】掌握创建标量函数的创建方法；掌握创建内嵌表值函数的方法；掌握创建多语句表值函数的方法。【 实验内容 】创建一个标量函数 Score_FUN，根据学生姓名和课程名，查询成绩；创建一个内嵌函数 S_Score_FUN，根据学生姓名，查询该生所有选课的成绩；创建一个多语句函数 ALL_Score_FUN，根据课程名，查询所有选择该课程学生的成绩信息，包括：学号 SNO，姓名 SN，性别 SEX，成绩 SCROE。结果按成绩降序排列。