SqlServer2008CH7创建和管理存储过程

1、精选ppt1创建和管理存储过程创建和管理存储过程精选ppt2目录目录n 批处理批处理 n 存储过程存储过程n创建存储过程创建存储过程n执行存储过程执行存储过程n 修改与删除存储过程修改与删除存储过程n 自定义函数自定义函数n 创建自定义函数创建自定义函数n 执行自定义函数执行自定义函数n 修改与删除自定义函数修改与删除自定义函数精选ppt3NO:1 NO:1 上节回顾精选ppt4 上节回顾上节回顾n 批量批量:n 变量变量: 局部变量局部变量 全局变量全局变量n 打印消息打印消息:n 控制流语言控制流语言: if else case while精选ppt5NO:1 NO:1 存储过程精选ppt

2、6存储过程存储过程n 存储过程是服务器上一组预先定义并编译好的存储过程是服务器上一组预先定义并编译好的SQL语句语句n 它可以接受参数、返回状态值和参数值，并且可它可以接受参数、返回状态值和参数值，并且可以嵌套调用以嵌套调用n 使用存储过程可以改变使用存储过程可以改变SQL语句的运行性能，提高语句的运行性能，提高其执行效率其执行效率n 存储过程还可以用作一种安全机制，使用户通过存储过程还可以用作一种安全机制，使用户通过它访问未经授权的表或视图它访问未经授权的表或视图n 类似于高级语言中的子程序类似于高级语言中的子程序精选ppt7存储过程存储过程 -存储过程分类存储过程分类n 系统存储过程（系统

3、存储过程（sp_）：存放在）：存放在master数据库中，提供存数据库中，提供存储系统表信息的快捷方式，可在任何数据库执行储系统表信息的快捷方式，可在任何数据库执行n 局部（本地）存储过程：在各个用户数据库中创建局部（本地）存储过程：在各个用户数据库中创建n 临时存储过程：可以是局部的（临时存储过程：可以是局部的（#），也可以是全局的），也可以是全局的（#），局部临时存储过程在单个用户任务中有效，全局临），局部临时存储过程在单个用户任务中有效，全局临时存储过程在所有用户任务中有效时存储过程在所有用户任务中有效n 远程存储过程：从连接到不同服务器的远程服务器或客户远程存储过程：从连接到不同服务器

4、的远程服务器或客户机调用的存储过程，早期版本使用，现由分布查询支持机调用的存储过程，早期版本使用，现由分布查询支持n 扩展存储过程（扩展存储过程（xp_）：在）：在SQL环境外部执行的环境外部执行的DLL精选ppt8存储过程存储过程 -优点优点n 共享应用程序逻辑：用户使用相同的存储过程保共享应用程序逻辑：用户使用相同的存储过程保证一致的数据修改证一致的数据修改n 提供安全机制：用户可以被授予执行存储过程的提供安全机制：用户可以被授予执行存储过程的权限，及时无权访问存储过程引用的表或视图权限，及时无权访问存储过程引用的表或视图n 自动执行：能在启动时自动执行自动执行：能在启动时自动执行n 提高

5、性能：第一次执行后，执行计划存放在高速提高性能：第一次执行后，执行计划存放在高速缓存中缓存中n 减少网络流量：通过发送一条语句来执行一个复减少网络流量：通过发送一条语句来执行一个复杂的操作，减少了在服务器和客户机之间传递的请求杂的操作，减少了在服务器和客户机之间传递的请求数数精选ppt9存储过程存储过程 -创建存储过程创建存储过程n 语法：语法：create procedure procedure_nameparameter data_type =default outputAsbeginsql_statement,nend说明：说明：nProcedure_name：存储过程名：存储过程名np

6、arameter：局部变量名：局部变量名noutput：指定局部变量是否为输出参数：指定局部变量是否为输出参数精选ppt10存储过程存储过程 -创建存储过程的几个注意创建存储过程的几个注意n create proc所创建的存储过程可以为永久对象，所创建的存储过程可以为永久对象，也可为临时对象，由名字决定也可为临时对象，由名字决定n create proc语句不可以与其他语句不可以与其他SQL语句一起使用语句一起使用在过程定义中不能使用在过程定义中不能使用create语句创建视图、默认、语句创建视图、默认、规则、触发器、存储过程。在过程中创建了以外的对规则、触发器、存储过程。在过程中创建了以外的

7、对象，之后又删除它们，在该过程中不能再创建它们的象，之后又删除它们，在该过程中不能再创建它们的同名对象同名对象n 在存储过程嵌套调用时，被调用的存储过程可以在存储过程嵌套调用时，被调用的存储过程可以访问调用这所建立的除临时表以外的所有数据库对象访问调用这所建立的除临时表以外的所有数据库对象精选ppt11存储过程存储过程 -创建存储过程的几个注意创建存储过程的几个注意(续续)n 嵌套调用只能在嵌套调用只能在32个以内，当前调用级别个以内，当前调用级别存储在存储在nestlevle中中n 要创建存储过程必须时系统管理员角色、要创建存储过程必须时系统管理员角色、数据库所有者角色或数据定义语言管理角色

8、之数据库所有者角色或数据定义语言管理角色之一，或者被授予一，或者被授予create procedure许可许可n 根据可使用的内存，存储过程最大的尺寸根据可使用的内存，存储过程最大的尺寸为为128M精选ppt12存储过程存储过程 - ( (示例示例1)1)n 建立一个不带参数的存储过程建立一个不带参数的存储过程. create proc simpleProcDemo as select * from student_table精选ppt13存储过程存储过程 -执行存储过程执行存储过程n 语法：语法： execute return_status=pro_name parameter=value

9、outputreturn_status：返回状态（即错误代号），：返回状态（即错误代号），为为0时证明执行成功，其他值为失败时证明执行成功，其他值为失败精选ppt14存储过程存储过程 -执行存储过程执行存储过程n 示例示例: 执行存储过程有三种方式执行存储过程有三种方式. execute simpleProcDemo exec simpleProcDemo simpleProcDemo精选ppt15存储过程存储过程 -存储过程的参数传递存储过程的参数传递n execute语句中可使用两种方法传递参数，语句中可使用两种方法传递参数，执行过程中可以随意使用，当两种不可混用执行过程中可以随意使用，当

10、两种不可混用nparameer_value1,parameter_value2.在这种方式下，参数顺序要严格按定义时的顺在这种方式下，参数顺序要严格按定义时的顺序，默认值可省略，但要按照顺序序，默认值可省略，但要按照顺序nparameter_name1=value,.在这种方式在这种方式下，参数名与参数值唯一对应，顺序可任意排下，参数名与参数值唯一对应，顺序可任意排列列精选ppt16存储过程存储过程 -存储过程的参数传递存储过程的参数传递n 示例示例1:create proc procWithparameter(temp int)as print temp-检验检验execute procWi

11、thparameter 1精选ppt17存储过程存储过程 -存储过程的参数传递存储过程的参数传递n 示例示例2:create proc procWithParameter_1vInputClassNum int as begin select * from student_table where class_num=vInputClassNum end-检验检验execute procWithParameter_1 2精选ppt18存储过程存储过程 -修改和删除存储过程修改和删除存储过程n 修改存储过程修改存储过程n语法：语法：alter proc proc_name as sql_state

12、mentn 删除存储过程删除存储过程n语法：语法：drop proc proc_name精选ppt19存储过程存储过程 - 通知执行成功通知执行成功/* 通知过程执行是否成功通知过程执行是否成功 */create proc procWithReturn(temp int)as if(temp=0) begin print temp=0,执行成功 return 0 end else begin print temp=1,执行失败 return 1 end 精选ppt20存储过程存储过程 - 通知执行成功通知执行成功/* 通知过程执行是否成功通知过程执行是否成功 */create proc pro

13、cWithReturn(temp int)as if(temp=0) begin print temp=0,执行成功 return 0 end else begin print temp=1,执行失败 return 1 end 精选ppt21存储过程存储过程 -通知执行成功通知执行成功-检验检验 declare returnNum int execute returnNum=procWithReturn 0 print returnNum 精选ppt22存储过程存储过程 -过程调用过程过程调用过程/* 过程调用过程 */create proc procDemo1(intClassNum int

14、)as select Name from student_table where class_num=intClassNumcreate proc procDemo2(cAddress char(5)as select Name from student_table where address=cAddress精选ppt23存储过程存储过程 -过程调用过程过程调用过程create proc procDemo3(intClassNum int,cAddress char(5)as exec procDemo1 intClassNum exec procDemo2 cAddress-检验exec

15、procDemo3 1,B1 精选ppt24存储过程存储过程 -返回多个输出值返回多个输出值/* 返回多个输出值 */create proc procDemo4( intID int, vcName varchar(20) output, cAddress char(2) output )as select vcName=name,cAddress=address from student_table where ID=intID return 0 精选ppt25存储过程存储过程 - 返回多个输出值返回多个输出值-多个输出值过程调用多个输出值过程调用create proc procDemo5(

16、intID int) as declare vcName varchar(20) declare cAddress char(2) declare invokeFlag int -调用过程是否成功标志调用过程是否成功标志 exec invokeFlag=procDemo4 intID,vcName output,cAddress output if(invokeFlag=0) begin print name= +vcName+ address= +cAddress end else begin print procedure: procDemo4 invoke error end精选ppt2

17、6存储过程存储过程 - 返回多个输出值返回多个输出值-检验检验 declare tempInvokeFlag int exec tempInvokeFlag=procDemo5 1 If(tempInvokeFlag=0) print procDemo5 调用成功调用成功 else print procDemo5 调用失败调用失败精选ppt27存储过程存储过程 - 使用使用recompile /* 使用recompile */create proc tempProc with recompileas print ok精选ppt28NO:2 NO:2 自定义函数自定义函数精选ppt29自定义函数

18、n 用户自定义函数类似于存储过程，是由一用户自定义函数类似于存储过程，是由一个或多个个或多个T-SQL语句构成的子程序。它把这些语句构成的子程序。它把这些语句封装起来，构成一个可以重复使用的程序语句封装起来，构成一个可以重复使用的程序单元，用户自定义函数与存储过程的不同之处单元，用户自定义函数与存储过程的不同之处在于函数必须返回数值在于函数必须返回数值n 允许创建用户定义函数是从允许创建用户定义函数是从SQL 2000开始开始的新增功能，在之前的版本中用户只能调用的新增功能，在之前的版本中用户只能调用SQL内置的函数内置的函数精选ppt30自定义函数 -用户自定义函数的种类用户自定义函数的种类

19、n 标量函数：标量函数只能返回单个数值，标量函数：标量函数只能返回单个数值，其返回值可为其返回值可为timestame、text、next、image、table和和cursor之外的所有系统数据之外的所有系统数据类型，但不能为用户自定义数据类型类型，但不能为用户自定义数据类型n 表值函数：表值函数的返回值为表值函数：表值函数的返回值为table数据数据类型。分为两类：内嵌表值函数和多语句表值类型。分为两类：内嵌表值函数和多语句表值函数函数精选ppt31自定义函数 -创建用户自定义函数创建用户自定义函数语法：语法： create functionowner, function_name(par

20、ameter type=default.) returns type with option. as begin function_body return parameter end精选ppt32自定义函数 - 示例示例 - 建立函数建立函数,返回指定学号的名称返回指定学号的名称,建立此函数建立此函数create function functionDemo2(id int)returns varchar(20)as begin declare vName varchar(20)select vName=Namefrom student_tablewhere id=id return vName end精选ppt33自定义函数 -调用自定义函数调用自定义函数-验证验证select id,student name=dbo.functionDemo2(id)from student_table精选ppt34自定义函数 -删除和修改自定义函数删除和修改自定义函数修改修改nalter function dbo.funtion_name 新的定义新的定义n和定义语句一样和定义语句一样n保留原有的权限保留原有的权限删除删除ndrop