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

1 第9章存储过程和事务 9 1存储过程概述9 2创建管理简单存储过程9 3创建管理带参的存储过程9 4事务 2 9 1存储过程概述 1 存储过程概念存储过程是为了实现某个特定任务 由一组预先编译好的SQL语句组成 将其放在服务器上 由用户通过指定存储过程的名字来执行的一种数据库对象 2 存储过程类型系统存储过程 以SP 为前缀 是由SQLServer2005自己创建 管理和使用的一种特殊的存储过程 不能对其进行修改或删除 如sp helpdb sp renamedb等扩展存储过程 以XP 为前缀 SQLSERVER的实例可以动态加载和运行的DLL 直接在实例地址空间中运行 可以使用SQLSERVER扩展存储过程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 语法格式如下 CREATEPROC EDURE procedure nameASsql statement n 2 语法注释 procedure name 新建存储过程的名称 其名称必须符合标识符命名规则 且对于数据库及其所有者必须唯一 sql statement 指存储过程中的任意数目和类型的Transact SQL语句 5 9 2 1无参存储过程的创建 例1 在PUBS数据库中 创建一个名称为pr searchorddate的存储过程 该存储过程将查询出sales表中订购日期ord date在1994年以后的记录信息 代码如下 CREATEPROCpr searchorddateASSELECT FROMsalesWHEREord date 1994 1 1 GO 6 创建存储过程的注意事项 只能在当前数据库中创建存储过程 数据库的所有者可以创建存储过程 也可以授权其他用户创建存储过程 存储过程是数据库对象 其名称必须遵守标识符命名规则 不能将CREATEPROCEDURE语句与其它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 procedure name对上例的存储过程pr searchorddate的执行语句如下 EXECUTEpr 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 使用系统存储过程查看存储过程信息在SQLServer中 可以使用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修改存储过程 修改存储过程语法格式为 ALTERPROC DURE procedure name parameterdata type default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION ASSql statement 12 9 2 4修改存储过程 例14 修改存储过程pr searchorddate 查询出sales表中订购日期在93年以后的订单 其程序清单如下 AlterPROCpr searchorddateASSELECT FROMsalesWHEREord date 1993 1 1 GO 13 9 2 5编译存储过程 在我们使用了一次存储过程后 可能会因为某些原因 必须向表中新增加数据列或者为表新添加索引 从而改变了数据库的逻辑结构 这时 需要对存储过程进行重新编译 SQLServer提供三种重新编译存储过程的方法 1 在建立存储过程时设定重新编译语法格式 CREATEPROCEDUREprocedure nameWITHRECOMPILEASsql statement2 在执行存储过程时设定重编译语法格式 EXECUTEprocedure nameWITHRECOMPILE3 通过使用系统存储过程设定重编译语法格式为 EXECsp recompileOBJECT 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 语法格式如下 CREATEPROC DURE procedure name parameterdata type default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION ASSql statement 17 9 3 1创建带参存储过程的语法 2 语法注释 parameter 存储过程中的输入和输出参数 data type 参数的数据类型 OUTPUT 表明参数是返回参数 该选项的值可以返回给EXEC UTE 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字段为某个州且姓中包含某字符串的所有的员工信息 CREATEPROCpr searchempl statechar 2 strvarchar 40 AsSelect FromauthorsWherestate stateandau lnamelike str 20 9 3 2创建 执行带输入参数的存储过程 语法格式如下 EXEC UTE return status procedure name parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 对上例的存储过程pr searchempl的执行语句如下 EXECUTEpr searchempl CA hi 21 9 3 2创建 执行带输入参数的存储过程 例3 查询出northwind数据库中城市值为某某值并且雇佣时间在某某日期之后的所有员工的基本信息 实现的T SQL语句 USEnorthwindGOCREATEPROCp getEmployee citynvarchar 15 hiredatedatetimeASSELECT FROMemployeesWHEREcity cityANDhiredate hiredateGO 22 9 3 2创建 执行带输入参数的存储过程 执行带输入参数的存储过程按位置传递参数值在执行存储过程的语句中 直接给出参数的值 当有多个参数时 给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致 即参数传递的顺序就是参数定义的顺序 通过参数名传递参数值在执行存储过程的语句中 使用 参数名 参数值 的形式给出参数值 其优点是参数可以以任意顺序给出 23 9 3 2创建 执行带输入参数的存储过程 在输入参数中使用默认值在执行存储过程p getEmployee时 如果没有指定参数 则系统运行就会出错 此时如果希望在执行时不给出参数也能正确运行 则在创建存储过程时给输入参数指定默认值 24 9 3 2创建 执行带输入参数的存储过程 按位置传递参数值EXECp getEmployee london 1994 1 1 通过参数名传递参数值EXECp getEmployee city london hiredate 1994 1 1 或EXECp getEmployee hiredate 1994 1 1 city london 25 9 3 2创建 执行带输入参数的存储过程 例4 对存储过程p getEmployee进行修改 指定城市默认值为 london 指定雇佣日期为1990年1月1日 USEnorthwindGOALTERPROCp getEmployee citynvarchar 15 london hiredatedatetime 1990 1 1 ASSELECT FROMemployeesWHEREcity cityANDhiredate hiredateGOEXECp 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字段为某编号的书籍总价格 CREATEPROCpr titleprice pub idchar 4 spricemoneyoutputAsSelect sprice sum price FromtitlesWherepub id pub id存储过程的执行 declare ssmoneyexecpr titleprice 0877 ssoutputselect ssas总价格 28 9 3 3创建 执行带输出参数的存储过程 例6 创建一个存储过程p getCountEmployees 用于统计nothwind数据库员工表中雇佣日期在某某时间之后的员工的个数 实现的T SQL语句 USEnorthwindGOCREATEPROCp getCountEmployees hiredatedatetime 1990 1 1 countintOUTPUTASSELECT count count FROMemployeesWHEREhiredate hiredateGO 29 9 3 3创建 执行带输出参数的存储过程 调用存储过程的T SQL语句 DECLARE ecountintEXECp getCountEmployees 1993 1 1 ecountOUTPUTSELECT 员工个数为 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数据库创建一个存储过程 返回产品表中的所有产品的库存量 createprocpr lierasbegindeclare fanhuizhiintselect fanhuizhi sum unitsinstock fromproductsreturn fanhuizhiend 33 9 3 4存储过程的返回值 接受这个返回值必须要用变量来接收declare jieshouzhiintexec jieshouzhi pr lierprint jieshouzhi 34 9 3 4存储过程的返回值 上例中返回的值也可以用output参数来返回 可以将上例的存储过程改写为 createprocpr lier 2 fanhuizhiintoutputasbeginselect fanhuizhi sum unitsinstock fromproductsend 35 9 3 4存储过程的返回值 接收output的返回值也必须要用变量 如 declare jieshouzhiintexecpr lier 2 jieshouzhioutputprint jieshouzhi注意 1 return返回的是整数 output可以返回任何数据 2 如果让return返回非整数值 在创建存储过程时不会出错 但是运行存储过程时将会出错 36 存储过程案例 例9 在PUBS数据库中创建一个带参数的存储过程SelectUser 查询出用户表UserMember中是否存在某用户 如果不存在 则返回值为1 否则则查询该用户的密码是否正确 如不正确 则返回值为2 否则返回值为0 37 存储过程案例 createprocselectuser usernamevarchar 20 passvarchar 20 asif usernamenotin select用户名fromusermember return 1 elseifexists select fromusermemberwhere用户名 usernameand密码 pass return 0 elsereturn 2 38 存储过程案例 执行存储过程语句如下 Declare fanintExec fan selectuser aaa aaa If fan 0Print 用户名和密码都正确 If fan 1Print 用户名不存在 If fan 2Print 用户名存在 密码不正确 39 9 4事务的概述 1 事务的概念事务是最小的工作单元 这个工作单元要么成功完成所有操作 要么就是失败 并将所做的一切复原 2 事务特性原子性 Atomic 整个数据库事务是不可分割的工作单位一致性 ConDemoltent 指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性独立性 Isolated 在并发环境中 当不同的事务同时操纵相同的数据时 每个事务都有各自的完整数据空间 持久性 Durable 只要事务成功结束 它对数据库所做的更新就必须永久保存下来 40 9 4事务的概述 3 事务的运行模式显式事务 显示事务是手工配置的事务 用保留字标识显式事务的开始和结束 开始显式事务 使用BEGINTRAN 结束显示事务 使用COMMITTRAN 取消事务 使用ROLLBACKTRAN命令 隐式事务 在前一个事务完成时新事务隐式启动 但每个事务仍以COMMIT或ROLLBACK语句显示完成 41 9 4事务的概述 自动提交事务每条单独的SQL语句都是一个事务 这是SQL默认的事务管理模式 每个T SQL语句完成时 都被 成功 提交或 失败 回滚 42 9 4事务的管理与应用 1 BEGINTRAN语句使用BEGINTRAN语句主要是显示地命令SQLSERVER开始一个新事务 如果遇上错误 在BEGINTRAN之后的所有数据改动都能进行回滚 以将数据返回到已知的一致状态 该语句主要用于显示事务中 其语法结构如下 BEGIN TRAN TRANSACTION transaction name tran name variable WITHMARK description 43 9 4事务的管理与应用 1 BEGINTRAN语句参数说明 TRANSACTION可简写为TRANtransaction name为事务的名称 其命名必须符合标识符规则 也可以省略不写 tran name variable指用户定义的含有有效事务名称的变量名称 WITHMARK description 用于指定在日志中标记事务 44 9 4事务的管理与应用 2 COMMITTRAN语句COMMITTRAN语句用于提交事务的操作结果 如果执行事务直到它无误地完成 则可以使用该语句对数据库做永久的改动 其语句格式为 COMMIT TRAN TRANSACTION transaction name tran name variable 45 9 4事务的管理与应用 3 ROLLBACKTRAN语句ROLLBACKTRAN语句用于当事务中的T SQL语句发生错误时进行回滚操作 从而恢复数据库至事务开始之前的状态 其语法格式为 ROLLBACK TRAN TRANSACTION transaction name tran name variable savepoint name savepoint variable 46 9 4事务的管理与应用 4 SAVETRAN语句SAVETRAN语句允许部分地提交一个事务 同时仍能回滚这个事务的其余部分 其语法格式为 SAVE TRAN TRANSACTION savepoint name savepoint variable 47 9 4事务的管理与应用 例1 请用事务在PUBS数据库中创建一个存储过程pr auth user 当向authors表中插入一个作者信息 同时将该作者的姓名插入到用户表中的username列 pwd列的初始值为用户名 实现的T SQL语句 CREATETABLEusremember usernamevarchar 80 Pwdvarchar 50 Emailvarchar 50 Phonechar 12 GO 48 9 4事务的管理与应用 CREATEPROCpr auth user au idvarchar 11 au lnamevarchar 40 au fnamevarchar 40 phonechar 12 contractbit 一般将具有默认值的参数放在后边定义 便于调用时省略 addressvarchar 50 null cityvarchar 20 null statechar 2 null zipchar 5 nullAS 49 9 4事务的管理与应用 BEGINTRA