数据库实验11_1207082107_陈永超

《数据库》实验报告实验序号：11学号127082107姓名陈永超专业、班级12物联网实验地点1#307指导教师实验时间一、实验目的及要求1、学习和掌握用户定义数据类型的概念、创建及使用方法。2、学习和掌握用户定义函数的概念、创建及使用方法。二、实验设备（环境）及要求Sqlserver2005。三、实验内容与步骤或结果实验11.1创建和使用用户定义数据类型运行结果截图：实验11.2删除用户定义的数据类型运行结果截图：实验11.3创建和使用用户定义的函数运行结果截图：实验11.4修改用户定义的函数运行结果截图：实验11.5删除用户定义的函数四、分析与讨论我通过本次实验掌握用户定义数据类型以及掌握用户定义函数的概念、创建及使用方法！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！五、教师评语签名：日期：成绩附源程序清单：1.useironygoEXECsp_addtypeIdnum,'char(6)','NOTNULL'GOUSEironyGOCREATETABLESTUDENT(SNOIdnum,snchar(11),ssexchar(2),sagetinyint)GOCREATETABLETEACHER(TNOIdnum,TNCHAR(11),TSEXCHAR(2),TAGETINYINT,TPROFCHAR(11))GOUSEironyGOALTERTABLESTUDENTALTERCOLUMNSNNamepersonGOALTERTABLETEACHERALTERCOLUMNTNNamepersonGO2.USEironyGOALTERTABLESTUDENTALTERCOLUMNSNchar(10)NOTNULLGOALTERTABLETEACHERALTERCOLUMNTNchar(10)NOTNULLGOEXECsp_droptypeNamepersonGO3.CREATEFUNCTIONScore_FUN(@SNAME_INCHAR(8),@CNAME_INCHAR(10))RETURNSTINYINTASBEGINDECLARE@SCORE_OUTTINYINTSELECT@SCORE_OUT=scoreFROMsc,s,cwheres.sno=sc.snoandc.cno=sc.cnoandSn=@SNAME_INandCn=@CNAME_INRETURN(@SCORE_OUT)ENDGOuseironygodeclare@S_Scoretinyintexec@S_Score=dbo.Score_FUN'钱尔','编译原理'print'钱尔的编译原理成绩是'+STR(@S_score)gouseironygocreatefunctionS_Score_FUN(@SNAME_INCHAR(8))RETURNSTABLEASRETURN(SELECTCN,SCOREFROMS,SC,CWhereS.Sno=SC.SnoandC.Cno=SC.cnoandSn=@SNAME_IN)Gouseironygoselect*fromS_Score_FUN('钱尔')gouseironygocreatefunctionALL_Score_FUN(@CNAME_INCHAR(10))RETURNS@ALL_SCORE_TABTABLE(SNOCHAR(2)PRIMARYKEY,SNCHAR(8)NOTNULL,SEXCHAR(2),SCORETINYINT)ASBEGININSERT@ALL_SCORE_TABSELECTS.SNO,SN,SEX,SCOREFROMS,SC,CWhereS.Sno=SC.SnoandC.Cno=SC.CnoandCn=@CNAME_INRETURNENDGouseironygoSELECT*FROMALL_Score_FUN('微机原理')Go4.ALTERFUNCTION[dbo].[Score_FUN](@SNAME_INCHAR(10),@CNAME_INCHAR(10))RETURNSchar(8)ASBEGINDECLARE@SCORE_OUTchar(8)SELECT@SCORE_OUT=CASEWHENSCOREISNULLTHEN'未考'WHENSCORE60THEN'不及格'WHENSCORE=60ANDSCORE70THEN'及格'WHENSCORE=70ANDSCORE80THEN'中'WHENSCORE=80ANDSCORE90THEN'良好'WHENSCORE=90THEN'优秀'ENDFROMsc,s,cwheres.sno=sc.snoandc.cno=sc.cnoandSn=@SNAME_INandCn=@CNAME_INRETURN(@SCORE_OUT)ENDuSEironygodeclare@S_ScoreCHAR(8)EXEC@S_Score=dbo.Score_FUN'钱尔','编译原理'PRINT'钱尔的编译原理成绩是'+@S_ScoreGouSEironygoALTERFUNCTIONS_Score_FUN(@SNAME_INCHAR(8))RETURNSTABLEASRETURN(SELECTCN,SCORE,LEVER=CASEWHENSCOREISNULLTHEN'未考'WHENSCORE60THEN'不及格'WHENSCORE=60ANDSCORE70THEN'及格'WHENSCORE=70ANDSCORE80THEN'中'WHENSCORE=80ANDSCORE90THEN'良好'WHENSCORE=90THEN'优秀'ENDFROMsc,s,cwhereS.sno=SC.snoandC.cno=Sc.cnoandSn=@SNAME_IN)gouSEironygoselect*fromS_Score_FUN('钱尔')Go5．useironydropfunctionS_Score_FUNGO