第7章 存储过程

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