存储过程

概述

简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批处理文件，但是作用不限于批处理。
它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。

分类

分为3类：系统存储过程、用户存储过程、扩展存储过程。

创建存储过程

创建

【例1】创建查看test数据库中fruits表的存储过程

CREATE PROCEDURE auth_proc
AS
SELECT * FROM authors

【例2】创建名称为CountProc的存储过程

CREATE PROCEDURE CountProc
AS
SELECT COUNT(*) AS '总数' FROM authors

调用

EXEC auth_proc;
EXEC CountProc;

带输入参数

【例3】创建存储过程QueryById，根据用户输入参数返回特定的记录

CREATE PROCEDURE QueryById @sID INT
-- CREATE PROCEDURE QueryById2 @sID INT=1（带默认参数） 
AS 
SELECT * FROM authors WHERE auth_id = @sID

EXEC QueryById 1
EXEC QueryById @sID = 1

带输出参数

【例4】定义存储过程QueryById3，根据用户输入id返回

CREATE PROCEDURE QueryById3
@sID INT=1,
@authcount INT OUTPUT
AS
SELECT @authcount=COUNT(auth_id)  FROM authors WHERE auth_id=@sID;

-- 调用
DECLARE @authcount INT
DECLARE @sID INT =1
EXEC QueryById3 @sID, @authcount OUTPUT
SELECT LTRIM(STR(@authcount)) 

管理存储过程

修改存储过程

【例5】修改名称为CountProc的存储过程，将SELECT语句查询的结果按id进行分组

SET ANSI_NULLS ON
GO
SET QUOTED_IDECTIFIER ON
GO
ALTER PROCEDURE CountProc
AS
SELECT auth_id,COUNT(*) AS '总数' FROM authors GROUP BY auth_id

查看存储过程

【例6】使用分别使用OBJECT_DEFINITION、sp_help或者sp_helptext这三个系统存储过程查看CountProc存储过程的定义信息

SELECT OBJECT_DEFINITION( OBJECT_ID(‘CountProc’));
sp_help CountProc
sp_helptext CountProc

重命名存储过程

sp_rename auth_proc,AuthProc

删除存储过程

DROP PROCEDURE AuthProc

问题

1. 如何更改存储过程中的代码？

两种方法：一种是删除并重新创建，另一种是使用ALTER PROCEDURE语句修改。
当删除并重新创建时，原存储过程的所欲关联权限将丢失，而更改存储过程时，只是更改内部定义，并不影响与该过程相关联的存储权限，也不会影响相关存储过程。

2. 存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程，当然可以调用，但是不能用DROP语句删除其他存储过程。