管理儲存過程

儲存過程的概念

  將一些固定的操作集中起來由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每次執行重新編譯