SQL编程技术.ppt

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,第六章：,SQL,编程技术,6.1.SQL,编程基础,6.2.,游标及游标的应用,6.3.,存储过程,6.4.,数据库触发器,6.5.,嵌入式,SQL,6.1.SQL,编程基础,作用：,通过创建存储过程和包来编写业务规则，通过创建触发器对象触发数据库事件，或者对,SQL,命令的执行增加逻辑控制,运用内存变量、分支、循环等程序设计方法,6.1.1,批处理,批处理是一个以“,GO”,结束的,SQL,语句集，这些语句是作为一个组来执行并一起提交的,“,GO”,是,SQL Server 2000,批处理的结束标志，不是,SQL,语句。,脚本是一系列顺序执行的批处理,6.1.1,批处理,说明：,(1),如果,个批处理的所有语句正确执行，则整个批处理顺利提交；否则，批处理中，只要有一个语句错误则整个批处理都不提交。,(2),有些语句不能组合在同一批处理中，例如，,ALTER TABLE,修改表和,UPDATE,更新表中的新列放在一个批处理中，将会出现错误。,将包含多个批处理的一个程序段存放在以后缀为“,SQL”,的文件中，称为脚本文件。,批处理,批处理的特点,一次发给数据库服务器的所有的,SQL,语句，由,GO,作为结束标志。,编译成一个可执行单元,执行计划,执行时，每次执行计划中的一条语句,练习,1,问题：下面的标识符是否符合,SQL Server,的命名规则：,$table,table#,_var_1,order details,order,常量和变量,常量：,字符串常量,数值常量,日期常量,uniqueidentifier,常量,注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号,变量,变量：在程序运行过程中其值是变化的量,局部变量（,以,开头,；使用,DECLARE,语句声明）,【,问题,】,DECLARE var1 int,var2 money,SELECT var1=50,var2=$29.95,SELECT var1,var2,全局变量（也称配置函数，是系统定义的变量，以,开头；用户只能使用）,例如：,version,rowcount,练习,2,问题：,编写程序计算两个整数之和,运算符,算术运算符（,+,、*、,/,、,%,）,比较运算符（,=,、,、,=,、,=,、,、,!=,、,!,）,逻辑运算符（,NOT,、,OR,、,AND,、,IN,、,LIKE,、,BETWEEN,、,EXISTS,）,字符串连接运算符（）,赋值运算符（,=,）,位运算符（,&,、,|,、,、,）,常用函数,思考：,如何将一个数字转换为一个字符串？,如何截取字符串？,如何根据一个人的出生年月，使用日期函数计算年龄？,如何对一个数进行取整？进行四舍五入？,流控语句,【,问题,】,如何用,SQL,语句编程实现：求,1+2+3+4+100=?,编程思想：,代码：,分析：,WHILE,、,BEGINEND,、,IFELSE,、,GOTO,的使用用法,其他流控语句的使用用法,BRAEK,：,在循环语句中，用于,退出本层循环,CONTINUE,：,在循环语句中，,结束本次循环，重新转到下一次循环条件的判断,练习,3,编写程序，计算,n!,（,n=10,），,并将结果显示出来,流控语句,CASE,语句（开关语句）,【,练习,4】,在,Xk,数据库中显示班级编码、班级名称和系部名称（当系部编码为,01,时，显示系部名称为“计算机系”；当系部编码为,02,时，显示系部名称为“建筑系”；当系部编码为,03,时，显示系部名称为“旅游系”），要求使用,CASE,语句,流控语句,WAITFOR,语句：,WAITFOR DELAY time,延迟一段时间,WAITFOR TIME time,等待到一个时刻,2.,游标及游标的应用,问题,问题,在银行等重要交易中，要使一组,SQL,语句同时执行或撤消，如何做？,要对查询结果进行一行行的操作，如何做到？,事务概述,事务是作为单个逻辑工作单元,执行,的一系列操作。,思考：事务和批处理的区别？,事务的属性,（,ACID),原子性：原子工作单元,一致性：保证数据的一致性,隔离性：并发事务之间所做的修改要隔离,并发操作：几个用户程序同时读写一个数据的情况,持久性：对系统的影响要持久,简单事务编程,格式：,BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION,【,问题,】,简单的事务提交和回滚,说明：如果没有明确给出,BEGIN TRANSACTION,语句，则,SQL Server,是将每个,SQL,语句都当成一个事务进行执行,事务的嵌套,嵌套事务只有在提交了最外层的事务后，数据才执行永久修改,嵌套事务时，,ROLLBACK,语句只能将所有内层事务回滚到最外层的,BEGIN TRANSACTION,语句,封锁,利用封锁技术可以有效地保证数据的一致性,封锁粒度：可以锁定在行、列、表、数据库等不同的单元上,SQL Server,具有自动和强制封锁的功能,锁模式,锁模式：,共享锁（,Share Lock,）,排他锁（,Exclusive Lock,）,更新锁（,Update Lock,）,各种锁模式的兼容性：,共享锁,更新锁,排他锁,共享锁,YES,YES,NO,更新锁,YES,NO,NO,排他锁,NO,NO,NO,死锁,当两个事务,trans1,和,trans2,在下列的状态时，将产生死锁：,Trans1,：,存取数据项,X,和,Y,Trans2,：,存取数据项,Y,和,X,【,说明,】,如果事务,Trans1,封锁了数据项,X,，,事务,Trans2,封锁了数据项,Y,，则,Trans1,等待,Trans2,释放,Y,上的锁，,Trans2,等待,Trans1,释放,X,上的锁。因此，,Trans1,和,Trans2,都无限地等待对方打开锁住的数据项，则产生死锁。,死锁（续）,【,死锁示例,】,说明：,SQL Server,能自动发现并解除死锁,避免死锁的措施：,P.237,游标引例,以前：使用,SELECT,语句对表格进行查询，返回的结果集包括所有满足条件的行。,思考：如果要求每次只显示表格（例如上面的,Course,表）中的一行，该如何处理？,这在将,T-SQL,嵌入到其他高级语言（如,C,、,VC,、,Delphi,等）的编程中经常用到。,游标概述,可以将游标看作一种特殊的指针，它可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。,游标提供了对一个结果集进行逐行处理的能力：,在结果集中定位特定行,从结果集的当前位置检索行,支持对结果集中当前位置的行进行数据处理（修改,/,删除）,使用游标的步骤,声明游标,打开游标,处理数据（读取,/,修改,/,删除）,可以和其他,T-SQL,语句配合灵活使用,关闭游标（与打开游标配对）,删除游标（与声明游标配对，此时释放分配给游标的所有资源）,【,问题,】,游标基本使用的例子,相关语法格式,声明游标,DECLARE cursor_name CURSOR,FORWORD_ONLY|SCROLL,FOR select_statements,打开游标,OPEN cursor_name,使用游标,FETCH NEXT|PRIOR|FIRST|LAST,FROM cursor Into variable_name,关闭游标,CLOSE cursor_name,删除游标,DEALLOCATION cursor_name,练习,1,声明一个名为,CrsStudent,的游标，使其与“,00,电子商务班”的所有学生信息（学号、姓名）相关联，并逐行显示前三条学生信息,如果遍历整个结果集？,使用,fetch_status:,该全局变量,/,配置函数返回被最后,FETCH,语句执行的游标的状态，返回类型为,int,：,0,：,FETCH,语句成功,1:FETCH,语句失败或此行不在结果集中,2,：被提取的行不存在,练习,2,使用游标遍历,Course,表，输出报名人数最多的课程的信息（课程编码、课程名称、报名人数）,练习,3,建立一个存储过程，利用游标求,course,表中某系（用户给定系代码）所开课程的最大报名人数，并把课程名称和最大报名人数作为输出变量返回给用户。,3.,存储过程,概述,问题,要把某完成功能的,SQL,做成类似,C,语言的函数，供需要时调用，如何做？,什么是存储过程？,是一组被编译在一起的,T-SQL,语句的集合，它们被集合在一起以完成一个特定的任务。,存储过程的分类,系统存储过程,扩展存储过程（提供从,SQL Server,到外部程序的接口，以便进行各种维护活动）,用户自定义的存储过程,存储过程的作用,从以下几个方面考虑：,模块化编程：,创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。,快速执行：,存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。,减少网络通信量,有了存储过程后，在网络上只要一条语句就能执行一个存储过程。,安全机制,通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,存储过程和视图的比较,视图,存储过程,语句,只能是,SELECT,语句,可以包含程序流、逻辑以及,SELECT,语句,输入、,返回结果,不能接受参数，只能返回结果集,可以有输入输出参数，也可以有返回值,典型应用,多个表格的连接查询,完成某个特定的较复杂的任务,创建、执行简单的存储过程,创建格式：,CREATE PROCEDURE proc_name,AS,Sql_statements,【,问题,】,创建一个名为,p_Student,的存储过程，返回,student,表中班级编码为,20000001,的学生信息,执行格式：,EXEC proc_name,练习,1,创建一个名为,p_StuByClass,的存储过程，返回“,00,电子商务”班的所有学生的信息,执行该存储过程，并使用企业管理器查看该存储过程的定义,创建带输入参数的存储过程,创建格式：,CREATE PROCEDURE proc_name,para_name,datatype,=default,AS,Sql_statements,【,例,】,创建一个名为,p_StudentPara,的存储过程，该存储过程根据给定的班级编码显示该班级所有学生的信息,练习,2,创建一个名为,p_StuByPara,的,存储过程，根据给定的班级编码和学生学号，显示相应学生的信息,执行带输入参数的存储过程,执行格式：,EXEC proc_name,para_name,=value,指定参数名的格式,不指定参数名的格式,思考：当缺省参数执行存储过程时，如何处理？,带输出参数的存储过程,定义格式同上，同时指明,OUTPUT,关键字,创建、执行方法：,P202-203,【,练习,】,创建存储过程,P_ClassNum,，,要求能根据用户给定的班级代码，统计该班的人数，并将人数通过输出变量返回给用户。,如要查询班级代码为,20000002,的学生人数，如何调用存储过程,【,练习,】,创建存储过程,Cou_credit,，,要求能根据用户给定的学分值，统计满足该学分值的课程数目，并把它返回给调用程序。,如要统计,2,个学分的课程门数，该如何调用上述存储过程。,【,练习,】,创建名为,mod_credit,的存储过程，能修改用户指定课程号的课程学分值，修改值也由用户指定。,修改存储过程,修改格式：,ALTER PROCEDURE proc_name,para_name,datatype,=default,WITH ENCRYPTION,AS,Sql_statements,重命名、删除存储过程,重命名：,使用企业管理器,注意：要修改两个地方,删除格式：,DROP PROCEDURE proc_name,重编译处理（,RECOMPILE,）,在,创建时指明重编译,在执行时指明重编译,通过系统存储过程设定重编译选项,系统存储过程、扩展存储过程,sp_addlog,创建登录,ID,问题：为何执行该存储过程时可以带一个、两个或者三个参数？,xp_cmdshell,执行操作系统命令,x,p_enumgroups,列出域中的组的信息,x,p_loginconfig,报告,SQL Server,的登录安全信息,4.,数据库触发器,引例,思考：当学生报名选修,SQLServer,实用技术课程时，,WillNum,应自动加,1,，如何处理？,触发器的作用,在指定的表中数据发生变化时被调用以响应,INSERT,、,UPDATE,或,DELETE,事件,强制执行业务规则,创建简单的触发器,【,问题,】,创建一个触发器,Test1,，,要求每当在,Student,表中修改数据时，向客户端显示一条“记录已修改！”的消息,SQL,格式：,CREATE TRIGGER trigger_name,On table,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF INSERT UPDATE DELETE,AS,sql_statement,在,企业管理器中查看触发器信息,FOR/AFTER/INSTEAD OF,的比较,【,问题,】,将上例中触发器中的,FOR UPDATE,改为,INSTEAD OF UPDATE,，,查看执行结果有何不同。,总结：,使用,FOR/AFTER,时，执行,触发,SQL,语句（,INSERT/UPDATE/DELETE),：,在触发,SQL,语句成功执行后，再执行,触发器,使用,INSTEAD OF,时：执行,触发器,而不执行,触发,SQL,语句,练习,1,创建一个触发器，要求每当在,StuCou,表中插入数据时，向客户端显示一条“记录已添加！”的消息。,创建一个触发器，要求每当用户插入,student,表的记录时，自动显示表中所有内容,INSERTED,表和,DELETED,表,系统为每个触发器都自动创建了,INSERTED,表和,DELETED,表,INSERTED,表用于存放插入操作时的行；,DELETED,表用于存放删除操作时的行,这两个表的结构和被触发器作用的表的结构相同,用户不能对这两个表进行修改，但可以读取,练习,2,创建一个触发器，要求每当用户插入,student,表的记录后，自动显示,Student,表中插入的记录,创建触发器,【,问题,】,创建一个触发器,SetWillNum,，,要求当插入、更新、删除,StuCou,表的选课记录时，能更新,Course,表中相应的报名人数。,说明：该触发器只适用于每次新增、删除一条记录的情况。,创建批量数据触发器,思考：若有批量的数据新增、插入或删除，应该如何修改上例中的触发器？,处理多条记录的触发器简单、易维护，但每次都更新,Course,中每条记录的报名人数，效率不高,IF UPDATE,的,应用,【,问题,】,创建一个触发器,Test1,，,要求每当,student,表中修改,pwd,列的数据时，向客户端显示一条消息“密码已更改”的消息。,特点：只有当,IF UPDATE,指明的列发生修改时，才触发执行触发器。,级联更新和删除：,【,练习,】,创建触发器，当修改,student,表中的学号字段后，自动修改,stucou,表中相应的学号。（以前采用外键方式）,【,练习,】,在,student,表上创建触发器,del_trg,，,当删除表中某一个学生的记录时，能自动删除掉该学生的所有成绩记录,重命名、删除触发器,重命名触发器,SQL,格式：,sp_rename,oldname,newname,删除触发器,SQL,格式：,DROP TRIGGER trigger_name,说明：删除触发器所在的表时，,SQL Server,将会自动删除与该表相关的触发器,管理触发器,禁用触发器,ALTER TABLE table_name,DISABLE TRIGGER trigger_name,恢复使用触发器,ALTER TABLE table_name,ENABLE TRIGGER trigger_name,显示数据库中有哪些触发器,请参考联机丛书“,sysobjects,系统表”,使用注意事项总结,CREATE TRIGGER,要在单个批处理中执行,ON table,中的表格只能是一个,在同一条,CREATE TRIGGER,语句中，可以为多个事件（,INSERT/DELETE/UPDATE,定义相同的触发器操作）,可以为每个事件（,INSERT/DELETE/UPDATE,）,创建多个触发器,