第9章存储过程和事务.ppt
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 事务
- 资源描述:
-
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,1,第,9,章 存储过程和事务,9.1,存储过程概述,9.2,创建管理简单存储过程,9.3,创建管理带参的存储过程,9.4,事务,2,9.1,存储过程概述,1.,存储过程概念,存储过程是为了实现某个特定任务,由一组预先编译好的,SQL,语句组成,将其放在服务器上,由用户通过指定存储过程的名字来执行的一种数据库对象。,2.,存储过程类型,系统存储过程:,以,SP_,为前缀,是由,SQL Server2005,自己创建、管理和使用的一种特殊的存储过程,不能对其进行修改或删除。如,sp_helpdb,、,sp_renamedb,等,扩展存储过程:,以,XP_,为前缀,,,SQLSERVER,的实例可以动态加载和运行的,DLL,,直接在实例地址空间中运行,可以使用,SQL SERVER,扩展存储过程,API,完成编程。如,xp_servicecontrol,(停止或启动某个服务),用户自定义存储过程:,由用户自行创建的存储过程,可以输入参数、向客户端返回表格或结果、消息等,也可以返回输出函数,3,9.2,创建管理简单的存储过程,9.2.1,无参,存储过程的创建,9.2.2,无参,存储过程的执行,9.2.3,查看存储过程,9.2.4,修改存储过程,9.2.5,编译存储过程,9.2.6,删除存储过程,4,9.2.1,无参,存储过程的创建,使用,SQL,语句,创建存储过程,1,)语法格式如下:,CREATE PROC,EDURE,procedure_name,AS,sql_statement .n,2,),语法注释:,procedure_name,:新建存储过程的名称,其名称必须符合标识符命名规则,且对于数据库及其所有者必须唯一。,sql_statement,:指存储过程中的任意数目和类型的,Transact-SQL,语句。,5,9.2.1,无参,存储过程的创建,例,1,:,在,PUBS,数据库中,创建一个名称为,pr_searchorddate,的存储过程,该存储过程将查询出,sales,表中订购日期,ord_date,在,1994,年以后的记录信息。,代码如下:,CREATE PROC,pr_searchorddate,AS,SELECT*,FROM sales,WHERE ord_date=1994-1-1,GO,8,9.2.2,无参,存储过程的执行,对存储在服务器上的存储过程,可以使用,EXECUTE,命令或其名称执行它,其语法格式如下:,EXEC,UTE procedure_name,对上例的存储过程,pr_searchorddate,的执行语句如下:,EXECUTE pr_searchorddate,9,9.2,创建管理简单的存储过程,9.2.1,无参,存储过程的创建,9.2.2,无参,存储过程的执行,9.2.3,查看存储过程,9.2.4,修改存储过程,9.2.5,编译存储过程,9.2.6,删除存储过程,10,9.2.3,查看存储过程,1.,使用对象资源管理器查看存储过程,2.,使用系统存储过程查看存储过程信息,在,SQL Server,中,可以使用,sp_helptext,、,sp_depends,、,sp_help,等系统存储过程来查看存储过程的不同信息。,1,)使用,sp_helptext,查看存储过程的文本信息,.,其语法格式为:,sp_helptext,存储过程名,2,)使用,sp_depends,查看存储过程的相关性,.,其语法格式为:,sp_depends,存储过程名,3,)使用,sp_help,查看存储过程的一般信息,.,其语法格式为:,sp_help,存储过程名,11,9.2.4,修改存储过程,修改存储过程语法格式为:,ALTER PROC,DURE,procedure_name,parameter data_type,=defaultOUTPUT,n,WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,AS,Sql_statement,12,9.2.4,修改存储过程,例,14,:修改存储过程,pr_searchorddate,查询出,sales,表中,订购日期在,93,年以后的订单。,其程序清单如下:,Alter PROC,pr_searchorddate,AS,SELECT*,FROM sales,WHERE ord_date=1993-1-1,GO,13,9.2.5,编译存储过程,在我们使用了一次存储过程后,可能会因为某些原因,必须向表中新增加数据列或者为表新添加索引,从而改变了数据库的逻辑结构。这时,需要对存储过程进行重新编译,,SQL Server,提供三种重新编译存储过程的方法,:,1,、在建立存储过程时设定重新编译,语法格式:,CREATE PROCEDURE,procedure_name,WITH RECOMPILE,AS,sql_statement,2,、在执行存储过程时设定重编译,语法格式:,EXECUTE,procedure_name,WITH,RECOMPILE,3,、通过使用系统存储过程设定重编译,语法格式为:,EXEC,sp_recompile OBJECT,14,第,9,章 存储过程,9.1,存储过程概述,9.2,创建管理简单存储过程,9.3,创建管理带参的存储过程,9.4,其他存储过程,15,9.3,创建管理带参的存储过程,9.3.1,创建带参存储过程的语法,9.3.2,创建,/,执行带输入参数的存储过程,9.3.3,创建,/,执行带输出参数的存储过程,9.3.4,存储过程的返回值,16,9.3.1,创建,带参,存储过程的语法,1,)语法格式如下:,CREATE PROCDURE procedure_name,parameter data_type,=defaultOUTPUT,n,WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,AS,Sql_statement,17,9.3.1,创建,带参,存储过程的语法,2,),语法注释,:,parameter,:存储过程中的输入和输出参数。,data_type,:参数的数据类型。,OUTPUT,:表明参数是返回参数。该选项的值可以返回给,EXECUTE,。,18,9.3,创建管理带参的存储过程,9.3.1,创建带参存储过程的语法,9.3.2,创建,/,执行带输入参数的存储过程,9.3.3,创建,/,执行带输出参数的存储过程,9.3.4,存储过程的返回值,19,9.3.2,创建,/,执行带输入参数的存储过程,例,2,:在,PUBS,数据库中创建一个存储过程,pr_searchempl,查询出,authors,表中,state,字段为某个州且姓中包含某字符串的所有的员工信息。,CREATE PROC,pr_searchempl,state char(2),str varchar(40),As,Select*,From authors,Where state=state and,au_lname like%+str+%,20,9.3.2 创建/执行带输入参数的存储过程,语法格式如下:,EXEC,UTE,return_status=,procedure_name,parameter=value|variable,OUTPUT,|DEFAULT ,.n,WITH RECOMPILE,对上例的存储过程,pr_searchempl,的执行语句如下:,EXECUTE pr_searchempl CA,hi,21,9.3.2,创建,/,执行带输入参数的存储过程,例,3,:查询出,northwind,数据库中城市值为某某值并且雇,佣时间在某某日期之后的所有员工的基本信息。,实现的,T-SQL,语句,:,USE northwind,GO,CREATE PROC p_getEmployee,city nvarchar(15),hiredate datetime,AS,SELECT*FROM employees WHERE city=city AND hiredate=hiredate,GO,22,9.3.2,创建,/,执行带输入参数的存储过程,执行带输入参数的存储过程,按位置传递参数值,在执行存储过程的语句中,直接给出参数的值。当有多个参数时,给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致,即参数传递的顺序就是参数定义的顺序。,通过参数名传递参数值,在执行存储过程的语句中,使用,【,参数名,=,参数值,】,的形式给出参数值。其优点是参数可以以任意顺序给出。,23,9.3.2,创建,/,执行带输入参数的存储过程,在输入参数中使用默认值,在执行存储过程,p_getEmployee,时,如果没有指定参数,则系统运行就会出错;此时如果希望在执行时不给出参数也能正确运行,,则在创建存储过程时给输入参数指定默认值。,24,9.3.2,创建,/,执行带输入参数的存储过程,按位置传递参数值,EXEC p_getEmployee london,1994-1-1,通过参数名传递参数值,EXEC p_getEmployee city=london,hiredate=1994-1-1,或,EXEC p_getEmployee,hiredate=1994-1-1,city=london,25,9.3.2,创建,/,执行带输入参数的存储过程,例,4,:对存储过程,p_getEmployee,进行修改,指定城市默认值为,london,,指定雇佣日期为,1990,年,1,月,1,日。,USE northwind,GO,ALTER PROC p_getEmployee,city nvarchar(15)=london,hiredate datetime=1990-1-1,AS,SELECT*FROM employees WHERE city=city AND hiredate=hiredate,GO,EXEC p_getEmployee,26,9.3,创建管理带参的存储过程,9.3.1,创建带参存储过程的语法,9.3.2,创建,/,执行带输入参数的存储过程,9.3.3,创建,/,执行带输出参数的存储过程,9.3.4,存储过程的返回值,27,9.3.3,创建,/,执行带输出参数的存储过程,例,5,:在,PUBS,数据库中创建一个存储过程,pr_titleprice,统计出,titles,表中,pub_id,字段为某编号的书籍总价格。,CREATE PROC,pr_titleprice,pub_id char(4),sprice money output,As,Select sprice=sum(price),From titles,Where pub_id=,pub_id,存储过程的执行,:,declare ss money,exec pr_titleprice 0877,ss output,select ss as,总价格,28,9.3.3,创建,/,执行带输出参数的存储过程,例,6,:创建一个存储过程,p_getCountEmployees,,用于统计,nothwind,数据库员工表中雇佣日期在某某时间之后的员工的个数。,实现的,T-SQL,语句,:,USE northwind,GO,CREATE PROC p_getCountEmployees,hiredate datetime=1990-1-1,count int OUTPUT,AS,SELECT count=count(*)FROM employees WHERE hiredate=hiredate,GO,29,9.3.3,创建,/,执行带输出参数的存储过程,调用存储过程的,T-SQL,语句,:,DECLARE ecount int,EXEC p_getCountEmployees 1993-1-1,ecount OUTPUT,SELECT,员工个数为:,+str(ecount),GO,30,9.3,创建管理带参的存储过程,9.3.1,创建带参存储过程的语法,9.3.2,创建,/,执行带输入参数的存储过程,9.3.3,创建,/,执行带输出参数的存储过程,9.3.4,存储过程的返回值,31,9.3.4,存储过程的返回值,存储过程在执行后都会返回一个整型值。如果执行成功,则返回,0,;否则返回,-1,到,-99,之间的随机数,也可以使用,RETURN,语句来指定一个存储过程的返回值。,32,9.3.4,存储过程的返回值,例,7,:在,northwind,数据库创建一个存储过程,返回产品表中的所有产品的库存量。,create proc pr_lier,as,begin,declare fanhuizhi int,select fanhuizhi=sum(unitsinstock),from products,return fanhuizhi,end,33,9.3.4,存储过程的返回值,接受这个返回值必须要用变量来接收,declare jieshouzhi int,exec jieshouzhi=pr_lier,print jieshouzhi,34,9.3.4,存储过程的返回值,上例中返回的值也可以用,output,参数来返回,可以将上例的存储过程改写为:,create proc pr_lier_2,fanhuizhi int output,as,begin,select fanhuizhi=sum(unitsinstock),from products,end,35,9.3.4,存储过程的返回值,接收,output,的返回值也必须要用变量,如:,declare jieshouzhi int,exec pr_lier_2 jieshouzhi output,print jieshouzhi,注意:,1)return返回的是整数,output可以返回任何数据。,2)如果让return返回非整数值,在创建存储过程时不会出错,但是运行存储过程时将会出错。,36,存储过程案例,例,9,:在,PUBS,数据库中创建一个带参数的存储过程,SelectUser,,查询出用户表,UserMember,中是否存在某用户,如果不存在,则返回值为,1,,否则则查询该用户的密码是否正确,如不正确,则返回值为,2,,否则返回值为,0,。,37,存储过程案例,create proc selectuser,username varchar(20),pass varchar(20),as,if username not in(select,用户名,from usermember)return(1),else if exists(select*from usermember,where,用户名,=username and,密码,=pass),return(0),else return(2),38,存储过程案例,执行存储过程语句如下,:,Declare fan int,Exec fan=selectuser aaa,aaa,If fan=0,Print,用户名和密码都正确,If fan=1,Print,用户名不存在,If fan=2,Print,用户名存在,密码不正确,39,9.4,事务的概述,1,)事务的概念,事务是最小的工作单元。这个工作单元要么成功完成所有操作,要么就是失败,并将所做的一切复原。,2,)事务特性,原子性(,Atomic,):,整个数据库事务是不可分割的工作单位,一致性(,ConDemoltent,):,指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性,独立性(,Isolated,):,在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。,持久性(,Durable,),:,只要事务成功结束,它对数据库所做的更新就必须永久保存下来,40,9.4,事务的概述,3,)事务的运行模式,显式事务,:,显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。,开始显式事务,,使用,BEGIN TRAN,。,结束显示事务,,使用,COMMIT TRAN,。,取消事务,,使用,ROLL BACK TRAN,命令。,隐式事务,:,在前一个事务完成时新事务隐式启动,但每个事务仍以,COMMIT,或,ROLLBACK,语句显示完成。,41,9.4,事务的概述,自动提交事务,每条单独的,SQL,语句都是一个事务,这是,SQL,默认的事务管理模式,每个,T-SQL,语句完成时,都被(成功)提交或(失败)回滚。,42,9.4,事务的管理与应用,1,、,BEGIN TRAN,语句,使用,BEGIN TRAN,语句主要是显示地命令,SQL SERVER,开始一个新事务,如果遇上错误,在,BEGIN TRAN,之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态。该语句主要用于显示事务中。其语法结构如下:,BEGIN TRAN|TRANSACTION,transaction_name|tran_name_variable,WITH MARK description,43,9.4,事务的管理与应用,1,、,BEGIN TRAN,语句,参数说明:,TRANSACTION,可简写为,TRAN,transaction_name,为事务的名称,其命名必须符合标识符规则,也可以省略不写。,tran_name_variable,指用户定义的含有有效事务名称的变量名称。,WITH MARK description,用于指定在日志中标记事务,44,9.4,事务的管理与应用,2,、,COMMIT TRAN,语句,COMMIT TRAN,语句用于提交事务的操作结果,如果执行事务直到它无误地完成,则可以使用该语句对数据库做永久的改动。其语句格式为:,COMMIT TRAN|TRANSACTION,transaction_name|tran_name_variable,45,9.4,事务的管理与应用,3,、,ROLLBACK TRAN,语句,ROLLBACK TRAN,语句用于当事务中的,T-SQL,语句发生错误时进行回滚操作,从而恢复数据库至事务开始之前的状态。其语法格式为:,ROLLBACK TRAN|TRANSACTION,transaction_name|tran_name_variable|,savepoint_name|savepoint_variable,46,9.4,事务的管理与应用,4,、,SAVE TRAN,语句,SAVE TRAN,语句允许部分地提交一个事务,同时仍能回滚这个事务的其余部分。其语法格式为:,SAVE TRAN|TRANSACTION,|savepoint_name|savepoint_variable,47,9.4,事务的管理与应用,例,1:,请用事务在,PUBS,数据库中创建一个存储过程,pr_auth_user,当向,authors,表中插入一个作者信息,同时将该作者的姓名插入到用户表中的,username,列,,pwd,列的初始值为用户名。,实现的,T-SQL,语句,:,CREATE TABLE usremember,(username varchar(80),Pwd varchar(50),Email varchar(50),Phone char(12),GO,48,9.4,事务的管理与应用,CREATE PROC pr_auth_user,au_id varchar(11),au_lname varchar(40),au_fname varchar(40),phone char(12),contract bit,-,一般将具有默认值的参数放在后边定义,便于调用时省略,address varchar(50)=null,city varchar(20)=null,state char(2)=null,zip char(5)=null,AS,49,9.4,事务的管理与应用,BEGIN TRAN,INSERT authors,VALUES(au_id,au_lname,au_fname,phone,address,city,state,zip,contract),IF ERROR0,BEGIN,ROLLBACK TRANSACTION,RETURN,END,50,9.4,事务的管理与应用,INSERT usermember(username,pwd),VALUES(au_fname+au_lname,au_fname+au_lname),IF ERROR0,BEGIN,ROLLBACK TRANSACTION,RETURN,End,COMMIT TRAN,GO,测试语句:,Exec pr_auth_user 111-11-1111,zhang,hai,101 110-0001,1,hfdjfdjkfdjkfd,changchun,CA,12345,GO,51,9.4,事务的管理与应用,例,2:,请用事务在数据库中创建一个存储过程,sp_DeleteUser,在,suser,、,patrolmembers,、,roleuser,表中删除指定的用户信息。,CREATE procedure sp_DeleteUser,uName varchar(20),as,declare TotalError int,set TotalError=0,begin tran,delete from suser where susname=uName,set TotalError=TotalError+error,delete from patrolmembers where pmnum=uName,52,9.4,事务的管理与应用,set TotalError=TotalError+error,delete from roleuser where userid=(select sUsNum from sUser where sUsName=uName),set TotalError=TotalError+error,if(TotalError=0),begin,commit tran,end,else,begin,rollback tran,end,GO,展开阅读全文

咨信网声明:本文档由用户上传并分享,仅供学习交流、研究之用,未经授权,严禁复制、发行、汇编、翻译或网络传播等,侵权必究。
咨信网小贴示:
1、浏览或下载可查看了解,请点击【咨信网告知】。
2、下载文件中如有侵权或不适当内容,将立即纠正。
3、如果因为网速或其他原因下载失败请重新下载,重复下载[60天内]不扣币。
4、开具发票登录电脑端申请,请点击【索取发票】。
咨信网客服组:【微信客服】
1、浏览或下载可查看了解,请点击【咨信网告知】。
2、下载文件中如有侵权或不适当内容,将立即纠正。
3、如果因为网速或其他原因下载失败请重新下载,重复下载[60天内]不扣币。
4、开具发票登录电脑端申请,请点击【索取发票】。
咨信网客服组:【微信客服】


第9章存储过程和事务.ppt
















自信AI助手














微信客服
客服QQ
发送邮件
意见反馈



链接地址:https://www.zixin.com.cn/doc/14673044.html