第十二章 管理存储过程
存储过程与函数不同,因为存储过程并不返回取代其名称的值,也不能直接在表达式中使用
存储过程的优点
1)可用存储过程封装事务规则.一旦封装完成,这些规则就可用于多个应用,从而有一个一致的数据接口,因此,若需改变过程的功能,只需在一个地方对其进行修改,而不必对每个应用都进行修改.
2)存储过程允许标准组件式编程: 存储过程在被创建以后,可以再程序中被多次调用,而不必重新编写该存储过程的sql语句;而且数据库专业人员可随时对存储过程进行修改,但对应用程序源代码毫无影响,因为应用程序源代码只包含存储过程的调用语句,从而极大地提高了程序的可移植性.
3)存储过程能够实现较快的执行速度: 如果某一操作包含大量的t-dql代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多.因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析优化,并给出最终被存在系统表中的执行计划;而批处理的t-sql语句在每次运行时都要进行编译和优化,因此速度相对要慢一些
4)存储过程能够减少网络流量: 对于同一个针对数据数据库对象的操作(如查询修改),如果这一操作所涉及到的t-sql语句被组织成一存储过程,那么当在客户计算机调用该存储过程时,网络中传送的只是该调用语句;否则将是多条sql语句,从而大大增加了网络流量,降低网络负载.
5)存储过程作为一种安全机制来充分利用: 系统管理员通过对执行某一存储过程的权限进行限制,从而能够实现对相应的数据访问权限的限制,避免非授权用户对数据的访问,保证数据的安全性.
创建存储过程的规则
1)几乎任何可以写成批处理的t-sql代码都可用于创建存储过程,但是在设计存储过程时,需要遵循下列规则
2)名字必须符合sql server命名规则
3)引用的对象必须在创建存储过程前就存在
4)不能在单个存储过程中创建后去掉或在创建同名的对象
5)存储过程最多能有255个参数
6)在自己的存储过程中可以引用临时表,局部临时表在过程结束时将会消失
7)在存储过程中不能有如下的sql创建语句 create default create procedure create rule create trigger create view
8)可在过程中嵌套过程
9)创建存储过程的文本不能超过64k字节,以为sql存放在syscomments表中
10)若在存储过程中使用了select * ,而底层表中加入了新的列,新的列在过程运行时无法显示.
创建存储过程 例子:
在数据库company中创建一个存储过程,用于返回项目表的不小于5000的项目情况,并按项目表的降序进行排列
use company
if exists(select name from sysobjects where name='pinfo50000' and type='p')
drop procedure pinfo50000
go
create procedure pinfo50000 as select * from project where 项目表的>=5000 order by 项目表的 desc
go
exec pinfo50000
go
查看存储过程的定义
例: exec sp_helptext GetAvgPbiaodi
查看存储过程的所有者信息
例: exec sp_help GetAvgPbiaodi
查看存储过程的相关性
例 exec sp_depends GetAvgPbiaodi
重命名存储过程
exec sp_rename GetAvgPbiaodi ,'项目平均标的过程'
修改存储过程: 但是不会更改权限,也不影响相关的存储过程或触发器
删除存储过程:drop procedure pinfo50000
可以选择是否制定过程所有者名称,但不能制定服务器名称和数据库名称
不能除去组内的个别过程,必须除去整个过程组;如果该过程组在其它应用程序或存储过程中被调用,系统会显示出错信息
创建一组过程
create Proc group_sp ; 1
as select * from authors
go
create Proc group_sp ;2
as select au_lname from authors
go
create Proc group_sp ;3
as select distinct city from authors
go
这个批处理语句将创建一个称为group_sp的单一过程,它包括3个不同过程作为它的一部分.要引用单个过程,只要执行他们并带有;number作为名字的一部分
再删除过程组时,只要删除过程的名字,所用组的部件也将被删掉:
例 drop procedure dbo.group_sp
注意:无法删除组中的某一过程
