11. 存储过程
|
本章主题 l 什么是存储过程 l 创建存储过程 l 细说CREATE PROCEDURE命令 l 嵌套存储过程 l 执行存储过程 l 重命名存储过程 l 修改存储过程 l 删除存储过程 l 重新编译存储过程
必杀技！ 不可不学！学习如何使用SQL来写stored procedure |
11.1. 什么是存储过程
经过前四章的洗礼，相信您对Microsoft SQL Server2000程序设计已有了基本的认识。
第07章 Transact-SQL程序设计
第08章数据的查询、汇总、统计和分析
第09章添加、修改和删除数据记录
第10章视图
本章开始更高级的内容。
存储过程包含一些Transact-SQL语句，并以特定的名称存储在数据库中。存储过程是一种数据库对象。可以在存储过程中声明变量、有条件执行以及其他各项强大的程序设计功能。
l 它能够包含执行各项数据库操作的语句，并且可以调用其他的存储过程。
l 能够接收输入参数并以输出参数的形式将多个数据值返回给调用程序。
具体示例：试着看看，看得懂吗？后面会详介
CREATE PROCEDURE getPerson @mName varchar(10), @mCur_salary money
AS
UPDATE vfpman
SET cur_salary= cur_salary*1.2
WHERE name=@mName AND cur_salary=@mCur_salary
11.1.1. 存储过程的优点
l 允许模块化程序设计
l 更快的执行速度，存储过程被预先编译和优化并存储在数据库中
l 有效降低网络流量
l 较好的安全，如限制某人对某个表操作，却又必须要求他对该表执行特定的操作。
11.2. 创建存储过程
使用企业管理器
CREATE PROCEDURE命令
在查询分析器中执行命令。
11.3. 细说CREATE PROCEDURE命令
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
;number
是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。
VARYING
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
11.3.1. 指定存储过程的名称
l 存储过程名称最长不超过128字符
l 为避免与系统存储过程相混淆，尽量不要以sp_开头
11.3.2. 指定存储过程的语句
CREATE PROCEDURE命令不能够与其他的Transact-SQL命令位于同一个批处理中。
下面的代码会出错：
USE NorthwindSQL --与下面的CREATE PROCEDURE在同一批处理中
CREATE PROCEDURE myproc
AS
SELECT * FROM 飞狐工作室
修改为：
USE NorthwindSQL
GO
CREATE PROCEDURE myproc
AS
SELECT * FROM 飞狐工作室
注解：
l 每一个存储过程最好只负责完成一项工作
l 一个狠好的建议：在创建存储过程前，先在SQL查询分析器中编写并测试要包含在存储过程中的程序代码，等一切无误后，再加入CREATE PROCEDURE命令将它创建成存储过程。
l 存储过程可以访问表、视图，并可以调用其它存储过程。
l 存储过程的最大长度是128MB（其实不太可能达到这个长度的）
11.3.3. 输入参数
将数据值传递给存储过程，该数据值也称为输入参数。
{ @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
l 参数的名称必须以@开头。
l data_type指定输入参数的数据类型。
l default用来指定输入参数的默认值。
l 一个存储过程最多可以拥有2100个参数（包括输入参数和输出参数）。
范例一：利用传入的参数选出选定的姓名的员工资料。
/* 档案名称: Demo111.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DemoProc1' AND type = 'P')
DROP PROCEDURE DemoProc1
GO
CREATE PROCEDURE DemoProc1
@name varchar(10)
AS
SELECT身份证字号,
姓名,
出生日期,
年龄 = DATEDIFF(yy,出生日期,GETDATE())
FROM 飞狐工作室
WHERE 姓名 = @name
GO
执行该存储过程：
EXECUTE DemoProc1 ‘许建仁’ --方法之一
或
EXECUTE DemoProc1 ＠name=‘许建仁’ --方法之二
--参数名一定要与定义时的名称一致
--您更喜欢哪一种方法？
范例二：
/* 档案名称: Demo112.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DemoProc2' AND type = 'P')
DROP PROCEDURE DemoProc2
GO
CREATE PROCEDURE DemoProc2
@BeginningDate datetime = '01/01/1900',
@EndingDate datetime = '12/31/2001'
AS
SELECT a.客户编号,a.公司名称,
a.地址,a.连络人,a.电话,
b.订单号码,b.订单日期,
b.运费,b.收货人,
c.产品编号,c.单价,
c.数量,c.折扣
FROM 客户 a INNER JOIN 订货主档 b
INNER JOIN 订货明细 c
ON b.订单号码 = c.订单号码
ON a.客户编号 = b.客户编号
WHERE
b.订单日期 BETWEEN @BeginningDate AND @EndingDate
GO
执行该存储过程：
EXECUTE DemoProc2 ‘07/01/1996’,’07/31/1996’
或
EXECUTE DemoProc2 @BeginningDate =‘07/01/1996’,
@EndingDate=’07/31/1996’
--参数名一定要与定义时的名称一致
范例三：该范例体现了一种技巧！
/* 档案名称: Demo113.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DemoProc3' AND type = 'P')
DROP PROCEDURE DemoProc3
GO
CREATE PROCEDURE DemoProc3
@id varchar(10) = '[A-M]%',
@name varchar(10) = '许%',
@address varchar(10) = '%台北市%'
AS
SELECT * FROM 飞狐工作室
WHERE
身份证字号 LIKE @id AND
姓名 LIKE @name AND
住址 LIKE @address
GO
执行该存储过程：
EXECUTE DemoProc3 ‘ALM]%’, ’_正_’, ‘%锦州市%’
EXECUTE DemoProc3
11.3.4. 输出参数
范例一：示范将传入参数相乘后将结果返回。
/* 档案名称: Demo114.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DemoProc4' AND type = 'P')
DROP PROCEDURE DemoProc4
GO
CREATE PROCEDURE dbo.DemoProc4
@mult1 int,
@mult2 int,
@result int OUTPUT
AS
SET @result = @mult1 * @mult2
GO
声明与调用时，传入参数名必须一致，而输出参数名可以不同。
执行该存储过程：
/* 档案名称: Demo115.sql */
USE NorthwindSQL
DECLARE @answer int
EXEC DemoProc4 12,33, @answer OUTPUT
SELECT '运算结果是: ',@answer
范例二：计算出指定部门的平均薪资、最大薪资和最低薪资。
/* 档案名称: Demo116.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DemoProc5' AND type = 'P')
DROP PROCEDURE DemoProc5
GO
CREATE PROCEDURE dbo.DemoProc5
@department varchar(10),
@average money OUTPUT,
@maximum money OUTPUT,
@minimum money OUTPUT
AS
SELECT @average = AVG(目前薪资),
@maximum = MAX(目前薪资),
@minimum = MIN(目前薪资)
FROM 飞狐工作室
WHERE 部门 = @department
GO
执行该存储过程：
/* 档案名称: Demo117.sql */
USE NorthwindSQL
DECLARE @department varchar(10)
DECLARE @average decimal(19,4)
DECLARE @maximum decimal(19,4)
DECLARE @minimum decimal(19,4)
SELECT @department = '生产制造部'
EXEC DemoProc5 @department,
@average OUTPUT,
@maximum OUTPUT,
@minimum OUTPUT
SELECT '平均薪资:'+CONVERT(nvarchar, @average),
'最高薪资:'+CONVERT(nvarchar,@maximum),
'最低薪资:'+CONVERT(nvarchar,@minimum)
11.4. 嵌套存储过程
存储过程的嵌套---存储过程中调用其他的存储过程。
嵌套最高可达32层。
被调用的存储过程将可访问调用它的存储过程中所创建的所有对象。
11.5. 执行存储过程
l 可以在存储过程中使用RETURN命令将一个整数返回到调用程序、批处理或存储过程。
l 如果并未在RETURN命令中指定返回值，默认将返回数值0。
范例一：检查用户所指定的姓名是否存在于“飞狐工作室”表，如果存在，则返回1，如果不存在，则返回2。
/* 档案名称: Demo118.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'checkname' AND type = 'P')
DROP PROCEDURE checkname
GO
CREATE PROCEDURE checkname @EmployeeName varchar(10)
AS
IF (SELECT count(*) FROM 飞狐工作室 WHERE 姓名 = @EmployeeName) > 0
RETURN 1
ELSE
RETURN 2
GO
执行该存储过程：
/* 档案名称: Demo119.sql */
DECLARE @RC int
EXEC @RC = NorthwindSQL.dbo.checkname '章立民'
IF @RC = 1
PRINT '存在'
ELSE
PRINT '不存在'
11.5.1. INSERT INTO …EXECUTE
如果存储过程中以SELECT命令返回查询结果，则可以使用INSERT命令将存储过程所返回的查询结果添加到一个表中。
INSERT INTO 表名 EXEC存储过程名
范例一：示范将存储过程的查询结果添加到一个表中。
/* 档案名称: Demo1110.sql */
USE NorthwindSQL
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'SalaryOverview' AND type = 'P')
DROP PROCEDURE SalaryOverview
GO
CREATE PROCEDURE SalaryOverview
AS
SELECT部门,
性别 = CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
婚姻状况 = CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END,
最低薪资 = MIN(目前薪资),
最高薪资 = MAX(目前薪资),
平均薪资 = AVG(目前薪资)
FROM 飞狐工作室
GROUP BY 部门,
CASE 性别
WHEN 0 THEN '女'
WHEN 1 THEN '男'
END,
CASE 婚姻状况
WHEN 0 THEN '未婚'
WHEN 1 THEN '已婚'
END
GO
执行该存储过程：
/* 档案名称: Demo1111.sql */
USE NorthwindSQL
--将预存程序 SalaryOverview 所传回的结果新增至资料表 IncomeOverview
INSERT INTO IncomeOverview EXEC SalaryOverview
SELECT * FROM IncomeOverview
11.6. 重命名存储过程
特别提醒：除非必要，不要随便更改存储过程的名字，原因是这样会造成许多与存储过程依附的对象找不到存储过程而产生错误。
11.6.1. 使用SQL Server企业管理器
11.6.2. 使用系统存储过程sp_rename
EXEC sp_rename ‘GetTopSales’, ‘TopSales’
11.7. 修改存储过程
11.7.1. 使用SQL Server企业管理器
“存储过程属性”对话框，但此时不可以修改CREATE PROCEDURE后的存储过程名称。
11.7.2. 使用查询分析器
11.8. 删除存储过程
11.8.1. 使用SQL Server企业管理器
11.8.2. 使用DROP PROCEDURE命令
DROP PROCEDURE 存储过程名
|
第11章 结束 |
