第7章 存储过程

lwqk
查看存储过程
（4） sp_depends：用于显示和存储过程相关的数据库对象 sp_depends [@objname=]’object’ 参数object为要查看依赖关系的存储过程的名称。 例如： sp_depends my_proc
查看存储过程
（5） 返回当前数据库中的存储过程列表： Execs p_stored_procedures
本章首页
1、使用Transact-SQL语句创建存储过程
命令格式: 命令格式: create proc[ edure ] procedure_name [;number ] [ { @parameter data_type } [ varying ] [ =dafault ] [ output ] [ with encryption ] as sql_statement [ …n ]
这里我们举一个例子说明在SSMS中执行 这里我们举一个例子说明在SSMS中执行 SSMS 存储过程的步骤。 SSMS中执行存储过程 存储过程的步骤。在SSMS中执行存储过程 proc_goods的步骤如下 的步骤如下。 proc_goods的步骤如下。
（1）启动SSMS，连接到数据库实例，在 启动SSMS，连接到数据库实例， SSMS 对象资源管理器”窗口里， “对象资源管理器”窗口里，展开数据库实 例。 依次选择“数据库” （2）依次选择“数据库”→存放存储过 程的数据库，这里选择“ 程的数据库，这里选择“Sales”→“可编程 存储过程” proc_goods右键快捷 性”→“存储过程”→proc_goods右键快捷 菜单的“执行存储过程”选项， 菜单的“执行存储过程”选项，这时就打开 执行过程”窗口， 了“执行过程”窗口，可以看到该存储过程 有一个输入参数“ 员工编号” 有一个输入参数“@员工编号”，在该参数的 输入框中输入一个值“ “值”输入框中输入一个值“1001”。
修改存储过程
例： use 学生课程数据库 go alter proc my_proc as select 学号,姓名,性别,年龄,所在院系 from 学生表 where 所在院系='计算机‘ 书上例题【7-2】P155
2. 使用 使用SSMS修改存储过程 修改存储过程 在SSMS中修改存储过程的步骤如下： ① 展开服务器组，然后展开服务器。 ② 展开“数据库”文件夹，再展开存储过程所属的数 据库，然后单击“存储过程”文件夹。 ③ 在详细信息窗格中，右击存储过程，然后单击“属 性”命令。 ④ 在“文本”框中，按需要更改存储过程的文本。 ⑤ 若要检查语法，请单击“检查语法”命令。 ⑥ 若要更改权限，单击“权限”命令。 注：第二种方法参见P155
default:: 参数的默认值. output: 表明参数是返回参数. n: 表示最多可以指定2100个参数的占位符. as: 指定过程要执行的操作. sql_statement: 过程中要包含的任意数目和类型的 Transact-SQl 语句,但有一些限制. as sql_statement:[ …n ]: 其中的n是表示此过程 可以包含多条Transact-SQL语句的占位符.
2
存储过程的基础知识 7-1
存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
单个 SELECT 语句 SELECT 语句块
存储过程
----------------------
SELECT语句与逻辑 控制语句
可以包含
3
存储过程的基础知识 7-1
存储过程存放在服务器端数据库中，是经编译过 的能完成特定功能的T-SQL语句的集合，是作为一 个单元来处理的。在存储过程中可以对任何数据及 对象进行修改，包括新建或删除表、修改数据库设 置等。
Βιβλιοθήκη Baidu 7.1.1 存储过程的优点
存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提 供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同 的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。 存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共 例行程序。存储过程具有以下优点：
7.2.3 删除存储过程
本章首页
7.1.2 存储过程的分类
存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩 展存储过程。
系统存储过程
系统存储过程是指安装SQL Server时由系统创建的存储过程。存储 在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表 中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。 它们中的大部分可以在用户数据库中使用。
procedure_name: 新存储过程的名称. number: 可选的整数,用来对同名的过程分组,以便用 一条drop procedure语句即可将同组的过程一起除去. @parameter: 过程中的参数.存储过程最多可以有2100 个参数。 data_type: 参数的数据类型. varying: 指定作为输出参数支持的结果集(由存储过 程动态构造,内容可以变化).
@parameter: 过程的参数,在create procedure 语 句中定义. value: 过程中参数的值.
@variable: 用来保存参数或者返回参数的变量. output: default: 指定存储过程必须返回一个参数. 根据过程的定义提供参数的默认值.
n: 占位符,表示在它前面的项目可以多次重复执行.
扩展存储过程
扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。 它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如 C或C++等）创建自己的扩展过程。
用户定义的存储过程
由用户为完成某一特定功能而编写的存储过程。
7.2 存储过程的创建、修改和删除
7.2.1 创建存储过程 7.2.2 查看和修改存储过程 7.2.3 删除存储过程
3. 执行存储过程
存储过程创建成功后，保存在数据库中。 存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程 中可以使用EXECUTE命令来直接执行存储过程。 Server中可以使用EXECUTE命令来直接执行存储过程。 [[ exec[ute]] { [ @return_status = ] { procedure_name [ ; number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ output ] | [ default ] ] [ , …n ]
单击“确定”按钮，执行存储过程， （3）单击“确定”按钮，执行存储过程， 在结果窗口中就可以看到执行的结果。 在结果窗口中就可以看到执行的结果。
7.2.2 查看和修改存储过程 1、查看存储过程
1）使用企业管理器查看用户创建的存储过程 （1）从树型结构上选中存储过程所在的数据库节点。 （2）选中数据库节点下的存储过程节点 （3）右击存储过程，选择快捷菜单上的“属性”命令。
减少网络数据流量 存储过程是与数据库一起存放在服务器中并在服务器 上运行的。应用系统调用存储过程时只有触发执行存储过程 的命令和执行结束返回的结果在网络中传输。用户端不需要 将数据库中的数据通过网络传输到本地进行计算，再将计算 结果通过网络传送到服务器。所以，使用存储过程可以减少 网络中数据流量。 加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树， 第二次执行时就不用进行预编译，从而加快了系统运行速度。 另外，由于存储过程是在服务器上运行，分担了用户端的数 据处理工作，也加快了应用系统的处理速度。 加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限， 但授予用户执行存储过程的权限，通过存储过程来对这些表 或视图进行访问操作。这样，既可以保证用户能够通过存储 过程操作数据库中的数据，又可以保证用户不能直接访问与 存储过程相关的表，从而保证表中数据的安全性。
如果对存储过程的调用是批处理的第一条语句， 则可以直接使用存储过程的名字调用该存储过程。 例如： use lwzz go my_proc1 go 如果批处理中对存储过程的调用不是第一个语句， 应该使用EXECUTE或EXEC关键字： use lwzz exec my_proc1
2 ) 使用图形化工具执行存储过程
修改存储过程
1.使用SQL语句修改: 1.使用SQL语句修改: 使用SQL语句修改 alter proc[edure] procedure_name [ ; number ] [ { @parameter data_type } [ varying ] [ =dafault ] [ output ] [ with encryption ] as sql_statement [ …n ]
查看存储过程
2）、使用T-SQL语句查看存储过程 （1） sp_helptext：用于显示存储过程的源代码。 sp_helptext [[@objname=] name] 参数name为要查看的存储过程的名称。 例如： Use 学生课程数据库 go sp_helptext my_proc go
查看存储过程
第七章 存储过程
存储过程的基础知识 7-1
存储过程（procedure）类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也可以返回结果
int sum(int a,int b) { 存储过程相当于C语 言中的函数 int s; s =a+b; return s ; }
存储过程提供了处理复杂任务的能力
存储过程提供了许多标准SQL语言所没有的高级特性，它 通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL 语句处理复杂任务。
增强代码的重用性和共享性
每一个存储过程都是为了实现一个特定的功能而编写的模 块，模块可以在系统中重复地调用，也可以被多个有访问权 限的用户访问。所以，存储过程可以增强代码的重用性和共 享性，加快应用系统的开发速度，减少工作量，提高开发的 质量和效率。
2、使用企业管理器创建存储过程
在SQL Server企业管理器中，选择指定的服务器和数据 库，右击要创建存储过程的数据库，在弹出的快捷菜单中依 次选择“新建|存储过程…”选项。
使用向导创建存储过程
在企业管理器中，选中 某个SQL Server服务器，选 择要创建存储过程的数据库， 选择“工具”菜单中的“向 导”菜单项，系统会弹出 “选择向导”对话框，单击 向导中“数据库”选项左边 的加号，选中“创建存储过 程向导”选项。
例： use 学生管理数据库 Go if exists (select name from sysobjects where name = ' my_proc1 ' and type = 'p') drop procedure my_proc1 Go create proc my_proc1 as select 学号,姓名,性别,年龄,所在院系 from 学生表 where 所在院系='计算机'and 性别='女‘ 书上例题【7-1】P153
@return_status: 过程的返回状态.
一个可选的整形变量,保存存储
procedure_name: 拟调用的存储过程完全合法或 者不完全合法的名称. number: 可选的整数,用于将相同的名称的过程进 行组合,使得它们可以用一句drop procedure语句除去. @procedure_name_var: 储过程的名称. 局部定义变量名,代表存
（2） sp_help：用于显示存储过程的一般信息（参数及其 数据类型） sp_help [[@objname=] name] 参数name为要查看的存储过程的名称。 例如： Use 学生课程数据库 go sp_help my_proc go
查看存储过程
（3）如果要知道某个表被存储过程引用的情况，则可以使 用sp_depends语句查看。 例如： sp_depends