sql server课件第16讲存储过程

1、SQL Server 2005数据库管理系统第十六讲 存储过程16.1 概述16.2 创建存储过程16.3 查看存储过程 16.4 修改存储过程16.5 执行存储过程16.6 删除存储过程16.7 存储过程及其参数15. 1 概述 存储过程的概念 SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程过程中，可将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行

2、。 存储过程的优点 1、实现了模块化编程 存储过程经过了一次创建以后，可以被调用无数次。用户可以独立于应用程序而对存储过程进行修改。可按独特的功能模式设计不同的存储过程以供使用。存储过程的优点 2、存储过程具有对数据库立即访问的功能 存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行时不必再次进行编译，从而提高了程序的运行效率。 存储过程的优点 3、使用存储过程可以加快程序的运行速度 存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必再重复这些步骤。存储过程在经过第一次调用之后，就驻留在内存中，不必再经过编译和优化，所以执

3、行速度快。在有大量批处理的Transact-SQL语句要重复执行的时候，使用存储过程可以极大提高运行效率。存储过程的优点 4、使用存储过程可以减少网络流量 存储过程中可以包含大量的Transact-SQL语句，但存储过程作为一个独立的单元来使用。在进行调用时，只需要使用一个语句就可以实现，所以大大减少了网络上数据的传输。存储过程的优点 5、使用存储过程可以提高数据库的安全性 可以设置用户通过存储过程对某些关键数据进行访问，但不允许用户直接使用T-SQL或企业管理器对数据进行访问。存储过程的分类系统存储过程 用户自定义存储过程 15. 2 创建存储过程 1、使用企业管理器创建存储过程 在SQL

4、Server企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建|存储过程”选项。2、使用Transact-SQL语句创建存储过程 命令格式:create proc edure procedure_name ;number parameter data_type varying =dafault output with encryption as sql_statement n procedure_name: 新存储过程的名称. number: 可选的整数,用来对同名的过程分组,以便用一条drop procedure语句即可将同组的过程一起除去.

5、 parameter: 过程中的参数.存储过程最多可以有2100个参数。 data_type: 参数的数据类型. varying: 指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化).default: 参数的默认值. output: 表明参数是返回参数. n: 表示最多可以指定2100个参数的占位符. as: 指定过程要执行的操作. sql_statement: 过程中要包含的任意数目和类型的Transact-SQl 语句,但有一些限制. as sql_statement: n : 其中的n是表示此过程可以包含多条Transact-SQL语句的占位符.例：use 学生管理数据库G

6、oif exists (select name from sysobjectswhere name = my_proc1 and type = p)drop procedure my_proc1 Gocreate proc my_proc1 as select 学号,姓名,性别,年龄,所在院系 from 学生表 where 所在院系=计算机and 性别=女15. 2 创建存储过程3、 使用向导创建存储过程 在企业管理器中，选中某个SQL Server服务器，选择要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，系统会弹出“选择向导”对话框，单击向导中“数据库”选项左边的加号，选中“创

7、建存储过程向导”选项。 15. 3 查看存储过程 1、使用企业管理器查看用户创建的存储过程（1）从树型结构上选中存储过程所在的数据库节点。（2）选中数据库节点下的存储过程节点（3）右击存储过程，选择快捷菜单上的“属性”命令。15. 3 查看存储过程2、使用T-SQL语句查看存储过程（1）sp_helptext：用于显示存储过程的源代码。 sp_helptext objname= name 参数name为要查看的存储过程的名称。 例如： Use 学生课程数据库 go sp_helptext my_proc go15. 3 查看存储过程（2）sp_help：用于显示存储过程的一般信息（参数及其数据

8、类型） sp_help objname= name 参数name为要查看的存储过程的名称。 例如： Use 学生课程数据库 go sp_help my_proc go15. 3 查看存储过程（3）如果要知道某个表被存储过程引用的情况，则可以使用sp_depends语句查看。 例如： sp_depends lwqk15. 3 查看存储过程（4）sp_depends：用于显示和存储过程相关的数据库对象 sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。 例如： sp_depends my_proc15. 3 查看存储过程（5） 返回当前数据库中的

9、存储过程列表 Execs sp_stored_procedures 命令格式: alter procedure procedure_name ; number parameter data_type varying =dafault output with encryption as sql_statement n 15.4 修改存储过程例： use 学生课程数据库 go alter proc my_proc as select 学号,姓名,性别,年龄,所在院系 from 学生表 where 所在院系=计算机15.4 修改存储过程 存储过程创建成功后，保存在数据库中。在SQL Server中可

10、以使用EXECUTE命令来直接执行存储过程。 execute return_status = procedure_name ; number | procedure_name_var parameter = value | variable output | default , n 15.5 执行存储过程 return_status: 一个可选的整形变量,保存存储过程的返回状态。 procedure_name: 拟调用的存储过程完全合法或者不完全合法的名称。 number: 可选的整数,用于将相同的名称的过程进行组合,使得它们可以用一句drop procedure语句除去。 procedure

11、_name_var: 局部定义变量名,代表存储过程的名称。parameter: 过程的参数,在create procedure 语句中定义. value: 过程中参数的值. variable: 用来保存参数或者返回参数的变量. output: 指定存储过程必须返回一个参数. default: 根据过程的定义提供参数的默认值. n: 占位符,表示在它前面的项目可以多次重复执行. 如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用该存储过程。例如： use lwzz go my_proc1 go 如果批处理中对存储过程的调用不是第一个语句，应该使用EXECUTE或EXEC关

12、键字： use lwzz exec my_proc1 go15. 6 重命名存储过程 1 、 重命名存储过程 1）使用企业管理器修改存储过程名称 2）使用系统存储过程修改存储过程名称 语法形式： sp_rename 原存储过程名称，新存储过程名称15. 7 删除存储过程1、使用企业管理器删除存储过程（ 1）从树型结构上选中存储过程所在的数据库节点。（2）选中数据库节点下的存储过程节点。（3）选中要删除的存储过程，右击鼠标，在弹出的快捷菜单中选择“删除”命令。（4）选择“全部除去”按钮即完成操作。15. 7 删除存储过程2、使用Transact-SQL语句删除存储过程 drop procedur

13、e 例： use 学生课程数据库 go drop procedure my_proc输入参数: 输入参数允许调用程序向存储过程传送数据值。输出参数: 输出参数允许存储过程将数据值或指针变量传回调用程序。15. 8 存储过程及其参数1、输入参数： use 学生课程数据库 go create procedure my_procss ( sex char(2) ) as select * from 学生表 where 性别=sex return go execute my_procss 女 go execute my_procss男 2、输出参数： use 学生课程数据库 go create pro

14、cedure my_procname ( name varchar(8) =null, sex char (2) output, age tinyint output ) as if name=null begin print 查找哪位同学的年龄? return end select age=年龄, sex =性别 from 学生表 where 姓名=name return use 学生课程数据库 go declare ages tinyint,sexs char(2) execute my_procname 沈香娜, sexs output ,ages output select ages

15、as 年龄,sexs as性别 在执行存储过程时,参数既可以由位置标识,也可以由名字标识。 create procedure max_int (a int,b int,c int, maxabc int output ) as if (a=b) and (a=c) select maxabc=a else if (b=a) and (b=c) select maxabc=b else select maxabc=c return15. 8 关于参数顺序的问题declare maxs intexecute max_int 2,9,7, maxs output select maxs或者:declare maxs intexecute max_int a= 2, c= 7, b= 9, maxs outputselect maxs15. 8 关于参数顺序的问题15. 9存储过程的返回值Create proc Test_ret ( intput _int int =0 )If intput _int 0 return 1If intput _int 0 return -1If intput _int =0 return 0Go GoDeclare Ret_int intExec Ret_int= Test_ret-50Select返回值=Ret