第4章数据库编程初步

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,数据库应用与设计,*,北京信息科技大学,第,4,章,数据库编程初步,一般用开发工具开发数据库应用，实际上在数据库管理系统内部也支持数据处理编程功能，本章以,SQL Server,为背景介绍数据库编程的一些基本内容。,本章学习目标,了解基于数据库服务器的数据库编程基础；,掌握,Transact-SQL,编程的基本语句和流程控制方法；,理解游标的概念，掌握游标的使用方法；,理解存储过程的概念，掌握存储过程的创建和调用方法；,理解触发器的概念，掌握,DML,触发器的使用方法。,数据库应用与设计,本章重点和难点,本章学习的重点是掌握数据库编程的基本方法，要求学生能够理解游标、存储过程和触发器的概念，并能够运用,Transact-SQL,完成相应的编程。,本章学习的难点是能够灵活运用游标、存储过程和触发器完成相关的数据库应用开发。,数据库应用与设计,4.1,T-SQL,简介,SQL Server,使用的语言称作,Transact-SQL(,简称,T-SQL)，,它不仅包括基本,SQL,操作和查询的内容(第3章介绍的关系数据库标准语言,SQL)，,还有一般程序设计的能力。,数据库应用与设计,变量及其说明,用,DECLARE,语句说明内存变量,DECLARE ,，, .,注意，变量名前必须有,前缀，以便与关系的属性名相区分,数据库应用与设计,变量的赋值,对变量赋值的命令是,SELECT =,=,或,SET =,或,SELECT =,=,FROM ,前两种格式是直接对变量进行赋值，第三种格式是将,SELECT,查询的结果赋值给变量。,数据库应用与设计,关于数据类型,整数,非整数数字,货币数据类型,字符数据类型,日期和时间数据类型,二进制数据类型,关于,Unicode,数据类型,特殊数据类型,数据库应用与设计,运算符与表达式,算术运算符,位运算符,比较运算符,逻辑运算符,字符串运算符,数据库应用与设计,函数,聚合函数,配置函数,加密函数,游标函数,日期和时间函数,数学函数,元数据函数,行集函数,安全函数,字符串函数,系统函数,系统统计函数,数据库应用与设计,语句,赋值语句,BEGINEND,条件语句,循环语句,EXECUTE,语句,WAITFOR,语句,PRINT,语句,RETURN,语句,数据库应用与设计,思考题,numeric,和,float,数据类型都是非整数类型，它们有什么区别？在使用时要注意哪些问题？,money,（,或,smallmoney,）,数据类型与,numeric,和,float,数据类型有什么区别？,char,和,varchar,都是字符类型，它们在概念和使用上有哪些区别？,什么是,Unicode,数据类型？,讨论,T-SQL,中的运算符与一般程序设计语言中运算符的异同之处。,PRINT,语句只能输出单个字符型常量或表达式，如果要输出非字符型表达式应该如何处理？如果要输出多个表达式应该如何处理？,数据库应用与设计,游标,（,Cursor）,什么是游标？,为什么需要游标？,SELECT,的查询结果是记录的集合，如果需要交互的处理这些记录、或者需要根据这些记录完成对其他信息的处理，则需要把查询结果暂时存储、并能够读取和使用这些信息。,数据库应用与设计,游标的概念,游标是查询结果的临时存储,定义游标,使用游标,释放游标,数据库应用与设计,定义游标,基本格式,DECLARE ,CURSOR FOR ,游标可以看作是一个临时存储或临时文件，它的内容就是,SELECT,查询的结果,游标定义的更多解释参见教材,数据库应用与设计,游标的使用,打开游标,从游标中读记录,循环控制遍历游标的所有记录,关闭游标,释放游标,数据库应用与设计,打开游标,OPEN ,数据库应用与设计,从游标中读记录,基本格式是,FETCH, ,INTO ,完整格式参见教材,数据库应用与设计,循环控制遍历游标的所有记录,全局变量,FETCH_STATUS,一个系统预定义的全局变量、也可以看作是一个系统函数,，,取值如下：,0,：,FETCH,语句成功；,-1,：,FETCH,语句失败或行不在结果集中；,-2,：,提取的行不存在。,循环语句,WHILE FETCH_STATUS=0,数据库应用与设计,关闭游标,CLOSE ,数据库应用与设计,释放游标,DEALLOCATE ,数据库应用与设计,游标概念归纳,与程序设计语言中的文件相对照：,DECLARE CURSOR,相当于说明了一个文件,OPEN,相当于打开文件,FETCH,相当于读一条记录,CLOSE,相当于关闭文件,DEALLOCATE,语句相当于删除文件。,数据库应用与设计,游标查询处理举例,用游标逐条显示学生信息,说明变量,说明游标,打开游标,从游标中读第一条记录到变量,WHILE,读成功,BEGIN,输出当前变量(记录)的信息,读下一条记录到变量,END,关闭和释放游标,数据库应用与设计,游标查询处理举例,用游标显示学生及其选课的信息(嵌套),说明变量,说明学生游标,打开学生游标,从游标中读第一条学生记录到变量,WHILE,读成功,BEGIN,输出当前变量(记录)的信息,说明当前学生选课游标,打开选课游标,从游标中读第一条选课记录到变量,WHILE,读成功,BEGIN,输出当前变量(记录)的信息,读下一条选课记录到变量,END,关闭和释放选课游标,读下一条学生记录到变量,END,关闭和释放学生游标,注意内外层都使用全局变量,FETCH_STATUS,数据库应用与设计,利用游标进行删除和更新操作,使用游标的,UPDATE,命令的格式是,UPDATE ,SET =|,NULL,n,WHERE CURRENT OF ,使用游标的,DELETE,命令的格式是,DELETE FROM ,WHERE CURRENT OF ,利用,WHERE CURRENT OF ,进行的修改或删除只影响表的当前行。,数据库应用与设计,使用游标进行更新的例子,说明变量,说明游标,打开游标,从游标中读第一条记录到变量,WHILE,读成功,BEGIN,如果满足更新条件,UPDATE WHERE CURRENT OF ,读下一条记录到变量,END,关闭和释放游标,数据库应用与设计,嵌入式,SQL,非过程化与过程化,必须结合程序才能完成应用，但是就数据查询和数据操作而言还是非过程化的,数据库应用与设计,如何将,SQL,嵌入到宿主语言中,嵌入识别问题,宿主语言与,SQL,语言的数据交互问题,宿主语言的单记录与,SQL,的多记录的问题,数据库应用与设计,思考题,理解游标的概念，讨论游标的使用方法和用途。,在,SQL Server,中全局变量是如何定义的？全局变量,FETCH_STATUS,的用途是什么？再列举3-4个类似的全局变量，并说明它们的用途。,讨论利用游标进行删除和更新操作的处理机制。,为什么,SQL,有时候需要嵌入在程序设计语言中使用？,SQL,嵌入在程序设计语言中使用时会遇到哪些问题？这些问题是如何解决的？,数据库应用与设计,4.3 存储过程,存储过程（,Stored Procedure,）,源于客户,/,服务器数据库体系结构，它与传统数据库结构的一个很重要的区别是，在传统的数据库中只存放数据，所有的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起；而在客户,/,服务器结构的数据库中，在数据库中还可以存放程序，因为这种程序以数据库对象的形式存储在数据库中，所以称为存储过程。,数据库应用与设计,客户/ 服务器体系结构,客户/服务器体系结构是一种合理配置客户端和服务器端应用负载、充分利用系统资源、使系统服务效率更高的一种应用体系结构。,数据库应用与设计,非客户/服务器应用示意,查询在客户端完成,查询表格,30000条记录的表格,数据库应用与设计,客户/服务器应用示意,查询在服务器端完成,SQL,请求,返回一条记录,数据库应用与设计,存储过程的概念,存储在数据库中的程序,T-SQL,语句写的程序,SQL Server 2005,以后存储过程还可以是对,CLR,方法的引用（,Microsoft .NET Framework,公共语言运行时）,在数据库服务器上运行,SQL Server,自身的,很多功能也是使用存储过程实现的,系统存储过程,用户可以建立自己的存储过程,数据库应用与设计,存储过程的作用,可以避免在网络上传输大量无用的信息或原始数据，只需要传输调用存储过程的指令和数据库服务器返回的处理结果,把完成某一数据库处理的功能设计为存储过程，则可以在各个程序中反复调用，从而减轻程序的编写工作量,可以利用存储过程间接实现一些安全控制功能,数据库应用与设计,存储过程的使用,定义存储过程,执行存储过程,修改存储过程,删除存储过程,数据库应用与设计,定义存储过程,parameter,：,给出参数名（注意需要使用,做前缀）,data_type,：,指出参数的数据类型,VARYING,：,指定作为输出参数支持的结果集，该参数由存储过程动态构造，其内容可能发生改变，仅适用于,cursor,（,游标）类型的参数。,=,default,：,给出参数的默认值，,该值必须是常量或,NULL,，,如果过程中使用了带,LIKE,关键字的参数，则可包含通配符,%,、,_,、,和,。,OUT | OUTPUT,：,指示参数是输出参数。,sql,_statement,：,存储过程所要执行的,SQL,语句，它可以是一组,SQL,语句，可以包含流程控制语句等。,CREATE PROCEDURE ,schema_name.,procedure_name, ,parameter data_type, VARYING ,=,default, OUT | OUTPUT , ,AS,sql,_statement,数据库应用与设计,注意事项,创建存储过程通常是在数据库设计和开发阶段完成的,存储过程可以嵌套，即在一个存储过程中可以调用另外一个存储过程,存储过程一般用来完成数据查询和数据处理操作,在存储过程中不可以使用创建数据库对象的语句（如,CREATE TABLE,等各种,CREATE,语句）。,数据库应用与设计,执行存储过程,return_status,：,用于存放存储过程返回的状态。这个变量在执行存储过程前必须说明过。,procedure_name,：,要执行或调用的存储过程名。,parameter,：,存储过程中定义的参数。,value,：,传递给存储过程的参数值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。如果在存储过程中定义了默认值，则可以不必指定参数。,variable,：,用来存储参数或返回参数的变量。,OUTPUT,：,说明是输出参数，用于从存储过程返回值。使用游标变量作为参数时必须使用该关键字。,EXECUTE ,return_status,= ,schema_name.,procedure_name,parameter,=, ,value,|,variable, OUTPUT ,.,n,数据库应用与设计,修改存储过程,命令是,ALTER PROCEDURE,相当于先删除旧存储过程，再建立新存储过程，所以语法格式与,CREATE PROCEDURE,相同。,数据库应用与设计,删除存储过程,DROP PROCEDURE,schema_name,.,procedure_name,数据库应用与设计,存储过程应用举例,例4-4,.,使用简单过程,查询课程信息（课程名称、学时、课程性质和责任教师的姓名）,例4-5,.,使用带有参数的简单过程,返回指定类型课程的信息,例4-6,.,使用带有参数和返回值的简单过程,查询指定课程大于指定成绩的学生人数，查询结果通过,RETURN,语句返回,数据库应用与设计,存储过程应用举例,例,4-7.,使用带有通配符参数的简单过程,返回指定的一些课程的信息（提供课程名称中的关键字）,例4-8,.,使用,OUTPUT,参数,计算指定课程的及格率，一个参数传入指定课程，一个参数传出计算结果,例4-9,.,使用,cursor,数据类型的参数,在存储过程中使用游标,数据库应用与设计,获得有关存储过程的信息,存储过程的定义可以通过当前数据库的,sys.,sql,_modules,和,sys.objects,系统,视图查询到,SELECT definition,FROM sys.,sql,_modules JOIN sys.objects,ON sys.,sql,_modules.object_id = sys.objects.object_id,WHERE TYPE = P,数据库应用与设计,思考题,如何理解存储过程是存储在数据库服务器的？,存储过程与传统程序设计语言或开发环境开发的过程有什么区别？,查阅,SQL Server 2005,创建存储过程的完整命令格式，讨论存储过程的各种用途。,数据库应用与设计,4.4,触发器,触发器是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。,分为,DML(,数据操作语言)触发器、,DDL(,数据定义语言)触发器和,LOGIN,触发器,DML,触发器在执行数据操作语言时触发,DDL,触发器在执行数据定义语言时触发,LOGIN,触发器在有用户登录时触发,数据库应用与设计,DML,触发器,定义在表上，当对表进行插入、删除或修改时触发，即可能使数据发生变化时触发,DML,触发器分为插入触发器、删除触发器和更新触发器三类,触发器的三个要素：表、触发的事件、采取的动作,事件发生时自动触发，用户无选择的权利,数据库应用与设计,DML,触发器,的用途,常用于强制业务规则、数据完整性和一些提示服务,，如：,当在某些表上发生数据操作时可以及时在线提示或发送电子邮件给用户，以引起用户的关注,触发器可以通过级联的方式对相关的表进行修改。例如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性（简单的可以通过参照完整性的级联功能完成）,触发器可以禁止或撤消违反数据完整性的修改（一般可以用参照完整性约束完成）,触发器可以强制定义比,CHECK,约束更加复杂的约束，特别是跨表的约束则只能使用触发器来实现,数据库应用与设计,建立触发器,ON,指出,定义触发器的表或视图,FOR INSERT、UPDATE,或,DELETE,指出触发事件,sql,_statement,给出处理过程,CREATE TRIGGER ,schema_name .,trigger_name,ON ,table,|,view,FOR, INSERT , UPDATE , DELETE ,AS,sql,_statement,数据库应用与设计,触发器应用举例,使用包含提醒消息的,DML,触发器。,CREATE TRIGGER reminder1,ON Student.,课程,FOR INSERT, UPDATE,AS,RAISERROR (,注意责任教师, 16, 10),执行如下,UPDATE,语句会发生什么,：,UPDATE student.,课程,SET,学时=32,WHERE,课程编号=,X00206,则会在客户端显示如下信息：,消息,50000,，级别,16,，状态,10,，过程,reminder1,，,第,5,行,注意责任教师,数据库应用与设计,deleted,表和,inserted,表,当,DML,触发器激活时系统会自动产生两个特殊的临时表：,deleted,表和,inserted,表,当发生,INSERT,操作时新插入的记录也存储在,inserted,表,当发生,DELETE,操作时被删除的记录也存储在,deleted,表,当发生,UPDATE,操作时修改前的旧记录也存储在,deleted,表、修改后的新记录也存储在,inserted,表,数据库应用与设计,deleted,表和,inserted,表的作用？,可以使用,deleted,表和,inserted,表判断正在操作的记录是否符合要求，从而,检查错误并采取相应的措施,找出数据修改前、后表的状态差异，并基于该差异采取相应的措施,可以扩展表之间的参照完整性,deleted,表和,inserted,表只在触发器内可用，一旦触发器完成任务，这两个系统产生的临时表将自动删除,数据库应用与设计,触发器应用举例,使用,DML,触发器在“课程”和“教师”表之间强制实现业务规则,规定,“,专业基础,”,课的责任教师必须是,“,教授,”,或,“,副教授,”,，这时候,“,课程,”,表对,“,教师,”,表不是简单的参照，而必须定义相应的检查或约束规则，此规则不能简单用,CHECK,约束,实现，可以用触发器实现。,数据库应用与设计,查看导致触发器触发的事件,通过查询,sys.triggers,和,sys.trigger_events,系统,目录表,可以,查询,导致触发器触发的事件,SELECT TE.*,FROM sys.trigger_events AS TE JOIN sys.triggers AS T,ON T.object_id = TE.object_id,WHERE T.name = ,IsProfesor,数据库应用与设计,思考题,查阅相关资料了解,DDL,触发器的概念、用途和使用方法。,全面讨论,DML,触发器的用途和使用方法。,在,DML,触发器应用中,deleted,表和,inserted,表是怎么回事？讨论它们的用途。,数据库应用与设计,本章小结,T-SQL,的有关基础,游标,存储过程,触发器,数据库应用与设计,本章实验,实验6 游标,实验7 存储过程,实验8 触发器,具体要求参见教材,数据库应用与设计,