存储过程和事务.ppt

《存储过程和事务.ppt》由会员分享，可在线阅读，更多相关《存储过程和事务.ppt（52页珍藏版）》请在三一办公上搜索。

1、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，直接在实例地址空间中运

2、行，可以使用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_statem

3、ent.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,6

4、,创建存储过程的注意事项,只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。,7,9.2 创建管理简单的存储过程,9.2.1 无参存储过程的创建9.2.2 无参存储过程的执行9.2.3 查看存储过程9.2.4 修改存储过程 9.2.5 编译存储过程9.2.6 删除存储过程,8,9.2.2 无参存储过程的执行,对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：EXEC UTE p

5、rocedure_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_helpt

6、ext查看存储过程的文本信息.其语法格式为：sp_helptext 存储过程名 2）使用sp_depends查看存储过程的相关性.其语法格式为：sp_depends 存储过程名 3）使用sp_help查看存储过程的一般信息.其语法格式为：sp_help 存储过程名,11,9.2.4 修改存储过程,修改存储过程语法格式为：ALTER PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS Sql_statement,12,9.2.4 修改

7、存储过程,例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 proc

8、edure_name WITH RECOMPILE AS sql_statement2、在执行存储过程时设定重编译 语法格式：EXECUTE procedure_name WITH RECOMPILE3、通过使用系统存储过程设定重编译 语法格式为：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

9、 存储过程的返回值,16,创建带参存储过程的语法,1）语法格式如下：CREATE PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS Sql_statement,17,创建带参存储过程的语法,2）语法注释：parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。OUTPUT：表明参数是返回参数。该选项的值可以返回给EXECUTE。,18,9.3 创建管理带参的存储过程,9.3.1 创建带参存储过程的语法

10、9.3.2 创建/执行带输入参数的存储过程 9.3.3 创建/执行带输出参数的存储过程 9.3.4 存储过程的返回值,19,9.3.2 创建/执行带输入参数的存储过程,例2：在PUBS数据库中创建一个存储过程pr_searchempl,查询出authors表中state字段为某个州且姓中包含某字符串的所有的员工信息。CREATE PROC pr_searchemplstate char(2),str varchar(40)AsSelect*From authorsWhere state=state and au_lname like%+str+%,20,9.3.2 创建/执行带输入参数的存储过

11、程,语法格式如下：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 northwindGOCREATE PROC p_getEmployeecity nvarch

12、ar(15),hiredate datetimeAS SELECT*FROM employees WHERE city=city AND hiredate=hiredateGO,22,9.3.2 创建/执行带输入参数的存储过程,执行带输入参数的存储过程 按位置传递参数值 在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。通过参数名传递参数值 在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。,23,9.3.2 创建/执行带输入参数的存储过程,在输

13、入参数中使用默认值 在执行存储过程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：

14、对存储过程p_getEmployee进行修改，指定城市默认值为london，指定雇佣日期为1990年1月1日。USE northwind GOALTER PROC p_getEmployeecity nvarchar(15)=london,hiredate datetime=1990-1-1AS SELECT*FROM employees WHERE city=city AND hiredate=hiredateGOEXEC p_getEmployee,26,9.3 创建管理带参的存储过程,9.3.1 创建带参存储过程的语法 9.3.2 创建/执行带输入参数的存储过程 9.3.3 创建/执行带

15、输出参数的存储过程 9.3.4 存储过程的返回值,27,9.3.3 创建/执行带输出参数的存储过程,例5：在PUBS数据库中创建一个存储过程pr_titleprice,统计出titles表中pub_id字段为某编号的书籍总价格。CREATE PROC pr_titleprice pub_id char(4),sprice money outputAsSelect sprice=sum(price)From titlesWhere pub_id=pub_id存储过程的执行:declare ss money exec pr_titleprice 0877,ss output select ss a

16、s 总价格,28,9.3.3 创建/执行带输出参数的存储过程,例6：创建一个存储过程p_getCountEmployees，用于统计nothwind数据库员工表中雇佣日期在某某时间之后的员工的个数。实现的T-SQL语句:USE northwindGOCREATE PROC p_getCountEmployeeshiredate datetime=1990-1-1,count int OUTPUTAS SELECT count=count(*)FROM employees WHERE hiredate=hiredateGO,29,9.3.3 创建/执行带输出参数的存储过程,调用存储过程的T-SQ

17、L语句:DECLARE ecount intEXEC p_getCountEmployees 1993-1-1,ecount OUTPUTSELECT 员工个数为：+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 存储过程的

18、返回值,例7：在northwind数据库创建一个存储过程，返回产品表中的所有产品的库存量。create proc pr_lieras 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参数来

19、返回，可以将上例的存储过程改写为：create proc pr_lier_2fanhuizhi int outputasbegin select fanhuizhi=sum(unitsinstock)from productsend,35,9.3.4 存储过程的返回值,接收output的返回值也必须要用变量，如：declare jieshouzhi int exec pr_lier_2 jieshouzhi output print jieshouzhi注意：1)return返回的是整数，output可以返回任何数据。2)如果让return返回非整数值，在创建存储过程时不会出错，但是运行存储过

20、程时将会出错。,36,存储过程案例,例9：在PUBS数据库中创建一个带参数的存储过程SelectUser，查询出用户表UserMember中是否存在某用户，如果不存在，则返回值为1，否则则查询该用户的密码是否正确，如不正确，则返回值为2，否则返回值为0。,37,存储过程案例,create proc selectuserusername varchar(20),pass varchar(20)asif username not in(select 用户名 from usermember)return(1)else if exists(select*from usermember where 用户

21、名=username and 密码=pass)return(0)else return(2),38,存储过程案例,执行存储过程语句如下:Declare fan intExec fan=selectuser aaa,aaaIf fan=0Print 用户名和密码都正确If fan=1Print 用户名不存在If fan=2Print 用户名存在，密码不正确,39,9.4 事务的概述,1）事务的概念 事务是最小的工作单元。这个工作单元要么成功完成所有操作，要么就是失败，并将所做的一切复原。2）事务特性 原子性（Atomic）：整个数据库事务是不可分割的工作单位 一致性（ConDemoltent）：

22、指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性 独立性（Isolated）：在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。持久性（Durable）：只要事务成功结束，它对数据库所做的更新就必须永久保存下来,40,9.4 事务的概述,3）事务的运行模式 显式事务：显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。开始显式事务，使用BEGIN TRAN。结束显示事务，使用COMMIT TRAN。取消事务，使用ROLL BACK TRAN命令。隐式事务：在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显示完成。

23、,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_variableWITH MARK descriptio

24、n,43,9.4 事务的管理与应用,1、BEGIN TRAN语句参数说明：TRANSACTION可简写为TRANtransaction_name为事务的名称，其命名必须符合标识符规则，也可以省略不写。tran_name_variable指用户定义的含有有效事务名称的变量名称。WITH MARK description用于指定在日志中标记事务,44,9.4 事务的管理与应用,2、COMMIT TRAN语句COMMIT TRAN 语句用于提交事务的操作结果，如果执行事务直到它无误地完成，则可以使用该语句对数据库做永久的改动。其语句格式为：COMMIT TRAN|TRANSACTION transa

25、ction_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语句允许部分地提交一个事务，同时仍能回滚这个事务的其余部分。其语法格式为：

26、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 P

27、ROC pr_auth_userau_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)=nullAS,49,9.4 事务的管理与应用,BEGIN TRANINSERT authorsVALUES(au_id,au_lname,au_fname,phone,address,

28、city,state,zip,contract)IF ERROR0BEGIN ROLLBACK TRANSACTION RETURNEND,50,9.4 事务的管理与应用,INSERT usermember(username,pwd)VALUES(au_fname+au_lname,au_fname+au_lname)IF ERROR0BEGIN ROLLBACK TRANSACTION RETURNEndCOMMIT TRANGO测试语句：Exec pr_auth_user 111-11-1111,zhang,hai,101 110-0001,1,hfdjfdjkfdjkfd,changch

29、un,CA,12345GO,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+errorif(TotalError=0)begin commit tran end else begin rollback tran end GO,