TSQL基础和存储过程.ppt

1,SQL第2部分:T-SQL基础,T-SQL基础存储过程触发器,2,T-SQL语法基础,1注释2变量3运算符4函数5流程控制语句,3,1注释,注释是程序代码中不执行的文本字符串（也称为注解）。在SQLServer中，可以使用两种类型的注释字符：一种是ANSI标准的注释符“-”，它用于单行注释；另一种是与C语言相同的程序注释符号，即“/*/”。,4,2变量,变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。,5,局部变量,局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“”，而且必须先用DECLARE命令定义后才可以使用。定义格式:declarelocal_variabledata_type例如:declaresageint,snamechar(10),6,全局变量,全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQLServer的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。,全局变量不是由用户的程序定义的，它们是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头。局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,使用全局变量时应该注意以下几点：,例如:version返回当前服务器的日期版本和处理器类型等.rowcount返回最近的语句影响的行数,7,3运算符,运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQLServer2000中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串连接运算符。,8,算术运算符,算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）。,例如:Selectcast(100+900aschar(4)selectcast(2006-3-15assmalldatetime)+365as3.15后一年的日期,9,赋值运算符,Transact-SQL中只有一个赋值运算符，即等号（=）。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。,例如:Declaremyageintsetmyage=25或者selectsno=ec+sno,sname,sagefromstudent,Select可以用在查询语句中，set不可以,10,位运算符,位运算符使我们能够在整型数据或者二进制数据（image数据类型除外）之间执行位操作。此外，在位运算符左右两侧的操作数不能同时是二进制数据。,例如:Declareaint,bintSeta=2Setb=8Selectanumberparameterdata_type=defaultoutput,ASsql_statement,sprocedure_name：给出存储过程名；snumber：对同名的存储过程指定一个序号(允许同名)；sparameter：给出参数名；sdata_type：指出参数的数据类型；Output:返回值参数s=default：给出参数的默认值；ssql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,70,例：创建一个最简单的存储过程：10.8,CREATEProceduresp_getstu;1ASSELECT*FROM学生,71,例：带参数的存储过程,查询大于指定年龄的学生,CREATEPROCeduresp_getstu;2(sageint)ASSELECT*FROM学生WHERE年龄sage,72,注意事项:存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句：CREATETABLECREATEVIEWCREATEDEFAULTCREATERULECREATETRIGGERCREATEPROCEDURE,73,执行存储过程,EXECute=|,74,例：执行带参数的存储过程,查询大于22岁的学生,executesp_getstu;222,75,例：带输出参数的存储过程,查询指定学生的年龄CREATEPROCeduresp_getstu;3(namechar(10),ageintoutput)ASSELECTage=年龄FROM学生WHERE姓名=nameDeclaresageintExecsp_getstu;3张三,sagePrintsage,执行存储过程时参数的使用方法有两种1.通过位置如查询指定系大于指定年龄的学生CREATEPROCsp_getstu;4(classchar(10),sageint)ASSELECT*FROMstudentWHEREclass=classandSagesageExecsp_getstu;4电商,202.通过引用Execsp_getstu;4class=电商,sage=20注意输入参数名称要和过程内部定义的一致，输出参数可以不同。,77,存储过程的返回值和状态信息,无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。,78,CREATEPROCeduresp_getstu;3(sageint=NULL)ASIFsageISNULLBEGINPRINT必须提供一个数值作参数！RETURN13ENDIFNOTEXISTS(SELECT*FROMstudentWHEREsagesage)BEGINPRINT没有满足条件的记录！RETURN-103ENDSELECT*FROMstudentWHEREsagesageRETURN0,例：带参数和返回状态值的存储过程。,79,例：执行以上存储过程。,DECLAREstatusintEXECUTEstatus=sp_getstu;322printstatus,80,存储过程的修改和删除,修改存储过程的语句是（一般格式）：ALTERPROCedureprocedure_name;numberparameterdata_type=default,ASsql_statement删除存储过程的语句是：DROPPROCedureprocedure_name注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。,81,充分利用用好存储过程,存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。,82,存储过程应用示例,在外部环境中调用存储过程.演示:环境:sqlserver2000+VB,83,*创建存储过程的sql*CREATEPROCEDUREmyprocsdeptchar(10),sagesmallintASSELECT*FROMstudentWHEREsdept=sdeptANDsagesage,84,关键代码,DimmycommandAsADODB.Command命令Dimparm_deptAsADODB.Parameter参数1Dimparm_ageAsADODB.Parameter参数2Setmycommand=NewADODB.CommandSetparm_dept=NewADODB.ParameterSetparm_age=NewADODB.Parametermycommand.Parameters.Appendparm_dept加入参数mycommand.Parameters.Appendparm_age,85,mycommand.CommandText=myprocmyprocedure是要调用的存储过程名称mycommand.CommandType=adCmdStoredProc表明command为存储过程SetrstByQuery=NewADODB.RecordsetSetrstByQuery=mycommand.Execute()调用存储过程返回一个结果集,练习,定义一个存储过程，要求依据输入的学生学号，显示该生姓名及选修课程平均成绩。Student(sno,sname,sage,sex,sdept)Sc(sno,cno,score)Course(cno,cname,credit),87,触发器,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。,触发器的定义,88,触发器,1.触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。2.触发器可以禁止或撤消违反参照完整性的修改。3.触发器可以强制比用CHECK约束定义更加复杂的限制。4.通过触发器实现安全性控制.,触发器的用途,89,触发器,一个触发器和三部分内容有关：定义触发器的表激活触发器的数据操作语句触发器要采取的动作,触发器是依附于表的数据库对象,90,触发器,CREATETRIGGERtrigger_nameONtableFORINSERT|UPDATE|DELETEASIFUPDATE(column)AND|ORUPDATE(column)sql_statement,SQLserver中建立触发器的语句,91,触发器,CREATETRIGGERtest_triggerONstudentFORINSERTASPRINT插入了一个元组,例：建立一个简单的触发器。,92,触发器,为触发器运行而自动派生的两个视图：inserted存放刚插入的新记录deleted存放刚删除的旧记录,插入视图和删除视图,触发器分类,插入类触发器insert删除类触发器delete更新类触发器update,93,触发器,createtriggertri1onstudentforinsert,update,deleteasifexists(selectcount(*)frominserted)beginprint来自inserted表select*frominsertedendifexists(selectcount(*)fromdeleted)beginprint来自deleted表select*fromdeletedend,查看inserted和deleted视图内容,updatestudentsetsage=27wheresno=2003004insertintostudent(sno,sname)values(2003011,21)deletefromstudentwheresno=2003011,94,触发器,触发时机After|For、insteadof,After触发器是在SQLServer服务器接到执行SQL语句请求之后，先建立临时的Inserted表和Deleted表，然后实际更改数据，最后才激活触发器的。InsteadOf触发器在SQLServer服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，然后就触发了InsteadOf触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，一概不管，把执行权全权交给了InsteadOf触发器，由它去完成之后的操作。,95,CREATETRIGGERdel_triggerONSTUDENTFORDELETEASDELETEFROMSCWHERESNO=(SELECTSNOFROMdeleted),触发器,例：定义一个触发器，使得当删除学生记录时，同时将所有该学生选课记录删除,96,-CREATETRIGGERins_trigger-ONSCFORINSERT-AS-IF(SELECTCOUNT(*)FROMSTUDENTs,insertedi-WHEREs.SNO=i.SNO)=0-BEGIN-RAISERROR(THISSTUDENTISNOTEXISTS！,1,1)-ROLLBACKTRANSACTION-END,触发器,例：对选课表的插入操作定义一个触发器，使得当插入选课记录时，检查相应的学生元组是否存在，如果不存在则撤消所做的插入操作。,97,触发器,例：对学生表的插入操作定义一个触发器，使得当插入记录时，检查相应的学生年龄是否满足条件，如果不存在则显示错误信息。CREATETRIGGERstu_triONstudentINSTEADOFINSERTASif(SELECTagefrominserted)40print不能插入大于40岁的学生纪录elseinsertintostudentselect*frominserted,98,练习,当修改学生表student中学生的学号，通过触发器自动更正选课表SC与学生表一致。,99,游标,需要游标的数据操作当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组活动集：select语句返回的元组的集合当前行：活动集中当前处理的那一行。游标即是指向当前行的指针,100,游标的使用,定义与使用游标的语句declare定义一个游标，使之对应一个select语句declare游标名scrollcursorforselect语句forupdateof列表名forupdate任选项，表示该游标可用于对当前行的修改与删除,101,游标的使用10.9,open打开一个游标，执行游标对应的查询，结果集合为该游标的活动集open游标名fetch在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中fetchnext|prior|first