游标规则用户自定义函数.ppt

,游标,2,本章目标,游标的使用规则的使用自定义函数,游标引例,以前：使用SELECT语句对表格进行查询，返回的结果集包括所有满足条件的行。思考：如果要求每次只显示表格（例如上面的Course表）中的一行，该如何处理？这在将T-SQL嵌入到其他高级语言（如C、VC、Delphi等）的编程中经常用到。,游标概述,可以将游标看作一种特殊的指针，它可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。游标提供了对一个结果集进行逐行处理的能力：在结果集中定位特定行从结果集的当前位置检索行支持对结果集中当前位置的行进行数据处理（修改/删除）,使用游标的步骤,声明游标打开游标处理数据（读取/修改/删除）可以和其他T-SQL语句配合灵活使用关闭游标（与打开游标配对）删除游标（与声明游标配对，此时释放分配给游标的所有资源）【问题】游标基本使用的例子,相关语法格式,声明游标DECLAREcursor_nameCURSORFORWORD_ONLY|SCROLLFORselect_statements打开游标OPENcursor_name使用游标FETCHNEXT|PRIOR|FIRST|LASTFROMcursorIntovariable_name关闭游标CLOSEcursor_name删除游标DEALLOCATEcursor_name,例子,declaretNamevarchar(100)declaremyCursorcursorforselectnamefromsystypesopenmyCursorfetchnextfrommyCursorintotNamewhilefetch_status=0beginprint(tName)fetchnextfrommyCursorintotNameendclosemyCursordeallocatemyCursor,注意须序o.,在写SQL存储过程和触发器的时候，经常会用游标，一用到游标就要循环,练习1,声明一个名为CrsStudent的游标，使其与“00电子商务班”的所有学生信息（学号、姓名）相关联，并逐行显示前三条学生信息,使用fetch_status:该全局变量/配置函数返回被最后FETCH语句执行的游标的状态，返回类型为int：0：FETCH语句成功1:FETCH语句失败或此行不在结果集中2：被提取的行不存在,练习2,使用游标遍历Course表，输出报名人数最多的课程的信息（课程编码、课程名称、报名人数）,练习3,建立一个存储过程，利用游标求course表中某系（用户给定系代码）所开课程的最大报名人数，并把课程名称和最大报名人数作为输出变量返回给用户。,规则,创建具有范围的规则,CREATERULErange_ruleASrange=$1000ANDrange$20000,用以限制插入该规则被绑定到的列中的整数的范围,创建具有列表的规则,CREATERULElist_ruleASlistIN(1389,0736,0877),用于将输入到该规则被绑定到的列中的实际值限制为只能是该规则中列出的值,创建具有模式的规则,CREATERULEpattern_ruleASvalueLIKE_-%0-9,创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符(-)和任意多个字符（或没有字符），并以0到9之间的整数结尾,将规则绑定到列,execsp_bindrulerange_rule,teaInfo.Salary,注意1.规则名和表中的列必须用2.规则名和表中的列之间用,将规则绑定到别名数据类型,EXECsp_bindrulerule_ssn,ssn,1.在CREATETABLE语句中，类型为ssn的列会继承rule_ssn规则。2.类型为ssn的现有列也会继承rule_ssn规则，除非为futureonly_flag指定了futureonly，或者在ssn上直接绑定了规则。3.绑定到列的规则始终优先于绑定到数据类型的规则。,规则名,别名数据类型,与自定义数据类型相关的存储过程sp_addtype添加一个自定义数据类型sp_droptype删除一个自定义数据类型Execsp_addtypeAccountType,varchar(20),notnullExecsp_droptypeAccountType-先删除使用了该类型的表createtableAccountInfo(accIDAccountType),使用futureonly_flag,EXECsp_bindrulerule_ssn,ssn,futureonly,将rule_ssn规则绑定到别名数据类型ssn。由于已指定futureonly，因此不影响类型为ssn的现有列,规则名,别名数据类型,缺省,缺省的创建createdefaultd_Dateasgetdate(),绑定缺省execsp_bindefaultd_Date,stu.goSchDate,自定义函数,21,Datepart参数(补充),Datepart参数可用的值Yearyy,yyyyquarterqq,qMonthmm,mdayofyeardy,yDaydd,dWeekwk,wwHourhhminutemi,nsecondss,smillisecondms,22,用户自定义函数,用CREATEFUNCTION创建自定义函数语法格式：CREATEFUNCTION所有者名称.函数名(参数名称AS数据类型=默认值,n)RETURNS返回值类型ASBEGIN函数体SQL语句RETURN数值表达式END,SQLServer2005允许用户自己定义所需要的函数。,23,用CREATEFUNCTION创建自定义函数,说明：l自定义函数必须在当前数据库中定义。l函数名：必须符合标识符构成规则，在数据库中名称必须惟一，省略所有者名称默认为系统管理员dbo。l参数名称：用局部变量定义的形式参数，用于接收调用函数时传递过来的参数。l默认值必须是常量，如果设定了默认值则调用函数时若不提供参数，形式参数自动取默认值。lRETURNS指定返回值类型，RETURN指定返回值，注意这两个关键字的区别。l自定义函数的调用与系统标准函数的调用相同，但必须写出“所有者名称.函数名”并在圆括号内给出参数。,24,用CREATEFUNCTION创建自定义函数,【例】定义一个根据出生日期求指定年份对应年龄的函数在数据库diannaoxs中创建一个名为“相对年龄”的用户自定义函数，根据员工的“出生日期”和“指定年份”计算员工到指定年份时的年龄。USEdiannaoxsGOCREATEFUNCTION相对年龄(出生年月Datetime,defyearint)RETURNSintASBEGINRETURNdefyear-year(出生年月)ENDGO如果在员工表查询到2008年小于30岁的员工，则可使用以下代码：SELECT姓名,出生日期,到2008的年龄=dbo.相对年龄(出生日期,2008)FROM员工表WHEREdbo.相对年龄(出生日期,2008)30注意：函数名前的所有者名称dbo不能省略。,Begin-End不能少,调用函数(dbo.不能少)selectdbo.func_date(2009-2-2),返回类型为表格，调用函数的语法(函数语句中不需要begin-end)：select*fromfunc_tableIdsfs(),25,用SQL语句修改、删除自定义函数,1、使用ALTERFUNCTION语句修改自定义函数ALTERFUNCTION语句语法与CREATEFUNCTION基本相同2、使用DROPFUNCYION语句删除自定义函数语法格式：DROPFUNCYION所有者名称.函数名称,n使用DROPFUNCYION可一次删除多个自定义函数。使用系统存储过程sp_droptype也可以删除自定义函数。,26,存储过程与用户自定义函数,存储过程是使用EXEC命令独立调用的，而用户自定义函数是在另一个SQL语句中调用的函数必须始终返回一个值(一个标量值或一个表格)。而存储过程可以返回一个标量值、一个表值或无需返回值,27,存储过程与用户自定义函数,存储过程与用户自定义函数都可以返回表createprocedureproc_testasselect*fromstuinfocreatefunctionfuncTest()returnsta