为什么使用“存储过程”?在创建 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 学生管理GOCREATE PROCEDURE 增加成绩ASUPDATE 学生SET 入学成绩= 入学成绩*1.1GO--在创建一个存储过程时,如果已经存在同名的存储过程,--则不允许创建新的存储过程。可以将以上代码改写为:USE 学生管理--如果存在名称为“增加成绩”的存储过程,则将其删除IF EXISTS (SELECT name FROM sysobjects WHERE name = '增加成绩' AND type = 'P') DROP PROCEDURE 增加成绩GOCREATE PROCEDURE 增加成绩ASUPDATE 学生SET 入学成绩= 入学成绩*1.1GO--执行存储过程“增加成绩”,并查看执行结果。USE 学生管理GOEXEC 增加成绩SELECT * FROM 学生GO--创建存储过程add_proc,用于计算两个参数之和并将结果输出。CREATE PROCEDURE add_proc@num1 INT = 0,@num2 INT = 0ASDECLARE @num3 INTSET @num3 = @num1 + @num2PRINT @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 OUTPUTAS--@num3为输出参数SET @num3 = @num1 + @num2--执行存储过程add_proc1DECLARE @num AS INTEXEC add_proc1 12, 23, @num OUTPUTPRINT @num--创建存储过程AvgScore,用于根据给定的院系和班级名称计算平均成绩,--并使用输出参数返回结果。CREATE PROCEDURE AvgScore@org varchar(100),--院系名称,输入参数@class varchar(50),--班级名称,输入参数@score float OUTPUT -- 成绩,输出参数ASDECLARE @orgid intSET @orgid = 0-- 根据参数中指定的院系名称org, 获取院系编号SELECT @orgid = 记录编号 FROM 院系WHERE 院系名称=@orgIF @orgid = 0 BEGIN SET @score = 0 PRINT '指定的院系记录不存在' ENDELSE BEGIN SELECT @score = AVG(入学成绩) FROM 学生 WHERE 所属院系=@orgid AND 班级=@class GROUP BY 所属院系, 班级 END--执行DECLARE @score floatEXEC AvgScore '软件系', '二班', @score OUTPUTPRINT @score运行结果为: 584--创建存储过程AvgScore1,根据给定的院系和班级名称计算平均成绩,--并将结果使用输出参数返回。如果指定的院系存在,则返回,否则返回。CREATE PROCEDURE AvgScore1@org varchar(100),@class varchar(50),@score float OUTPUTASDECLARE @orgid intSET @orgid = 0-- 根据参数中指定的院系名称org, 获取院系编号SELECT @orgid = 记录编号 FROM 院系 WHERE 院系名称=@orgIF @orgid = 0 RETURN 0ELSE BEGIN SELECT @score = AVG(入学成绩) FROM 学生 WHERE 所属院系=@orgid AND 班级=@class GROUP BY 所属院系, 班级 RETURN 1 ENDGO--执行DECLARE @score floatDECLARE @result intEXEC @result = AvgScore1 '软件系', '二班', @score OUTPUT-- 检查返回值IF @result = 1 PRINT @scoreELSE PRINT '没有对应的记录'总结:从存储过程中返回数据的三种方式1.OUTPUT 输出参数2.RETURN 返回状态值,始终是整数3.SELECT 语句的结果集 ExecuteReader:返回一个表格 ExecuteScalar:返回一个标量值 ExecuteNonQuery:不返回任何结果使用ALTER PROCEDURE语句修改存储过程使用 sp_rename 重命名存储过程--使用ALTER PROCEDURE语句修改存储过程“增加成绩”,对其进行加密处理。USE 学生管理GOALTER PROCEDURE 增加成绩WITH ENCRYPTIONASUPDATE 学生 SET 入学成绩= 入学成绩* 1.1--将存储过程add_proc重命名为add_proc2EXEC sp_rename 'add_proc', 'add_proc2'--删除存储过程add_procDROP PROCEDURE add_proc
