创建存储过程:

创建不带参数的存储过程

语法：

CREATE PROC[EDURE] 存储过程名

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<=100

goto label_1

select @count,@sum

exec pr_sum

创建存储过程的注意事项：

① 每个存储过程应该完成一项单独的工作。

② 为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数

WITH ENCRYPTION。

③ 一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。

创建带参数的存储过程

回顾我们刚才创建的求三角形面积的存储过程proc_area:

执行该存储过程：exec proc_area

可以看出，存储过程proc_area的功能就是计算边长为2、4、5的三角形的面积。由于用户无法参与过程内运行的语句，该存储过程只能执行固定的操作，其实用性大大降低了。带输入参数的存储过程允许用户通过输入参数，参与到过程中执行的命令中，我们可以把语句运行的关键数值设置成输入参数，由用户在调用存储过程的时候定义该值，从而达到控制存储过程功能的目的。

语法：

createproc[edure] 存储过程名

@parameter_namedataype[=default]

[,……]

as

DEMO1：建立一个存储过程，选择某一个指定学生的学生姓名、所学课程及任课教师姓名。

CREATEPROCEDURE proc_StuCouTea_name@studentname varchar(8)

AS

SELECTstudent_name,

course_name,

teacher_name

FROMstudent a,

student_courseb,

coursec,

teacher_course_classd,

teachere

WHEREa.student_id=b.student_id

ANDb.course_id=c.course_id

ANDc.course_id=d.course_id

ANDd.teacher_id=e.teacher_id

ANDstudent_name=@studentname

用户可以运行下面的语句来执行它，以查询学生张虹所学的课程及这些课程的任课教师姓名：

EXECproc_StuCouTea_name '张虹'

这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。如将上例中输入参数的定义：

@studentnamevarchar(8)

替换为：

@studentnamevarchar(8)='%'

重新创建存储过程spStuCouTea_withParam，如果执行该存储过程时不提供任何参数，则执行返回的结果集将是空集，而不会产生错误。

DEMO2：修改刚才的计算三角形面积的存储过程，要求由用户输入三个边的边长，计算三角形面积。

createprocedure proc_area

@afloat,@b float,@c float

as

declare@p float,@s float

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'输入三边不能构成一个三角形!'

执行：

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[EDURE] 存储过程名

@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... [WITH RECOMPILE]

SQLServer对这个存储过程不重用查询计划，在每次执行时都被重新编译和优化,并创建新的查询计划。

DEMO：为JWGL数据库创建一个带重编译选项的存储过程，用于查询某学生的成绩信息。

CREATEPROC spStudentCourse@studentid char(8)

WITHRECOMPILE

AS

SELECT*

FROMstudent_course

wherestudent_id = @studentid

② 执行存储过程时重编译。

在EXECUTE语句中使用WITH RECOMPILE选项，让SQL Server在执行一个存储过程时，重新编译该存储过程。其语法如下：

EXECUTEprocedure_name [parameter] [WITH RECOMPILE]

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）。无默认值。