第9章 存储过程的创建和使用 9.1 存储过程简介 9.2 创建存储过程 9.3 修改存储过程 9.4 删除存储过程 9.5 执行存储过程

利用Transact-SQL编写存储过程； 使用部分常用的系统存储过程和扩展的 存储过程的方法。 能力目标 　　存储过程的创建和维护方法； 　　存储过程的调用参数的特点； 　　执行存储过程的各种方法； 　　利用Transact-SQL编写存储过程； 　　使用部分常用的系统存储过程和扩展的 存储过程的方法。

态度目标 　　团队精神； 　　态度积极； 　　任务按时完成； 　　出勤。

利用Transact-SQL编写存储过程。 难点： 重点难点 　　重点： 　　存储过程的创建和维护方法； 　　存储过程的调用参数的特点； 　　执行存储过程的各种方法； 　　利用Transact-SQL编写存储过程。 　　难点：

9.1 存储过程简介 1. 什么是存储过程（Stored-Procedure） 存储过程就是利用 Transact-SQL语言编写的存 9.1 存储过程简介 　　1. 什么是存储过程（Stored-Procedure） 　　存储过程就是利用 Transact-SQL语言编写的存 储在数据库内的可以执行一些特殊的或常用的数据 处理的程序，它是数据库对象之一，可以通过编译 在数据库后台执行，是数据库端编程的重要技术。

9.1 存储过程简介 2. 存储过程的种类 系统存储过程（System Stored Procedures） 9.1 存储过程简介 　　2. 存储过程的种类 　　系统存储过程（System Stored Procedures） 　　扩展存储过程（Extended Stored Procedures） 　　远程存储过程（Remote Stored Procedures） 　　临时存储过程（Temporary Stored Procedures） 　　用户自定义的存储过程（User-defined Stored Procedures）

9.2 创建存储过程 　　1. 使用“SQL Server Management Studio” 创建存储 过程

9.2 创建存储过程 2. 使用Transact-SQL语句创建存储过程 9.2 创建存储过程 　　2. 使用Transact-SQL语句创建存储过程 CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]     [ { @parameter [ type_schema_name. ] data_type }         [ VARYING ] [ = default ] [ [ OUT [ PUT ]     ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] [ FOR REPLICATION ] AS { <sql_statement> [;][ ...n ] | <method_specifier> } [;]

9.2 创建存储过程 2. 使用Transact-SQL语句创建存储过程 9.2 创建存储过程 　　2. 使用Transact-SQL语句创建存储过程 【案例9.1】创建一个存储过程 emp_sale_info ，传递员工的 编号（emp_id）和销售日期（Order_Date），查询返回该员工 在该日期以来销售产品的数量（Quantity）之和。 CREATE PROCEDURE emp_sale_info @emp_id INT, @orderdate DATETIME, @sumQty NUMERIC(10,2) OUTPUT AS SELECT @sumQty = SUM(Quantity) FROM Sale_detail D, Sales S WHERE emp_id = @emp_id AND Order_Date >= @orderdate AND S.Order_id = D.Order_id

9.3 修改存储过程 　　1. 使用“ SQL Server Management Studio ”SQL编辑 器修改存储过程

9.3 修改存储过程 2. 使用Transact-SQL语句修改存储过程 9.3 修改存储过程 　　2. 使用Transact-SQL语句修改存储过程 ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]     [ { @parameter [ type_schema_name. ] data_type }     [ VARYING ] [ = default ] [ [ OUT [ PUT ]     ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS      { <sql_statement> [ ...n ] | <method_specifier> }

9.3 修改存储过程 2. 使用Transact-SQL语句修改视图 【案例9.2】用ALTER PROCEDURE重新定义存储过程 9.3 修改存储过程 　　2. 使用Transact-SQL语句修改视图 【案例9.2】用ALTER PROCEDURE重新定义存储过程 emp_sales_qty ，传递员工编号和销售日期，输出该日期 前销售的产品数量平均值。 ALTER PROCEDURE emp_sales_qty @empno INT, @orderdate DATETIME, @result NUMERIC(10,2) OUTPUT AS SELECT @result = AVG(Quantity) FROM Sales S, Sale_detail D WHERE S.Order_id = D.Order_id AND emp_id = @empno AND Order_Date<= @orderdate

9.4 删除存储过程 　　1. 用“SQL Server Management Studio” 管理工具 删除存储过程

9.4 删除存储过程 2. 使用Transact-SQL语句删除存储过程 【案例9.3】 9.4 删除存储过程 　　2. 使用Transact-SQL语句删除存储过程 【案例9.3】 　　删除emp_sales_qty存储过程（在当前数据库 eTradeInfo内）。 DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ] DROP PROCEDURE title_price

9.5 执行存储过程 使用Transact-SQL语句调用存储过程 [ { EXEC | EXECUTE } ] { 9.5 执行存储过程 　　使用Transact-SQL语句调用存储过程 [ { EXEC | EXECUTE } ]     {       [ @return_status = ]       { module_name [ ;number ] | @module_name_var }         [ [ @parameter = ] { value                            | @variable [ OUTPUT ]                            | [ DEFAULT ]                            }        ]       [ ,...n ]       [ WITH RECOMPILE ]     }[;]

9.5 执行存储过程 【案例9.4】执行存储过程emp_sale_info。 【案例9.5】执行存储过程cust_order_info。 9.5 执行存储过程 【案例9.4】执行存储过程emp_sale_info。 DECLARE @sumQty NUMERIC(10,2) EXECUTE emp_sale_info 12, '2006-01-01', @sumQty OUTPUT 【案例9.5】执行存储过程cust_order_info。 DECLARE @orderdate DATETIME EXECUTE cust_order_info '张%', @orderdate 【案例9.6】在远程服务器SQL SERVER1上执行 checkcontract存储过程，在@retstat中保存返回状态， 说明运行成功或失败。 DECLARE @retstat int EXECUTE @retstat=SQLSERVER1.eTradeInfo.dbo. Checkcontract '409-56-4008'

使用案例数据库eTradeInfo，在“SQL Server Management Studio”管理窗口中，完成以下题目存储 案例分析 　　使用案例数据库eTradeInfo，在“SQL Server Management Studio”管理窗口中，完成以下题目存储 过程创建和Transact-SQL代码调试。 　　1. 编写一个存储过程，输入某雇员编号（emp_id）、商 品订购单编号（ Order_ID ）和订购日期（Order_Date），输 出该订单的客户信息。 　　2. 编写存储过程，输入仓库编号，输出其库存商品数量 之和以及商品种类之和，并写出调用过程。 　　3. 编写存储过程，输入某个部门名称、日期，输出该部 门人数及在该日期以来签订的合同数量。 　　4. 编写存储过程，输入某类商品编号，输出其目前库存 数量、已销售数量，并写出调用过程。

1. 用“SQL Server Management Studio”和T-SQL 语句创建存储过程，并在“SQL编辑器”窗口中调试； 案例分析 要求： 　　1. 用“SQL Server Management Studio”和T-SQL 语句创建存储过程，并在“SQL编辑器”窗口中调试； 　　2. 调用存储过程的各种方法； 　　3. 熟练掌握在“SQL编辑器”窗口中调试存储过 程技术。 问题： 　　1. 用户可以创建或修改存储过程吗？ 　　2. 用户存储过程可以不带参数执行吗？ 讨论： 　　使用存储过程的主要优点。结合前台开发工具 嵌入SQL命令的形式，比较使用存储过程的意义。

存储过程分系统存储过程、扩展存储过程和用 户自定义的存储过程。 系统存储过程是SQL Server 2005自身配备的具 本章小结 　　存储过程分系统存储过程、扩展存储过程和用 户自定义的存储过程。 　　系统存储过程是SQL Server 2005自身配备的具 有特殊功能和用途的存储过程，SQL Server 2005也 配备了部分扩展存储过程。 　　用户自定义的存储过程是用户使用Transact- SQL编写的用户常用的一些程序，这些程序代码存 储在SQL Server数据库中，并可以通过编译执行。 　　调用存储过程类似调用系统函数，可以输入参 数，也可以通过参数输出执行的结果，大大提高了 代码的重用性、安全性和执行速度。