数据库基础--存储过程

为什么使用“存储过程”?
在创建 SQL Server 数据库应用程序时,Transact-SQL 语言是应用程序和 SQL Server 数据库之间的主要编程接口。可用如下两种方法存储和执行 Transact-SQL 语句:
(1) 将 Transact-SQL 程序保存在本地,创建向 SQL Server 发送命令并处理结果的应用程序。
(2) 可以将 Transact-SQL 程序保存在 SQL Server 中,即存储过程,在本地创建执行存储过程及处理结果的应用程序。
  任何一组 Transact-SQL 语句构成的代码块,都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理,并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作,减少了网络上的传输量,同时也提高了客户端的工作效率(因为这些操作都是在服务器端完成的)。存储过程是集中存储在 SQL Server 中的 T-SQL 语句的预编译集合,用以实现某种任务(如查询或更新)。这些语句在一个名称下存储并作为一个单元进行处理。

使用存储过程的优势
1.可以减少客户端代码的重复。
2.允许更快地执行。
3.减少网络流量。
4.可以简化数据库管理。
5.可作为安全机制使用。

--创建存储过程“增加成绩”,将表“学生”中所有学生的入学成绩增加%。
USE 学生管理
GO
CREATE PROCEDURE 增加成绩
AS
UPDATE 学生SET 入学成绩= 入学成绩*1.1
GO
--在创建一个存储过程时,如果已经存在同名的存储过程,
--则不允许创建新的存储过程。可以将以上代码改写为:
USE 学生管理
--如果存在名称为“增加成绩”的存储过程,则将其删除
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = '增加成绩' AND type = 'P')
  DROP PROCEDURE 增加成绩
GO
CREATE PROCEDURE 增加成绩
AS
UPDATE 学生SET 入学成绩= 入学成绩*1.1
GO
--执行存储过程“增加成绩”,并查看执行结果。
USE 学生管理
GO
EXEC 增加成绩
SELECT * FROM 学生
GO
--创建存储过程add_proc,用于计算两个参数之和并将结果输出。
CREATE PROCEDURE add_proc
@num1 INT = 0,
@num2 INT = 0
AS
DECLARE @num3 INT
SET @num3 = @num1 + @num2
PRINT @num3
--执行存储过程add_proc
--不带参数执行:
EXEC add_proc
--带参数执行:
EXEC add_proc 13, 25
--创建存储过程add_proc1,用于计算两个参数之和,
--并使用输出参数返回结果。
CREATE PROCEDURE add_proc1
@num1 INT = 0,
@num2 INT = 0,
@num3 INT OUTPUT
AS
--@num3为输出参数
SET @num3 = @num1 + @num2
--执行存储过程add_proc1
DECLARE @num AS INT
EXEC add_proc1 12, 23, @num OUTPUT
PRINT @num
--创建存储过程AvgScore,用于根据给定的院系和班级名称计算平均成绩,
--并使用输出参数返回结果。
CREATE PROCEDURE AvgScore
@org varchar(100),--院系名称,输入参数
@class varchar(50),--班级名称,输入参数
@score float OUTPUT -- 成绩,输出参数
AS
DECLARE @orgid int
SET @orgid = 0
-- 根据参数中指定的院系名称org, 获取院系编号
SELECT @orgid = 记录编号
  FROM 院系WHERE 院系名称=@org
IF @orgid = 0
  BEGIN
    SET @score = 0
    PRINT '指定的院系记录不存在'
  END
ELSE
  BEGIN
    SELECT @score = AVG(入学成绩) FROM 学生
      WHERE 所属院系=@orgid AND 班级=@class
      GROUP BY 所属院系, 班级
  END
--执行
DECLARE @score float
EXEC AvgScore '软件系', '二班', @score OUTPUT
PRINT @score
运行结果为:
  584
--创建存储过程AvgScore1,根据给定的院系和班级名称计算平均成绩,
--并将结果使用输出参数返回。如果指定的院系存在,则返回,否则返回。
CREATE PROCEDURE AvgScore1
@org varchar(100),
@class varchar(50),
@score float OUTPUT
AS
DECLARE @orgid int
SET @orgid = 0
-- 根据参数中指定的院系名称org, 获取院系编号
SELECT @orgid = 记录编号
  FROM 院系 WHERE 院系名称=@org
IF @orgid = 0
  RETURN 0
ELSE
  BEGIN
    SELECT @score = AVG(入学成绩) FROM 学生
      WHERE 所属院系=@orgid AND 班级=@class
      GROUP BY 所属院系, 班级
    RETURN 1
  END
GO
--执行
DECLARE @score float
DECLARE @result int
EXEC @result = AvgScore1 '软件系', '二班', @score OUTPUT
-- 检查返回值
IF @result = 1
  PRINT @score
ELSE
  PRINT '没有对应的记录'

总结:从存储过程中返回数据的三种方式
1.OUTPUT 输出参数
2.RETURN 返回状态值,始终是整数
3.SELECT 语句的结果集
  ExecuteReader:返回一个表格
  ExecuteScalar:返回一个标量值
  ExecuteNonQuery:不返回任何结果

使用ALTER PROCEDURE语句修改存储过程
使用 sp_rename 重命名存储过程
--使用ALTER PROCEDURE语句修改存储过程“增加成绩”,对其进行加密处理。
USE 学生管理
GO
ALTER PROCEDURE 增加成绩
WITH ENCRYPTION
AS
UPDATE 学生 SET 入学成绩= 入学成绩* 1.1
--将存储过程add_proc重命名为add_proc2
EXEC sp_rename 'add_proc', 'add_proc2'

--删除存储过程add_proc
DROP PROCEDURE add_proc