《SQLServer数据库应用与维护》-第17讲 存储过程2

上讲内容回顾回顾7.1存储过程7.1.1存储过程的定义7.1.2存储过程的优点7.1.3存储过程的分类7.1.4常用的系统存储过程7.1.5创建与调用存储过程7.1.6管理存储过程上讲内容提问2、使用存储过程有什么好处？3、存储过程从创建者不同可以分为哪两类？4、常见的系统存储过程有，通常以什么开头？5、用户自定义存储过程语句是，如何调用？1、存储过程是什么？回顾6、用户自定义存储过程有哪几类，我们上次课讲了？上讲内容总结回顾存储过程（StoredProcedure）：是在大型数据库系统中，一组为了完成特定功能的SQL语句集。1、创建存储过程

CreateProc[edure]<存储过程名>[<@参数名1>…]

AS<表达式>2、执行存储过程

Exec[ute]<存储过程名>[<@参数名1>…]预编译指定名称、参数可选创建存储过程执行存储过程完成任务

第七章存储过程与触发器第七章

存储过程与触发器目的与要求：掌握根据需要创建、修改、删除存储过程和触发器。能够在实际应用开发时能够灵活运用存储过程与触发器，以提高开发效率。重点与难点：存储过程的创建管理与应用（重难点）触发器的创建管理与应用（重难点）第17讲

存储过程27.1存储过程7.1.1存储过程的定义7.1.2存储过程的优点7.1.3存储过程的分类7.1.4常用的系统存储过程7.1.5创建与调用存储过程7.1.6管理存储过程7.1.7存储过程提高★★7.1.5创建与调用存储过程7.1.5

创建与调用存储过程在SQLServer中，可以使用两种方法创建存储过程：①利用SQLServer对象管理器创建存储过程。②使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。1、创建存储过程7.1.5

创建与调用存储过程下面介绍CREATEPROCEDURE命令的使用。定义存储过程的语法：CreateProc[edure]<存储过程名>[<@参数名1>…]

as<表达式>SQL语句1、创建存储过程7.1.5

创建与调用存储过程例1：学号为“2011101”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。Create

Procedure

del_student

as

BEGIN

delete

from

选课表

where

学号='2011101'

delete

from

学生表

where

学号='2011101'ENDCreate

Procedure

del_student

@sno

nvarchar(50)as

BEGIN

delete

from

选课表

where

学号=@sno

delete

from

学生表

where

学号=@snoEND1、创建存储过程7.1.5

创建与调用存储过程调用存储过程的语法：直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：2、调用存储过程exec[ute]<存储过程名>[<@参数名1>…]执行存储过程：要执行存储过程，只要调用存储过程名即可。如果过程不是批命令中的第一条语句，则要在过程名前面加上关键字EXEC。这是因为存储过程名不是关键字，当语句在执行过程中遇到存储过程名时，会将其作为上一条语句的一部分处理，因此导致执行出错。7.1.5

创建与调用存储过程2、调用存储过程例2：执行存储过程del_studentdel_student对Select

*

from

学生表del_student

Select

*

from

学生表exec

del_student

对错，因为del_student不是SQL定义的关键字，所以分析器将其解释成了student表的别名，从而导致执行结果出错。修改为7.1.5

创建与调用存储过程3、存储过程的实训任务创建存储过程

Create

Proc[edure]

存储过程名

@参数1

数据类型=默认值OUTPUT,

@参数n

数据类型=默认值OUTPUT

AS

SQL语句任务1：没有参数的存储过程任务2：有输入参数的存储过程任务3：有参数默认值的存储过程任务4：有输出参数的存储过程的创建任务5：有输出参数的存储过程的执行和C语言的函数一样，参数可选；参数分为输入参数、输出参数；输入参数允许有默认值；OUTPUT：表明该参数是一个返回参数。7.1.5

创建与调用存储过程例3：创建一个没有参数的存储过程。任务1：没有参数的存储过程create

proc

test1

as

begin

--SQL代码省略end调用该存储过程exec

test1

7.1.5

创建与调用存储过程例4：创建一个没有参数的存储过程proc_ItemInfo，查询物料的基本信息。任务1：没有参数的存储过程create

procedure

proc_ItemInfoas

select

*

from

物料go调用该存储过程execproc_ItemInfo上机实验任务二实验7-1第二题：【存储过程基础练习】8分钟1、创建一个无参数存储过程p1并调用该存储过程，要求查询以下信息：客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。7.1.5

创建与调用存储过程存储过程的参数分两种：输入参数、输出参数。输入参数：用于向存储过程传入值，类似C语言的按值传递；输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递。7.1.5

创建与调用存储过程例5：创建一个有参数的存储过程任务2：有输入参数的存储过程create

proc

test2@value1

int,@value2

intasbegin--SQL代码省略end调用该存储过程exec

test2

1000,2000--或：exec

test2

@value1

=1000,

@value2=2000上机实验任务三实验7-1第二题：【存储过程基础练习】8分钟2、创建一个带参数的存储过程p2并调用该存储过程，要求该存储过程根据传入的员工编号，在员工人事表中查询该员工的信息。7.1.5

创建与调用存储过程例6：设置参数的默认值任务3：有参数默认值的存储过程Create

Proc

test3@value1

int=100,

@value2

int=400asbegin

--SQL代码省略end调用该存储过程exec

test3--都采用默认值--或：exec

test3

300,500--都不采用默认值exec

test3

300--value2采用默认值exec

test3

,500--value1采用默认值×exec

test3

@value2

=500①默认值放在参数的数据类型后面，而不是放在参数变量的后面。②为了调用的方便，推荐将默认参数放置在参数列表的最后。上机实验任务一实验7-2第一题：【存储过程基础练习】8分钟3、创建一个带参数默认值的存储过程p3，并调用该存储过程，要求该存储过程根据查询指定薪水范围的员工信息。默认值：最低薪水：3500，最高薪水：50007.1.5

创建与调用存储过程例7：创建带有输出参数的存储过程任务4：有输出参数的存储过程如果调用批命令将变量作为参数传入存储过程，而参数在存储过程中被修改，则修改不会传给调用该存储过程的命令，除非生成和执行存储过程时对参数指定OUTPUT关键字。create

proc

test4@value

varchar(50),

@value_out

varchar(50)

OUTPUTasselect

@value_out

=valueX

from

table1

where

value=@value

return

go上机实验任务二实验7-2第一题：【存储过程基础练习】5分钟4、创建一个有输出参数的存储过程p4，该存储过程根据传入的员工姓名，查询该员工的编号。(只创建过程)7.1.5

创建与调用存储过程例8：调用带输出参数的存储过程test4。DECLARE

@value_save

VARCHAR(50)EXEC

test4

@value='myValue',

@value_out=@value_save

OUTPUTPRINT

@value_save

GO任务5：有输出参数的存储过程的执行调用带输出参数的存储过程要声明一个存储返回值的变量，执行语句还要包括OUTPUT关键字，否则修改无法在调用中反映出来。注意：参数名（这里是@value_out）在表达式左边列出，而本地变量（@value_save）则设置为等于输出参数的值，在表达式右边列出。上机实验任务三实验7-2第一题：【存储过程基础练习】15分钟4、创建一个有输出参数的存储过程p4，该存储过程根据传入的员工姓名，查询该员工的编号。(调用过程)5、根据指定的员工姓名，调用存储过程p5，并在销售主表中查询该员工的所有订单编号。（要求创建新的存储过程p5，并调用此过程）中场小结提问小结请写出以下存储过程的执行命令，要求@num1使用默认值，@num2使用65：create

proceduretest5@num1int=60,@num2int=70 as…中场小结提问小结

--定义存储过程

CREATEPROCproc_stuInfo

@stuNameVARCHAR(10),

@mINTOUTPUT,

@stuAge=18INT

AS

.....代码略

--调用存储过程

DECLARE@sINT

EXECproc_stuInfo'张三',@s,30

EXECproc_stuInfo'张三',@s

EXECproc_stuInfo

@stuAge=22,@stuName='李四',@m=@sOUTPUT代码阅读：指出下列语句的错误@stuAge

INT=18EXECproc_stuInfo'张三',@s

OUTPUT,30EXECproc_stuInfo'张三',@s

OUTPUT正确

7.1.6管理存储过程7.1.6

管理存储过程查看存储过程修改存储过程重命名存储过程删除存储过程存储过程重新编译7.1.6

管理存储过程查看存储过程存储过程源代码保存在系统表syscomments的text列中，可以直接查询该列获取信息。也可以使用系统存储过程sp_helptext获取存储过程的源代码。例1：查看PO数据库中proc_ItemInfo存储过程的源代码。exec

sp_helptext

proc_ItemInfo如果开发人员不让用户浏览源代码，则可以在生成存储过程时加上WithEncryption选项，则源代码经过加密处理。注意：SQLServer可以内部解密源代码，但该方法使得用户或任何最终用户工具都无法解密源代码7.1.6

管理存储过程查看存储过程可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_help[[@objname=]name]--参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]--参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’--参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表7.1.6

管理存储过程2.修改存储过程（1）使用对象资源管理器来修改用户创建的存储过程。在对象资源管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择“修改”选项，此时便可以看到存储过程的源代码，当然也可以进行更改。

7.1.6

管理存储过程2.修改存储过程（2）使用命令语句来修改用户创建的存储过程。存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但其优点是不必删除存储过程再重建，因此，也不必重新指定权限。例2：修改PO数据库中proc_ItemInfo存储过程ALTER

PROCEDURE

proc_ItemInfowith

encryption

--加密处理asbegin

select

*

from

物料endgo7.1.6

管理存储过程3.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：例3：重命名存储过程proc_test为proc_test1sp_rename

原存储过程名称,新存储过程名称sp_rename

proc_test,proc_test17.1.6

管理存储过程4.删除存储过程删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：例4：删除存储过程proc_test1DROPPROCEDURE{procedure}[,…n]Drop

Proc

proc_test17.1.6

管理存储过程5.存储过程重新编译在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQLServer提供三种重新编译存储过程的方法：（1）在建立存储过程时设定重新编译语法格式：CREATE

PROCEDURE

proc_nameWITH

RECOMPILEAS

sql_statement7.1.6

管理存储过程5.存储过程重新编译在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译，SQLServer提供三种重新编译存储过程的方法：（2）在执行存储过程时设定重编译语法格式：EXECUTE

proc_nameWITH

RECOMPILE（3）通过使用系统存储过程设定重编译语法格式：EXEC

sp_recompileOBJECT7.1.7存储过程提高7.1.7

存储过程提高返回过程状态实现数据插入在存储过程中使用临时表远程存储过程7.1.7

存储过程提高1.返回过程状态大多数编程语言能够向调用者返回过程状态，0通常表示成功，SQlServer也不例外。例1：返回某物料信息，如果该物料名称不存在，则返回状态值－101Create

Proc

test2

@name

varchar(50)as

if

not

exists(select

*

from

物料

where

物料名称=@name)

return

-101

select

*

from

物料

where

物料名称=@name

return7.1.7

存储过程提高1.返回过程状态大多数编程语言能够向调用者返回过程状态，0通常表示成功，SQlServer也不例外。例2：执行存储过程test2Declare

@status

intexec

@status=

test2

@name='螺丝刀'if

@status=-101Print

'Notitlewiththatnamefound.'7.1.7

存储过程提高1.返回过程状态SQLServer的内部状态码。如果存储过程异常终止，则返回一个状态码，SQLServer保留了－1到－99的值。状态码含义状态码含义0执行成功-8存在非致命性内部错误-1缺对象-9达到系统极限-2存在数据类型错误-10存在致命内部不一致性-3选择该进程为死锁受害者-11存在致命内部不一致性-4存在权限错误-12表或索引混乱-5存在语法错误-13数据库混乱-6存在其他用户错误-14硬件故障-7存在资源错误，如空间不足7.1.7

存储过程提高2.实现数据插入例3：插入一个新的物料信息Create

Proc

test3

(@id

int,

@name

varchar(50),

@kind

int,

@unit

nvarchar(20))asinsert

into

物料values(@id,@name,@kind,@unit)returntest3

@id=4001,

@name='粉笔',

@kind=1,

@unit='盒'7.1.7

存储过程提高3.在存储过程中使用临时表中间结果要在工作表中存放，以便进行其他处理或更高级的处理时，通常要在存储过程中