第23节 创建和调用存储过程

视图能保存一条查询,但不能直接像函数参数那样传“最低价格”。存储过程可以接收参数,执行查询或多步操作,使用EXEC调用。

1 从熟悉的查询改起

原来写 WHERE Price>=70,现在把70改成参数@minPrice,让调用者决定值。

GO
CREATE PROCEDURE dbo.P_FindBook
    @minPrice decimal(8,2) -- 输入参数,与单价类型一致
AS
BEGIN
    SET NOCOUNT ON; -- 减少“影响n行”消息,不影响SELECT结果
    SELECT BookId,Title,Price
    FROM dbo.Book
    WHERE Price>=@minPrice
    ORDER BY Price,BookId;
END;
GO
 
-- 创建后才调用;用命名参数更容易看懂
EXEC dbo.P_FindBook @minPrice=70; -- 数据库原理79、星空漫游88
EXEC dbo.P_FindBook @minPrice=80; -- 星空漫游88

创建过程不是立即执行过程体。CREATE PROCEDURE应在批首,GO把定义与后续EXEC分开。参数值不要写成 '@minPrice'字符串。

2 修改、查看、删除

GO
-- ALTER写完整的新定义,给参数加默认值0
ALTER PROCEDURE dbo.P_FindBook
    @minPrice decimal(8,2)=0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BookId,Title,Price FROM dbo.Book
    WHERE Price>=@minPrice ORDER BY Price,BookId;
END;
GO
EXEC dbo.P_FindBook; -- 未给参数,就采用默认值0,返回6本
EXEC sp_helptext N'dbo.P_FindBook'; -- 查看定义

删除语法 DROP PROCEDURE dbo.P_FindBook;只删过程定义。后面复习还要调用,先保留。自己的过程不用sp_前缀,避免与系统过程习惯混淆。

3 SELECT、OUTPUT、RETURN分别返回什么

GO
CREATE PROCEDURE dbo.P_CountBooks
    @n int OUTPUT -- 通过参数把图书种数带给调用者
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @n=COUNT(*) FROM dbo.Book;
    RETURN 0; -- 整数状态码,不是输出整张表
END;
GO
 
DECLARE @count int,@status int;
EXEC @status=dbo.P_CountBooks @n=@count OUTPUT;
SELECT @count AS [图书种数],@status AS [状态码]; -- 6、0
GO
DROP PROCEDURE dbo.P_CountBooks; -- 只清理这个输出参数演示过程

SELECT一般返回结果集;OUTPUT带回参数值,声明与调用两处都要写;RETURN返回整数状态。普通过程既能查,也能增删改,不是只能做查询。

本节练习

调用P_FindBook查最低价格60的书;解释默认参数、OUTPUT和RETURN的区别。


上一节:22 事务让多步操作一起成功 | 下一节:24 触发器与自动检查 | 课程目录