《SQL Server数据库应用与维护》-第9讲 存储过程

11.SQLServer2008的安全机制（重点）2.创建登录账户（重点）3.创建数据库用户（重点）4.角色与权限（重难点）上节回顾0上节:《第8讲：用户管理》词汇巩固

温故REVOKEDELETE,INSERT,UPDATEON

学生表FROM

用户ACASCADE抢答：加了CASCADE有什么效果。请举例！2

课程引入

词汇巩固

知新小老师：“存储过程是什么？”13

存储过程

词汇巩固

知新1

可读性？修改？大老师提问1：“编写100行T-SQL语句访问数据库”？4

存储过程

词汇巩固

知新1怎么办？大老师提问2：“黑客攻击----SQL注入”5

存储过程

词汇巩固

知新大老师：“存储过程到底是什么？！”1存储过程（StoredProcedure）：是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它，以实现某个任务。62.1任务演示2.2创建存储过程（重难点）2.3执行存储过程（重点）2.4存储过程实训任务（重难点）2.5删除存储过程（重点）2.6浏览存储过程2.7修改存储过程（重点）2.8存储过程提高（难点）

存储过程的管理

27学号姓名…2013020001孙雷…2013020002王明…2013020003张丽丽…学号课程号成绩2013020001C1952013020001C2802013020001C185学生表选课表2.1任务演示

任务演示：

由于学生的信息变化或学生转学等原因，学生的数据需要进行相应的处理。教务系统决定定期处理一批无效学生记录。

【例如】：学号为“2013020001”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。解决方法：

你能想到什么方案呢？81、直接对学生表和选课表进行修改。（好与坏？）方法：删除某学生记录的同时，删除其选课记录分两步：

delete fromsc wheresno=‘2013020001’ delete fromstudent wheresno=‘2013020001’ 2、创建一个存储过程，以实现对上述的修改。（好与坏？）假如已经建好了存储过程：del_student调用：execdel_student‘2013020001’假如学生相关的数据表有100多张，怎么办！！！！2.1任务演示假如有10位学生的数据要删除，怎么办！！！92.2创建存储过程CreateProceduredel_student

@snonvarchar(50)asBEGIN deletefromsc wheresno=@sno deletefromstudent wheresno=@snoENDCreateProc[edure]<存储过程名>[<@参数名1>…]

as<表达式>【例】：学号为“2013020001”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。CreateProceduredel_student

asBEGIN deletefromsc wheresno=‘2013020001’ deletefromstudent wheresno=‘2013020001’END10执行存储过程：要执行存储过程，只要调用存储过程名即可。如果过程不是批命令中的第一条语句，则要在过程名前面加上关键字EXEC。这是因为存储过程名不是关键字，当语句在执行过程中遇到存储过程名时，会将其作为上一条语句的一部分处理，因此导致执行出错。【例】：执行存储过程del_student

：del_studentSelect*fromstudent

del_student

Select*fromstudent

exec

del_student

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

exec[ute]<存储过程名>[<@参数名1>…]对11

中场小结12.X

存储过程（StoredProcedure）：是在大型数据库系统中，一组为了完成特定功能的SQL语句集。1、创建存储过程

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

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

Exec[ute]<存储过程名>[<@参数名1>…]词汇巩固

温故预编译指定名称、参数可选创建存储过程执行存储过程完成任务122.4存储过程实训任务创建存储过程

Create

Proc[edure]

存储过程名

@参数1

数据类型=默认值OUTPUT,

@参数n

数据类型=默认值OUTPUT

AS

SQL语句任务1：没有参数的存储过程任务2：有输入参数的存储过程任务3：有参数默认值的存储过程任务4：有输出参数的存储过程的创建任务5：有输出参数的存储过程的执行13【例】创建一个没有参数的存储过程

create

proctest1

as

begin

--SQL代码省略

end

执行该存储过程

exectest1

任务1：没有参数的存储过程【上机任务1-1】创建一个没有参数的存储过程StuScoreInfo,查询以下信息:学号,姓名,性别,课程名称,考试成绩。14【例】创建一个有参数的存储过程

createproctest2 @value1int, @value2int as select*fromtable1 wherevaluebetween@value1and@value2假设要显示value在1000到2000之间的信息：exectest21000，2000任务2：有输入参数的存储过程【上机任务1-2】创建一个有参数的存储过程stu_info,该存储过程根据传入的学生编号在student表中查询此学生的信息.1515【例】设置参数的默认值

CreateProctest3

@valueint=100

asbegin

--SQL代码省略

end任务3：有参数默认值的存储过程【上机任务1-3】创建一个有参数、有默认值的存储过程stu_info_bySex,该存储过程根据传入的学生的性别（默认为：男）查询学生的基本信息。1616如果调用批命令将变量作为参数传入存储过程，而参数在存储过程中被修改，则修改不会传给调用该存储过程的命令，除非生成和执行存储过程时对参数指定OUTPUT关键字【例】有输出参数的存储过程Createproctest4 @valuevarchar(50),@value_outvarchar(50)

OUTPUT as select@value_out=valueXfromtable1 wherevalue=@value

return

任务4：有输出参数的存储过程的创建【上机任务1-4】创建一个有输出参数的存储过程stu_info_out,

该存储过程根据传入的学生学号查询学生的专业，并将学生的所在专业输出（通过@speciality参数）。1717

调用存储过程要声明一个存储返回值的变量，执行语句还要包括OUTPUT关键字，否则修改无法在调用中反映出来

Declare

@value_savevarchar(50)

Exectest4@value=‘myValue',@value_out=@value_saveOUTPUT

Print@value_save【注意】参数名（这里是@value_out

）在表达式左边列出，而本地变量（@value_save）则设置为等于输出参数的值，在表达式右边列出。任务5：有输出参数的存储过程的执行【上机任务1-5】调用存储过程stu_info_out，查询学号为2013020002的学生的所在专业。并声明一个变量@speciality_save,用来存储输出的专业信息。18存储过程是什么？使用存储过程有什么好处？请写出以下存储过程的执行命令，要求@num1使用默认值，@num2使用65：create

proceduretest5@num1int=60,@num2int=70 as…

中场小结22.X词汇巩固

温故19代码阅读:指出下列语句的错误

--定义存储过程

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'张三',@sOUTPUT正确

考一考2.X词汇巩固

温故2020格式：DropProc

Procedure_name【例】：删除存储过程stu_info

DropProcstu_info

2.5删除存储过程2121存储过程源代码保存在系统表syscomments的text列中，可以直接查询该列获取信息。也可以使用系统存储过程sp_helptext获取存储过程的源代码。【例如】execsp_helptextstu_info如果开发人员不让用户浏览源代码，则可以在生成存储过程时加上WithEncryption选项，则源代码经过加密处理。注意：SQLServer可以内部解密源代码，但该方法使得用户或任何最终用户工具都无法解密源代码2.6浏览存储过程2222修改存储过程：AlterProc的语法和CreateProc语法相同，但其优点是不必删除存储过程再重建，因此，也不必重新指定权限【例】修改存储过程

AlterProcstu_info

AS

begin

--SQL代码省略

end2.7修改存储过程2323返回过程状态大多数编程语言能够向调用者返回过程状态，0通常表示成功，SQlServer也不例外。【例】：返回某本书到目前为止的销量，如果该书不存在，则返回状态值－101

CreateProctest6@titlevarchar(80)

as

ifnotexists(select*fromtitleswheretitle=@title)

return-101

selectytd_sales fromtitles

wheretitle=@title

return如果某本书的书名不存在，则返回-1012.8存储过程提高2424返回过程状态【例】：执行存储过程test6

Declare@statusint

exec@status=test6

@title='LifewithoutFear'

if@status=-101

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

CreateProcinsertauthor (@au_idvarchar(11),@au_lnamevarchar(40),@au_fnamevarchar(20),@phonechar(12),@contractbit)

as

insertintoauthors(au_id,au_lname,au_fname,phone,contract)

values(@au_id,@au_lname,@au_fname,@phone,@contract)

return

insertauthorau_id=‘000-00-0000’,@au_lname=‘new’,@au_fname=‘one’,@phone=‘555-555’555’,@contract=0262.8存储过程提高2727在存储过程中使用临时表中间结果要在工作表中存放，以便进行其他处理或更高级的处理时，通常要在存储过程中使用临时表。注意：存储过程退出时，存储过程中生成的局部临时表自动删除，而全局临时表则仍然存在，直到显式删除，或生成临时表的用户与SQLServer断开。2.8存储过程提高2828在存储过程中使用临时表例如：生成存储过程，将authors表的查询信息保存到临时表temp中

Createproctemp_test

as select*into##temp

from