用户自定义数据类型与函数PPT课件

1 实验11用户定义数据类型与自定义函数 本实验包含两方面的内容 一个是学习掌握用户定义数据类型的特点及定义方法 以及如何使用用户定义数据类型 另一个是学习掌握用户自定义函数的概念 创建及使用方法 知识要点 1 用户定义数据类型 1 为何使用用户定义数据类型当一个或多个表的字段中要存储同样类型的数据 且想确保这些字段具有完全相同的数据类型 长度和是否为NULL时 可使用用户定义数据类型 例如 学生号和教师号的数据类型都是基于CHAR的有固定长度6个字符 且不为空 2 用户定义数据类型的特点用户定义的数据类型名称在数据库中必须是唯一的 但是名称不同的用户定义的数据类型可以有相同的定义 3 创建用户定义数据类型语句sp addtype typename 类型 phystype 系统定义的数据类型 nulltype NULL NOTNULL owner 属主名 2 实验11用户定义数据类型与自定义函数 2 用户定义函数简介 1 为何使用用户定义函数用户定义函数可以让你针对特定应用程序问题提供解决方案 这些函数可以简单到计算一个值 也可以复杂到定义和实现业务处理规则 定义了这些函数以后 在需要的时候调用即可 如果业务处理的规则发生变化 只需要修改相应的函数 只要这些接口未发生变化 就可以保持原来的函数调用 2 用户定义函数的类型SQLServer2000有3种自定义函数 标量函数 内嵌表值函数和多语句表值函数 用户定义函数采用零个或更多的输入参数并返回标量值或表 函数最多可以有1124个输入参数 当函数的参数有默认值时 调用该函数时必须指定默认DEFAULT关键字才能获取默认值 用户定义函数不支持输出参数 标量函数标量函数返回在RETURNS子句中定义的类型的单个数据值 返回类型可以是除text ntext image cursor和timestamp之外的任何数据类型 不支持用户定义数据类型和非标量类型 如table或cursor 内嵌表值函数内嵌表值函数返回table 没有函数主体 表是单个SELECT语句的结果集 多语句表值函数多语句表值函数返回table 在BEGIN END块中定义的函数主体包含TRANSACT SQL语句 这些语句可生成行并将行插入将返回的表中 3 实验11用户定义数据类型与自定义函数 3 创建用户定义函数语句标量函数CREATEFUNCTION 属主名 函数名 parameter name AS 标量参数数据类型 default n RETURNS标量返回数据类型 WITH n AS BEGIN函数体RETURN标量表达式END内嵌表值函数CREATEFUNCTION 属主名 函数名 parameter name AS 标量参数数据类型 default n RETURNSTABLE WITH n AS RETURN select语句 4 实验11用户定义数据类型与自定义函数 多语句表值函数CREATEFUNCTION 属主名 函数名 parameter name AS 标量参数数据类型 default n RETURNS return variableTABLE WITH n AS BEGIN函数体RETURNEND ENCRYPTION SCHEMABINDING column definition table constraint n 实验目的 学习和掌握用户定义数据类型的概念 创建及使用方法 学习和掌握用户定义函数的概念 创建及使用方法 5 实验11 1创建和使用用户定义数据类型 实验目的 掌握创建用户定义数据类型的方法 掌握用户定义数据类型的使用 实验内容 用SQL语句创建一用户定义的数据类型Idnum 要求 系统数据类型为char 长度为6 不为空 用于学号 教师号字段的数据类型 用企业管理器创建一用户定义的数据类型Nameperson 要求 系统数据类型char 长度为11 不为空 用于学生姓名 教师姓名字段的数据类型 实验步骤 1 创建和使用一用户定义的数据类型Idnum 1 用SQL语句创建一个用户定义的数据类型Idnum T SQL程序设计及执行 用户定义的数据类型对象 6 实验11 1创建和使用用户定义数据类型 2 使用用户定义的数据类型Idnum 创建一个学生表STUDENT和一个教师表TEACHER 在查询窗口中输入下面程序 创建一个学生数据表STUDENT和一个教师表TEACHER USEjxskCREATETABLESTUDENT SNOIdnum SNCHAR 11 SSEXCHAR 2 SAGETINYINT CREATETABLETEACHER TNOIdnum TNCHAR 11 TSEXCHAR 2 TAGETINYINT TPROFCHAR 11 用户定义数据类型的使用 7 实验11 1创建和使用用户定义数据类型 2 创建一用户定义的数据类型Nameperson 1 用企业管理器创建一个用户定义的数据类型Nameperson 选择用户定义数据类型 创建用户定义的数据类型对话框 已定义的用户定义数据类型 8 实验11 1创建和使用用户定义数据类型 2 使用用户定义的数据类型Nameperson 修改学生表STUDENT中的姓名类型为Nameperson和教师表中的教师姓名类型为Nameperson 在查询窗口中输入下面程序 修改学生数据表STUDENT和教师表TEACHER USEjxskALTERTABLESTUDENTALTERCOLUMNSNNamepersonALTERTABLETEACHERALTERCOLUMNTNNameperson 用户定义数据类型的使用 9 实验11 2删除数据类型与自定义函数 实验目的 掌握使用系统存储过程删除用户定义的数据类型 掌握使用企业管理器删除用户定义的数据类型 实验内容 使用系统存储过程删除用户定义的数据类型Nameperson 使用企业管理器删除用户定义的数据类型Idnum 实验步骤 1 使用系统存储过程删除用户定义的数据类型 在查询窗口中输入下面程序 先解除对用户定义的数据类型Nameperson的使用 然后再删除用户定义的数据类型Nameperson USEjxskALTERTABLEstudentALTERCOLUMNSNOCHAR 6 NOTNULLALTERTABLEteacherALTERCOLUMNTNOCHAR 6 NOTNULLEXECSp droptypeNamepersonGO 2 使用企业管理器删除用户定义的数据类型Idnum 10 实验11 3创建和使用用户定义函数 实验目的 掌握创建标量函数的创建方法 掌握创建内嵌表值函数的方法 掌握创建多语句表值函数的方法 实验内容 创建一个标量函数Score FUN 根据学生姓名和课程名 查询成绩 创建一个内嵌函数S Score FUN 根据学生姓名 查询该生所有选课的成绩 创建一个多语句函数ALL Score FUN 根据课程名 查询所有选择该课程学生的成绩信息 包括 学号SNO 姓名SN 性别SEX 成绩SCROE 结果按成绩降序排列 11 实验11 3创建和使用用户定义函数 实验步骤 1 用企业管理器创建一个标量函数Score FUN 1 用企业管理器创建一个标量函数Score FUN 用户定义函数命令 12 实验11 3创建和使用用户定义函数 在窗口 文本 框中输入下面SQL语句 CREATEFUNCTIONScore FUN SNAME INCHAR 11 CNAME INCHAR 11 RETURNSTINYINTASBEGINDECLARE SCORE OUTTINYINTSELECT SCORE OUT gradeFROMsc s cWHERES sno Sc snoandS cno SoandSname SNAME INandCname CNAME INRETURN SCORE OUT END 用户定义的函数对象 13 实验11 3创建和使用用户定义函数 2 使用用户定义的函数Score FUN 查询学生钱尔的编译原理课程的成绩 执行用户定义的函数 14 实验11 3创建和使用用户定义函数 2 用SQL创建一个内嵌函数 1 用SQL创建一个内嵌函数S Score FUN 用SQL创建函数 2 使用用户定义的函数S Score FUN 查询学生钱尔所有课程的成绩 执行函数查询成绩 15 实验11 3创建和使用用户定义函数 3 用SQL创建一个多语句函数 1 用SQL创建一个多语句函数ALL Score FUN 2 使用用户定义的函数ALL Score FUN 查询选择微机原理课程的学生的成绩 执行函数查询成绩 用SQL创建函数 16 用户定义函数命令 实验11 4修改用户定义函数 实验目的 掌握使用企业管理器修改用户定义的函数方法 掌握使用SQL修改用户定义的函数方法 实验内容 用企业管理器修改函数Score FUN 将成绩转换为等级输出 用SQL修改函数S Score FUN 要求增加一输出列对应成绩的等级 实验步骤 1 用企业管理器修改函数 1 用企业管理器修改函数Score FUN 在用户定义的函数的属性窗口中的 文本 输入框输入下面T SQL语句 CREATEFUNCTIONScore FUN SNAME INCHAR 10 CNAME INCHAR 10 RETURNSCHAR 8 ASBEGINDECLARE SCORE OUTCHAR 8 SELECT SCORE OUT CASEWHENSCOREISNULLTHEN 未考 WHENSCORE 60ANDSCORE 70ANDSCORE 80ANDSCORE 90THEN 优秀 ENDFROMsc s cWHEREs sno o oandSn SNAME INandCn CNAME INRETURN SCORE OUT END 17 实验11 4修改用户定义函数 2 使用用户定义的函数Score FUN 查询学生钱尔的编译原理课程的成绩 执行用户定义函数 18 实验11 4修改用户定义函数 2 用SQL修改函数 1 用SQL修改函数S Score FUN 要求增加一输出列对应成绩的等级 修改函数定义 2 使用用户定义的函数S Score FUN 查询学生钱尔所有课程的成绩 执行函数查询成绩 19 实验11 5删除用户定义函数 实验目的 掌握使用企业管理器删除用户定义的函数方法 掌握使用SQL删除用户定义的函数方法 实验内容 用企业管理器删除函数Score FUN 用SQL删除函数S Score FUN 实验步骤 1 用企业管理器删除函数Score FUN2 用SQL删除函数S Score FUN 20 实验11习题 实验题 实验内容和要求针对教学数据库jiaoxuedb进行如下实验 1 用SQL语句创建一用户定义的数据类型AgeType 要求 系统数据类型为Tinyint 可为空 取值范围 0 100 用于学生表和教师表中的年龄字段和选课表中成绩字段的数据类型 2 用企业管理器创建数据类型NameType 要求 系统数据类型为Varchar 长