41. SQL -- 存储过程（2）
创建存储过程:创建不带参数的存储过程
语法：
CREATE PROC 存储过程名
AS
SQL
和C语言的函数一样，参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
创建步骤：
① 编写SQL语句。
② 测试SQL语句是否正确，并能实现功能要求。
③ 若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。
④ 执行该存储过程，验证其正确性。
DEMO1：创建一存储过程，要求该存储过程返回学生姓名、所学课程名称和任课教师。
CREATE PROCEDURE proc_StuCouTea_name
AS
SELECT student_name,course_name,teacher_name
FROM student a,student_courseb,course c,teacher_course_classd,teacher e
WHERE a.student_id=b.student_id
AND b.course_id=c.course_id
AND c.course_id=d.course_id
AND d.teacher_id=e.teacher_id
--执行以上脚本，便可创建存储过程proc_StuCouTea_name。如要执行该存储过程，可在查询分析器中
执行如下语句：
EXEC proc_StuCouTea_name
DEMO2：创建存储过程proc_area，根据三角形三边，计算三角形面积
create procedure proc_area
as
declare @a float,@b float,@c float
declare @p float,@s float
select @a=2,@b=4,@c=5
print '输入的三边为：'+convert(varchar(5),@a)+'、'+convert(varchar(5),@b)+'、
'+convert(varchar(5),@c)
if @a+@b>@c and @a+@c>@b and @b+@c>@a
begin
set @p=(@a+@b+@c)/2
set @s=sqrt(@p*(@p-@a)*(@p-@b)*(@p-@c))
print '该三角形的面积为：'+convert(varchar(8),@s)
end
else
print '输入三边不能构成一个三角形！'
exec proc_area
返回：
输入的三边为：2、4、5
该三角形的面积为：.79967
DEMO3：创建存储过程，计算从1到100相加的总和
create proc pr_sum
with encryption
as
declare @sum int, @count int
select @sum=0, @count=1
label_1:
select @sum=@sum+@count
select @count=@count+1
if @count@c and @a+@c>@b and @b+@c>@a
begin
set @p=(@a+@b+@c)/2
set @s=sqrt(@p*(@p-@a)*(@p-@b)*(@p-@c))
print'该三角形的面积为：'+convert(varchar(8),@s)
end
else
print'输入三边不能构成一个三角形!'
执行：
execproc_area 3,3,4
返回：
输入的三边为：3、3、4
该三角形的面积为：.47214
DEMO3：为JWGL数据库建立一个存储过程，通过执行存储过程将学生信息添加到student表。
CREATEPROCEDURE proc_AddStudent
@idchar(8)= NULL,
@namenvarchar(8)= NULL,
@sexchar(2)= NULL,
@birthdaysmalldatetime= NULL,
@classchar(6)= NULL,
@InDatesmalldatetime= NULL,
@homenvarchar(40)= NULL
AS
IF @id IS NULL
OR@name IS NULL
OR@sex IS NULL
OR@birthday IS NULL
OR@class IS NULL
OR@InDate is NULL
BEGIN
PRINT'请重新输入该学生信息！'
PRINT'你必须提供学生的学号、姓名、性别、出生日期、班级号及入学日期。'
PRINT'（家庭地址可以为空）'
RETURN
END
INSERTstudent
(student_id,
student_name,
sex,
birth,
class_id,
entrance_date,
home_addr)
VALUES
(@id,
@name,
@Sex,
@birthday,
@class,
@InDate,
@home)
PRINT'学生'+@name+'的信息成功添加到表student中。'
创建带输出参数存储过程
语法：
CREATEPROC 存储过程名
@parameter_namedataype[=default] OUTPUT
[,……]
AS
其中：
OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时SQL调用脚本必须使用OUTPUT关键字。
DEMO1：创建一个实现加法计算并将运算结果作为输出参数的存储过程
CREATEPROCEDURE proc_Add
@Value1INT,
@Value2INT,
@ResultValueINT OUTPUT
AS
SELECT@ResultValue = @Value1 + @Value2
GO
若在存储过程的定义中省略OUTPUT，调用时会出错。实际上，每个存储过程的执行，都将自动返回一个返回状态（可以通过@return_status获得），用于告诉调用程序―执行该存储过程的状况‖。调用程序可根据返回状态作相应的处理。一般而言，系统使用0表示该存储过程
执行成功。用户也可以在存储过程中使用RETURN来返回指定的值
DEMO2：创建存储过程proc_area_circle，输入圆的半径，计算圆面积，并通过该存储过程，计算圆柱的体积。
createproc proc_area_circle
@rfloat=0,
@sfloat output
as
set @s=pi()*@r*@r
go
执行该存储过程：
declare@r float
declare@s float
set @r = 10
execproc_area_circle@r,@s output
printconvert(varchar(20),@s)
返回结果：
314.159
存储过程重编译
在某些情况下，可能需要改变数据库的逻辑结构（如：为表新增列），或者为表新增索引。为了使该存储过程能够根据数据库的改变重新优化，或从新的索引中受益，这就要求SQLServer在执行存储过程时对它重新编译，因为除非重新启动SQL Server，否则，存储过程访问数据表的原始查询不会自动优化。以下是重新编译存储过程的三种方法：
① 在创建存储过程时，使用CREATE PROCEDURE中的RECOMPILE重编译选项。
具体语法如下：
CREATEPROCEDURE...
SQLServer对这个存储过程不重用查询计划，在每次执行时都被重新编译和优化,并创建新的查询计划。
DEMO：为JWGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。
CREATEPROC spStudentCourse@studentid char(8)
WITHRECOMPILE
AS
SELECT*
FROMstudent_course
wherestudent_id = @studentid
② 执行存储过程时重编译。
在EXECUTE语句中使用WITH RECOMPILE选项，让SQL Server在执行一个存储过程时，重新编译该存储过程。其语法如下：
EXECUTEprocedure_name
DEMO：重新编译选项，执行存储过程spAdd。
--创建存储过程：
CREATEPROCEDURE proc_Add
@Value1INT,
@Value2INT,
@ResultValueINT OUTPUT
AS
SELECT@ResultValue = @Value1 + @Value2
GO
--调用存储过程时，使用recompile参数重编译该存储过程：
DECLARE@ResultValue INT
EXECproc_add 4,9, @ResultValue OUTPUT
WITHRECOMPILE
PRINTCONVERT(CHAR(5), @ResultValue)
③ 使用sp_recompile系统存储过程，指定表的存储过程进行重编译。
语法如下：
sp_recompiletable_name
例如：EXEC sp_recompile student 将强制student表的所有存储过程和触发器在下一次运行时被重新编译。
自动执行存储过程
SQLServer 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建，并在sysadmin固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数，也不能从过程中返回任何结果集。
使用系统存储过程sp_procoption可以将现有存储过程设置为自动执行过程或停止自动执行，也可以查看SQL Server启动时执行的所有过程的列表。
语法：
sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ]
'option', [ @OptionValue = ] 'value'
其中：
● [@ProcName =] 'procedure' ：要为其设置或查看选项的过程名。无默认值。
● [@OptionName =] 'option'：要设置的选项的名称。option的唯一值是startup，
该值设置存储过程的自动执行状态。
● [ @OptionValue = ] 'value'：表示选项是设置为开（true或on）还是关（false或off）。无默认值。
页:
[1]