管理存储过程

存储过程的概念

  将一些固定的操作集中起来由SQL服务器来完成。类似批处理
  存储过程是SQL语句和可选控制流程语句的预编译集合。是一种封装
  重复任务操作的方法。以一个名称存储，作为一个单元处理

  存储过程可以接受和输出参数，返回执行存储过程的状态值

分类：系统存储过程（SP_为前缀）和用户自定义存储过程

优点：存储过程封装事物规则
      允许标准组件式编程
       能够实现较快的执行速度
      能够减少网络流量--传送只是调用语句
       可以被作为一种安全机制来充分利用

创建存储过程的规则
     名字必须符合SQL server 命名规则
     引用对象必须在创建存储过程前就存在
     最多能有255个参数
     不能在单个存储过程中创建后渠道或在创建同名的对象
      可以引用临时表
     不能有Sql 创建语句
      文本不能超过64K字节  因为代码放在syscomments。 名字放在sysObject表中

使用企业管理器和Transaction-SQL管理存储过程

存储过程的3个组成部分
   --所有的输入参数以及传给调用者的输出参数
   --被执行的针对数据库的操作语句，包括调用其他存储过程的语句
   --返回调用者的状态值，以指明调用是否成功 

创建存储过程
create procedure procedure_name
@parameter data_type 
varying default output
with
recompile|encryption|recompile,encryption
for replication
as sql_statement
-----------------------------
if exists(select name from sysobjects where name='pinfo50000' and type='P')
drop procedure pinfo50000
GO
create procedure pinfo50000
As select *from project where 项目标的>=50000
order by 项目标的 desc
go
exec pinfo50000
go

执行存储过程
execute @return_status=
procedure_name
@patameter=value|@variable|output|default
with recompile    --指定实际存储过程时重新编译执行计划

Use company
if exists(select name from sysobjects where name='GetAvgPbliaodi'and type='P')
drop procedure GetAvgPbiaodi
go
查询某员工所负责项目的平均项目标的
create procedure GetAvgPbiaodi
@name varchar(10),@avgpbiaodi int output
as Declare @ErrorSave int
Set @ErrorSave=0
Select @avgpbiaodi=AVG(项目标的)
from prohect as p inner join pmanager as pm
on p.负责人ID=pm.负责人ID
where pm.姓名=@name
if(@@Error<>0)
  set @ErrorSave=@@Error
  return @ErrorSave
go

 

执行存储过程
Declare @returnvalue int,@avg int
exec @returnvalue=GetAvgPbiaodi'张杰'，@avg output
print'返回值='+cast(@returnvalue as char(2))
print '平均'+cast(@avg as char(10))

 

-------------------
查看存储过程的定义
sp_helptext procedureName

 

查看存储过程的相关性
exec sp_depends procedureName

 

重命名存储过程
EXEC sp_rename oldProcedureName,newProcedure

 

修改存储过程

alter procedure procedureName

删除存储过程
drop procedure procedureName

创建一组存储过程
Create prc group_sp;1
as select*from authors
go
Create prc group_sp;2
as select au_lname from authors
go
Create prc group_sp;3
as select distinct city from authors
go
exec group_sp;3

 

删除组
drop procedure dbo.group_sp

在存储过程中使用参数

按位置传入
create procedure scores
@score1 smallint,@score2 smallint,
@score3 smallint,@score4 smallint,
@score5 smallint,@myAvg smalint output
as select
@myAvg=(@score1+@score2+@score3+@score4+@score5)/5
执行
declare @AvgScore smallint
exec scores 10,5,6,4,6@AvgScore OutPut
select 'is:',@AvgScore
go

按引用转参数

return将信息返回给调用过程
create proc MyReturn
@t1 smallint,@t2 smallint,@retval smallint
as select @retval=@t1+@t2
return @retval
调用
declare @muReturnValue smallint
Exec @muReturnValue=myReturn 9,9,0
Select'this return value is',@muReturnValue

With Recompile
可以在Create Procedure 语句或EXEC procedure
语句后增加With recompile

在Create Procedure 后使用、将不被Catch每次运行重新编译