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