第9章存储过程和事务电子教案

《第9章存储过程和事务电子教案》由会员分享，可在线阅读，更多相关《第9章存储过程和事务电子教案（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 s

3、ql_statement .n ,2）语法注释： procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。 sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。,6,创建存储过程的注意事项,只能在当前数据库中创建存储过程。 数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。 存储过程是数据库对象，其名称必须遵守标识符命名规则。 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。,7,9.2 创建管理简单的存储过程,9.2.1 无参存储过程的创建 9.2.

4、2 无参存储过程的执行 9.2.3 查看存储过程 9.2.4 修改存储过程 9.2.5 编译存储过程 9.2.6 删除存储过程,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

5、.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 修改存储过程,修改

6、存储过程语法格式为： ALTER PROCDURE 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 G

7、O,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_

8、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 | ENCRYPT

9、ION | 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_sear

10、chempl, 查询出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 WI

11、TH 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=hir

12、edate GO,22,9.3.2 创建/执行带输入参数的存储过程,执行带输入参数的存储过程 按位置传递参数值 在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。 通过参数名传递参数值 在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。,23,9.3.2 创建/执行带输入参数的存储过程,在输入参数中使用默认值 在执行存储过程p_getEmployee时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行， 则在创建存

13、储过程时给输入参数指定默认值。,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 A

14、LTER 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数据库中创建一

15、个存储过程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_getCountEmplo

16、yees，用于统计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 存储过程的