数据库编程初步学习教案.pptx

《数据库编程初步学习教案.pptx》由会员分享，可在线阅读，更多相关《数据库编程初步学习教案.pptx（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、会计学1数据库编程初步数据库编程初步(chb)第一页，共56页。本章本章(bn zhn)学习目标学习目标n n了解(lioji)基于数据库服务器的数据库编程基础；n n掌握Transact-SQL编程的基本语句和流程控制方法；n n理解游标的概念，掌握游标的使用方法；n n理解存储过程的概念，掌握存储过程的创建和调用方法；n n理解触发器的概念，掌握DML触发器的使用方法。第1页/共56页第二页，共56页。本章重点本章重点(zhngdin)和难点和难点n n本章学习的重点是掌握数据库编程的基本方法，要求学生能够理解游标、存储(cn ch)过程和触发器的概念，并能够运用Transact-SQL完

2、成相应的编程。n n本章学习的难点是能够灵活运用游标、存储(cn ch)过程和触发器完成相关的数据库应用开发。第2页/共56页第三页，共56页。4.1 T-SQL简介简介(jin ji)n nSQL Server使用的语言称作(chn zu)Transact-SQL(简称T-SQL)，它不仅包括基本SQL操作和查询的内容(第3章介绍的关系数据库标准语言SQL)，还有一般程序设计的能力。第3页/共56页第四页，共56页。变量变量(binling)及其说明及其说明n n用DECLARE语句(yj)说明内存变量 n nDECLARE ，.n n注意，变量名前必须有前缀，以便与关系的属性名相区分 第4

3、页/共56页第五页，共56页。变量变量(binling)的赋值的赋值n n对变量赋值的命令是 n nSELECT=,=n n或n nSET=n n或n nSELECT=,=FROM n n前两种格式是直接对变量进行(jnxng)赋值，第三种格式是将SELECT查询的结果赋值给变量。第5页/共56页第六页，共56页。关于关于(guny)数据类型数据类型n n整数 n n非整数数字 n n货币数据类型 n n字符(z f)数据类型 n n日期和时间数据类型 n n二进制数据类型 n n关于Unicode数据类型 n n特殊数据类型 第6页/共56页第七页，共56页。运算符与表达式运算符与表达式 n

4、 n算术(sunsh)运算符 n n位运算符 n n比较运算符 n n逻辑运算符 n n字符串运算符 第7页/共56页第八页，共56页。函数函数(hnsh)(hnsh)n n聚合函数 n n配置函数 n n加密函数 n n游标函数 n n日期(rq)和时间函数 n n数学函数 n元数据函数n行集函数 n安全函数 n字符串函数 n系统(xtng)函数 n系统(xtng)统计函数 第8页/共56页第九页，共56页。语句语句(yj)(yj)n n赋值语句 n nBEGINEND n n条件(tiojin)语句 n n循环语句 n nEXECUTE语句 n nWAITFOR语句 n nPRINT语句

5、n nRETURN语句 第9页/共56页第十页，共56页。思考题思考题 n nnumericnumeric和和floatfloat数数据据类类型型都都是是非非整整数数类类型型，它它们们有有什什么么区区别别？在在使使用用时要注意哪些问题？时要注意哪些问题？n nmoneymoney（或或smallmoneysmallmoney）数数据据类类型型与与numericnumeric和和floatfloat数数据据类类型型有有什什么么区别？区别？n ncharchar和和varcharvarchar都是字符类型，它们在概念和使用上有哪些区别？都是字符类型，它们在概念和使用上有哪些区别？n n什么是什么是

6、UnicodeUnicode数据类型？数据类型？n n讨论讨论T-SQLT-SQL中的运算符与一般程序设计语言中运算符的异同之处。中的运算符与一般程序设计语言中运算符的异同之处。n nPRINTPRINT语语句句只只能能(zh(zh nnnn)输输出出单单个个字字符符型型常常量量或或表表达达式式，如如果果要要输输出出非非字字符符型型表表达达式式应应该该如如何何处处理理？如如果果要要输输出出多多个个表表达达式式应应该该如如何处理？何处理？第10页/共56页第十一页，共56页。游标游标(yubio)(yubio)（CursorCursor）n n什么(shn me)是游标？n n为什么(shn m

7、e)需要游标？nSELECT的查询结果是记录的集合，如果需要交互的处理这些记录、或者需要根据这些记录完成对其他(qt)信息的处理，则需要把查询结果暂时存储、并能够读取和使用这些信息。第11页/共56页第十二页，共56页。游标游标(yubio)的概念的概念n n游标(yubio)是查询结果的临时存储n n定义游标(yubio)n n使用游标(yubio)n n释放游标(yubio)第12页/共56页第十三页，共56页。定义定义(dngy)游标游标n n基本(jbn)格式n n DECLARE CURSOR FOR n n游标可以看作是一个临时存储或临时文件，它的内容就是SELECT查询的结果n

8、n游标定义的更多解释参见教材第13页/共56页第十四页，共56页。游标游标(yubio)的使用的使用n n打开游标n n从游标中读记录n n循环控制遍历游标的所有(suyu)记录n n关闭游标n n释放游标第14页/共56页第十五页，共56页。打开打开(d ki)(d ki)游标游标 n nOPEN 第15页/共56页第十六页，共56页。从游标从游标(yubio)(yubio)中读记录中读记录 n n基本格式(g shi)是 n n FETCH INTO,n n完整格式(g shi)参见教材第16页/共56页第十七页，共56页。循环控制遍历游标的所有循环控制遍历游标的所有(suyu)记录记录n

9、 n全局变量FETCH_STATUS n n一个系统预定义的全局变量、也可以(ky)看作是一个系统函数，取值如下：n n0：FETCH 语句成功；n n-1：FETCH 语句失败或行不在结果集中；n n-2：提取的行不存在。n n循环语句n nWHILE FETCH_STATUS=0 第17页/共56页第十八页，共56页。关闭关闭(gunb)(gunb)游标游标 n nCLOSE 第18页/共56页第十九页，共56页。释放释放(shfng)(shfng)游标游标 n nDEALLOCATE 第19页/共56页第二十页，共56页。游标游标(yubio)概念归纳概念归纳n n与程序设计语言中的文件

10、相对照：n nDECLARE CURSOR相当于说明了一个文件n nOPEN相当于打开文件n nFETCH相当于读一条记录n nCLOSE相当于关闭文件n nDEALLOCATE语句(yj)相当于删除文件。第20页/共56页第二十一页，共56页。游标游标(yubio)(yubio)查询处理举例查询处理举例 n n用游标逐条显示学生(xu sheng)信息说明变量说明游标打开游标从游标中读第一条记录到变量WHILE 读成功(chnggng)BEGIN 输出当前变量(记录)的信息 读下一条记录到变量END关闭和释放游标第21页/共56页第二十二页，共56页。游标游标(yubi(yubio)o)查查

11、询处理询处理举例举例n n用游标显示(xinsh)学生及其选课的信息(嵌套)说明变量说明学生游标打开学生游标从游标中读第一条学生记录到变量WHILE 读成功BEGIN 输出当前(dngqin)变量(记录)的信息 说明当前(dngqin)学生选课游标 打开选课游标 从游标中读第一条选课记录到变量 WHILE 读成功 BEGIN 输出当前(dngqin)变量(记录)的信息 读下一条选课记录到变量 END 关闭和释放选课游标 读下一条学生记录到变量END关闭和释放学生游标注意内外层都使用全局变量FETCH_STATUS第22页/共56页第二十三页，共56页。利用游标进行删除利用游标进行删除(shnc

12、h)(shnch)和和更新操作更新操作 n n使用游标的UPDATE命令的格式(g shi)是 n nUPDATE n nSET=|NULL,nn nWHERE CURRENT OF n n使用游标的DELETE命令的格式(g shi)是 n nDELETE FROM WHERE CURRENT OF n n利用WHERE CURRENT OF 进行的修改或删除只影响表的当前行。第23页/共56页第二十四页，共56页。使用游标进行更新使用游标进行更新(gngxn)(gngxn)的的例子例子 说明变量说明游标打开(d ki)游标从游标中读第一条记录到变量WHILE 读成功BEGIN 如果满足更新

13、条件 UPDATE WHERE CURRENT OF 读下一条记录到变量END关闭和释放游标第24页/共56页第二十五页，共56页。嵌入式嵌入式SQL n n非过程化与过程化n n必须结合(jih)程序才能完成应用，但是就数据查询和数据操作而言还是非过程化的第25页/共56页第二十六页，共56页。如何将如何将SQL嵌入到宿主嵌入到宿主(szh)语言中语言中 n n嵌入识别问题 n n宿主(szh)语言与SQL语言的数据交互问题 n n宿主(szh)语言的单记录与SQL的多记录的问题 第26页/共56页第二十七页，共56页。思考题思考题n n理解游标的概念，讨论游标的使用方法和用途。理解游标的概

14、念，讨论游标的使用方法和用途。n n在在SQL ServerSQL Server中全局变量是如何定义的？全局变量中全局变量是如何定义的？全局变量FETCH_STATUSFETCH_STATUS的用途是什么？再列举的用途是什么？再列举3-43-4个类似的全局个类似的全局变量，并说明它们的用途。变量，并说明它们的用途。n n讨论利用游标进行删除和更新操作的处理机制。讨论利用游标进行删除和更新操作的处理机制。n n为什么为什么SQLSQL有时候需要嵌入在程序设计语言中使用？有时候需要嵌入在程序设计语言中使用？SQLSQL嵌入在程序设计语言中使用时会遇到哪些问题嵌入在程序设计语言中使用时会遇到哪些问题

15、(wnt)(wnt)？这些问题这些问题(wnt)(wnt)是如何解决的？是如何解决的？第27页/共56页第二十八页，共56页。4.3 存储存储(cn ch)过程过程 n n存储过程（Stored Procedure）源于客户/服务器数据库体系结构，它与传统数据库结构的一个很重要的区别是，在传统的数据库中只存放数据，所有(suyu)的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起；而在客户/服务器结构的数据库中，在数据库中还可以存放程序，因为这种程序以数据库对象的形式存储在数据库中，所以称为存储过程。第28页/共56页第二十九页，共56页。客户客户(k h)/(k h)/服务器体系结构

16、服务器体系结构n n客户/服务器体系结构是一种合理配置客户端和服务器端应用负载(fzi)、充分利用系统资源、使系统服务效率更高的一种应用体系结构。第29页/共56页第三十页，共56页。非客户非客户/服务器应用服务器应用(yngyng)示意示意n n查询查询查询查询(chxn)(chxn)在客户在客户在客户在客户端完成端完成端完成端完成查询(chxn)表格30000条记录的表格第30页/共56页第三十一页，共56页。客户客户(k h)/服务器应用示意服务器应用示意n n查询查询(chxn)在服务在服务器端完器端完成成SQL请求(qngqi)返回一条记录第31页/共56页第三十二页，共56页。存储

17、存储(cn ch)过程的概念过程的概念n n存储在数据库中的程序n nT-SQL语句写的程序n nSQL Server 2005以后(yhu)存储过程还可以是对CLR方法的引用（Microsoft.NET Framework 公共语言运行时）n n在数据库服务器上运行n nSQL Server自身的很多功能也是使用存储过程实现的系统存储过程n n用户可以建立自己的存储过程第32页/共56页第三十三页，共56页。存储存储(cn ch)过程的作用过程的作用n n可以避免在网络上传输(chun sh)大量无用的信息或原始数据，只需要传输(chun sh)调用存储过程的指令和数据库服务器返回的处理结果

18、 n n把完成某一数据库处理的功能设计为存储过程，则可以在各个程序中反复调用，从而减轻程序的编写工作量 n n可以利用存储过程间接实现一些安全控制功能第33页/共56页第三十四页，共56页。存储过程存储过程(guchng)的使用的使用n n定义存储过程(guchng)n n执行存储过程(guchng)n n修改存储过程(guchng)n n删除存储过程(guchng)第34页/共56页第三十五页，共56页。定义存储定义存储(cn ch)过程过程n nparameterparameter：给出参数名（注意需要使用：给出参数名（注意需要使用做前缀）做前缀）n ndata_typedata_type

19、：指出参数的数据类型：指出参数的数据类型n nVARYINGVARYING：指指定定作作为为输输出出参参数数支支持持的的结结果果集集，该该参参数数由由存存储储过过程程动动态态构构造造，其其内内容容可可能能(knng)(knng)发生改变，仅适用于发生改变，仅适用于cursorcursor（游标）类型的参数。（游标）类型的参数。n n=defaultdefault：给给出出参参数数的的默默认认值值，该该值值必必须须是是常常量量或或NULLNULL，如如果果过过程程中中使使用用了了带带LIKE LIKE 关键字的参数，则可包含通配符关键字的参数，则可包含通配符%、_ _、和和 。n nOUT|OU

20、TPUTOUT|OUTPUT：指示参数是输出参数。：指示参数是输出参数。n nsql_statementsql_statement：存存储储过过程程所所要要执执行行的的SQLSQL语语句句，它它可可以以是是一一组组SQLSQL语语句句，可可以以包包含含流流程程控制语句等。控制语句等。CREATE PROCEDURE schema_name.procedure_name parameter data_type VARYING =default OUT|OUTPUT,AS sql_statement 第35页/共56页第三十六页，共56页。注意事项注意事项n n创建存储过程通常是在数据库设计和开发

21、阶段完成的n n存储过程可以嵌套，即在一个(y)存储过程中可以调用另外一个(y)存储过程n n存储过程一般用来完成数据查询和数据处理操作n n在存储过程中不可以使用创建数据库对象的语句（如CREATE TABLE等各种CREATE语句）。第36页/共56页第三十七页，共56页。执行存储执行存储(cn ch)(cn ch)过程过程 n nreturn_statusreturn_status：用用用用于于于于存存存存放放放放存存存存储储储储过过过过程程程程返返返返回回回回的的的的状状状状态态态态。这这这这个个个个变变变变量量量量在在在在执执执执行行行行存存存存储储储储过过过过程程程程前前前前必须说

22、明必须说明必须说明必须说明(shumng)(shumng)过。过。过。过。n nprocedure_nameprocedure_name：要执行或调用的存储过程名。：要执行或调用的存储过程名。：要执行或调用的存储过程名。：要执行或调用的存储过程名。n nparameterparameter：存储过程中定义的参数。：存储过程中定义的参数。：存储过程中定义的参数。：存储过程中定义的参数。n nvalue value：传传传传递递递递给给给给存存存存储储储储过过过过程程程程的的的的参参参参数数数数值值值值。如如如如果果果果参参参参数数数数名名名名称称称称没没没没有有有有指指指指定定定定，参参参参数数

23、数数值值值值必必必必须须须须以以以以在在在在存存存存储储储储过过过过程程程程中中中中定定定定义义义义的的的的顺顺顺顺序序序序提提提提供供供供。如如如如果果果果在在在在存存存存储储储储过过过过程程程程中中中中定定定定义义义义了了了了默默默默认认认认值值值值，则则则则可可可可以以以以不不不不必必必必指指指指定参数。定参数。定参数。定参数。n nvariable variable：用来存储参数或返回参数的变量。：用来存储参数或返回参数的变量。：用来存储参数或返回参数的变量。：用来存储参数或返回参数的变量。n nOUTPUTOUTPUT：说说说说明明明明(shumng)(shumng)是是是是输输输输

24、出出出出参参参参数数数数，用用用用于于于于从从从从存存存存储储储储过过过过程程程程返返返返回回回回值值值值。使使使使用用用用游游游游标标标标变量作为参数时必须使用该关键字。变量作为参数时必须使用该关键字。变量作为参数时必须使用该关键字。变量作为参数时必须使用该关键字。EXECUTE return_status=schema_name.procedure_name parameter=value|variable OUTPUT ,.n 第37页/共56页第三十八页，共56页。修改修改(xigi)存储过程存储过程n n命令是ALTER PROCEDURE n n相当于先删除(shnch)旧存储过程

25、，再建立新存储过程，所以语法格式与CREATE PROCEDURE相同。第38页/共56页第三十九页，共56页。删除删除(shnch)存储过程存储过程DROP PROCEDURE DROP PROCEDURE schema_nameschema_name.procedure_nameprocedure_name 第39页/共56页第四十页，共56页。存储过程存储过程(guchng)应用举例应用举例n n例4-4.使用简单过程n n查询课程信息（课程名称、学时、课程性质和责任教师的姓名）n n例4-5.使用带有参数的简单过程 n n返回指定类型课程的信息 n n例4-6.使用带有参数和返回值的简

26、单过程 n n查询指定课程大于指定成绩的学生人数，查询结果通过(tnggu)RETURN语句返回 第40页/共56页第四十一页，共56页。存储过程存储过程(guchng)应用举例应用举例n n例4-7.使用带有通配符参数的简单过程 n n返回指定的一些(yxi)课程的信息（提供课程名称中的关键字）n n例4-8.使用 OUTPUT 参数n n计算指定课程的及格率，一个参数传入指定课程，一个参数传出计算结果 n n例4-9.使用 cursor 数据类型的参数 n n在存储过程中使用游标第41页/共56页第四十二页，共56页。获得有关存储过程获得有关存储过程(guchng)(guchng)的的信息

27、信息 n n存储过程的定义可以通过当前数据库的sys.sql_modules和系统视图(sht)查询到 SELECT definition FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id=sys.objects.object_id WHERE TYPE=P第42页/共56页第四十三页，共56页。思考题思考题n n如何理解存储过程是存储在数据库服务器的？n n存储过程与传统程序设计语言或开发环境开发的过程有什么区别？n n查阅SQL Server 2005创建存储过程的完整命令格式(g shi)，讨论存储过程的

28、各种用途。第43页/共56页第四十四页，共56页。4.4 触发器触发器 n n触发器是一类特殊的存储过程，它在满足某个特定条件时自动(zdng)触发执行。n n分为DML(数据操作语言)触发器、DDL(数据定义语言)触发器和LOGIN触发器n nDML触发器在执行数据操作语言时触发n nDDL触发器在执行数据定义语言时触发n nLOGIN触发器在有用户登录时触发第44页/共56页第四十五页，共56页。DML触发器触发器n n定义在表上，当对表进行插入、删除或修改时触发，即可能使数据发生变化时触发n nDML触发器分为插入触发器、删除触发器和更新触发器三类n n触发器的三个要素：表、触发的事件、

29、采取的动作(dngzu)n n事件发生时自动触发，用户无选择的权利第45页/共56页第四十六页，共56页。DML触发器的用途触发器的用途(yngt)n n常用于强制业务规则、数据完整性和一些提示服务常用于强制业务规则、数据完整性和一些提示服务(fw)(fw)，如：，如：n n当在某些表上发生数据操作时可以及时在线提示或发送当在某些表上发生数据操作时可以及时在线提示或发送电子邮件给用户，以引起用户的关注电子邮件给用户，以引起用户的关注 n n触发器可以通过级联的方式对相关的表进行修改。例如，触发器可以通过级联的方式对相关的表进行修改。例如，对父表的修改，可以引起对子孙表的一系列修改，从而对父表的

30、修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性（简单的可以通过参照完整保证数据的一致性和完整性（简单的可以通过参照完整性的级联功能完成）性的级联功能完成）n n触发器可以禁止或撤消违反数据完整性的修改（一般可触发器可以禁止或撤消违反数据完整性的修改（一般可以用参照完整性约束完成）以用参照完整性约束完成）n n触发器可以强制定义比触发器可以强制定义比CHECKCHECK约束更加复杂的约束，特约束更加复杂的约束，特别是跨表的约束则只能使用触发器来实现别是跨表的约束则只能使用触发器来实现 第46页/共56页第四十七页，共56页。建立建立(jinl)触发器触发器 n nONON指出定

31、义触发指出定义触发(chf)(chf)器的表或视图器的表或视图 n nFOR INSERTFOR INSERT、UPDATEUPDATE或或DELETEDELETE指出触发指出触发(chf)(chf)事件事件n nsql_statementsql_statement给出处理过程给出处理过程CREATE TRIGGER schema_name.trigger_name ON table|view FOR INSERT ,UPDATE ,DELETE AS sql_statement 第47页/共56页第四十八页，共56页。触发器应用触发器应用(yngyng)(yngyng)举例举例 n n使用包

32、含(bohn)提醒消息的DML触发器。CREATE TRIGGER reminder1ON Student.课程FOR INSERT,UPDATE AS RAISERROR(注意(zh y)责任教师,16,10)执行如下UPDATE语句会发生什么：UPDATE student.课程SET 学时=32 WHERE 课程编号=X00206 则会在客户端显示如下信息：消息50000，级别16，状态10，过程reminder1，第5行 注意责任教师第48页/共56页第四十九页，共56页。deleted表和表和inserted表表 n n当DML触发器激活时系统会自动产生两个特殊的临时表：deleted

33、表和inserted表n n当发生INSERT操作(cozu)时新插入的记录也存储在inserted表n n当发生DELETE操作(cozu)时被删除的记录也存储在deleted表n n当发生UPDATE操作(cozu)时修改前的旧记录也存储在deleted表、修改后的新记录也存储在inserted表第49页/共56页第五十页，共56页。deleted表和表和inserted表的作用表的作用(zuyng)？n n可以使用deleted表和inserted表判断正在操作的记录是否符合要求，从而检查错误并采取相应的措施 n n找出数据修改前、后表的状态差异，并基于该差异采取相应的措施n n可以扩展

34、(kuzhn)表之间的参照完整性 deleted表和inserted表只在触发器内可用，一旦触发器完成(wn chng)任务，这两个系统产生的临时表将自动删除 第50页/共56页第五十一页，共56页。触发器应用触发器应用(yngyng)(yngyng)举例举例n n使用DML触发器在“课程”和“教师”表之间强制实现业务规则(guz)n n规定“专业基础”课的责任教师必须是“教授”或“副教授”，这时候“课程”表对“教师”表不是简单的参照，而必须定义相应的检查或约束规则(guz)，此规则(guz)不能简单用CHECK约束 实现，可以用触发器实现。第51页/共56页第五十二页，共56页。查看查看(c

35、hkn)(chkn)导致触发器触发的导致触发器触发的事件事件 n n通过查询和系统目录表可以(ky)查询导致触发器触发的事件 SELECT TE.*FROM sys.trigger_events AS TE JOIN sys.triggers AS TWHERE T.name=IsProfesor第52页/共56页第五十三页，共56页。思考题思考题n n查阅相关资料了解DDL触发器的概念(ginin)、用途和使用方法。n n全面讨论DML触发器的用途和使用方法。n n在DML触发器应用中deleted表和inserted表是怎么回事？讨论它们的用途。第53页/共56页第五十四页，共56页。本章本章(bn zhn)(bn zhn)小结小结 n nT-SQL的有关基础 n n游标n n存储(cn ch)过程n n触发器 第54页/共56页第五十五页，共56页。本章本章(bn zhn)实验实验n n实验6 游标 n n实验7 存储(cn ch)过程 n n实验8 触发器 n n具体要求参见教材第55页/共56页第五十六页，共56页。