Sqlserver存儲過程深入分析(非常詳細)

存儲過程在t-sql中用到非常的多特別是安全性及大負載時都會到存儲過程了，下面文章來為各位介紹一篇非常詳細的關於Sqlserver存儲過程介紹。

前言

　雖說現在orm，nosql對數據的操作會減少很多t-sql的編程，但是t-sql這應該是每個程序員要掌握的，接下來的兩周准備把這塊知識點進行系統總結。

1.什麼是存儲過程

Transact-SQL中的存儲過程，非常類似於net語言中的方法，它可以重復調用。當存儲過程執行一次後，可以將語句緩存中，這樣下次執行的時候直接使用緩存中的語句。這樣

就可以提高存儲過程的性能。

 1. 存儲過程Procedure是一組為了完成特定功能的SQL語句集合，經編譯後存儲在數據庫中，用戶通過指定存儲過程的名稱並給出參數來執行。

 2. 存儲過程中可以包含邏輯控制語句和數據操縱語句，它可以接受參數、輸出參數、返回單個或多個結果集以及返回值。

 3. 由於存儲過程在創建時即在數據庫服務器上進行了編譯並存儲在數據庫中，所以存儲過程運行要比單個的SQL語句塊要快。同時由於在調用時只需用提供存儲過程名和必要的參數信息，所以在一定程度上也可以減少網絡流量、簡單網絡負擔。

2.存儲過程的優點

2.1 存儲過程允許標准組件式編程

        存儲過程創建後可以在程序中被多次調用執行，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，但對應用程序源代碼卻毫無影響，從而極大的提高了程序的可移植性。

2.2存儲過程能夠實現較快的執行速度

        如果某一操作包含大量的T-SQL語句代碼，分別被多次執行，那麼存儲過程要比批處理的執行速度快得多。因為存儲過程是預編譯的，在首次運行一個存儲過程 時，查詢優化器對其進行分析、優化，並給出最終被存在系統表中的存儲計劃。而批處理的T-SQL語句每次運行都需要預編譯和優化，所以速度就要慢一些。

2.3 存儲過程減輕網絡流量

        對於同一個針對數據庫對象的操作，如果這一操作所涉及到的T-SQL語句被組織成一存儲過程，那麼當在客戶機上調用該存儲過程時，網絡中傳遞的只是該調用語句，否則將會是多條SQL語句。從而減輕了網絡流量，降低了網絡負載。

2.4存儲過程可被作為一種安全機制來充分利用

        系統管理員可以對執行的某一個存儲過程進行權限限制，從而能夠實現對某些數據訪問的限制，避免非授權用戶對數據的訪問，保證數據的安全。

3.存儲過程的缺點

3.1.   運行速度

　　所以對於很簡單的sql，存儲過程沒有什麼優勢。   

3.2.  開發調試

　　存儲過程的開發調試要比一般程序困難（老版本DB2還只能用C寫存儲過程，更是一個災難）。   

3.3. 移植性：

　　這是我不選擇存儲過程的主要原因。反正一般的應用總是綁定某個數據庫的，不然就無法靠優化數據庫訪問來提高性能了。   

4.存儲過程

系統存儲過程是系統創建的存儲過程，目的在於能夠方便的從系統表中查詢信息或完成與更新數據庫表相關的管理任務或其他的系統管理任務。系統存儲過程主要存 儲在master數據庫中，以“sp”下劃線開頭的存儲過程。盡管這些系統存儲過程在master數據庫中，但我們在其他數據庫還是可以調用系統存儲過 程。有一些系統存儲過程會在創建新的數據庫的時候被自動創建在當前數據庫中。

 View Code
4.1.系統存儲過程示例

--表重命名
exec sp_rename 'stu', 'stud';--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查詢所有存儲過程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

4.2.自定義存儲過程

   4.2.1. 創建語法

create proc | procedure pro_name
[{@參數數據類型} [=默認值] [output],
{@參數數據類型} [=默認值] [output],
....
]
as
SQL_statements

   4.2.2.創建不帶參數存儲過程

--創建存儲過程

if (object_id('proc_get_student', 'P') is not null)--判斷存儲過程是否存在 另外一種  if (exists (select * from sys.objects where name = 'proc_get_student'))//
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;

--調用、執行存儲過程
exec proc_get_student;
  4.2.3. 修改存儲過程

--修改存儲過程
alter proc proc_get_student
as
select * from student;
  4.2.4. 帶參存儲過程

--帶參存儲過程

if (object_id('proc_find_stu', 'P') is not null)--判斷存儲過程是否存在
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

  4.2.5.帶通配符參數存儲過程

--帶通配符參數存儲過程

if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

  4.2.6. 帶輸出參數存儲過程

if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默認輸入參數
@name varchar(20) out, --輸出參數
@age varchar(20) output--輸入輸出參數
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go

--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

   4.2.7.不緩存存儲過程

--WITH RECOMPILE 不緩存
 
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go

exec proc_temp;

   4.2.8. 加密存儲過程

--加密WITH ENCRYPTION

if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

  4.2.9. 帶游標參數存儲過程

 View Code
  4.2.9.分頁存儲過程

---存儲過程、row_number完成分頁

if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分頁存儲過程
if (object_id('pro_page', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

5.Raiserror

Raiserror返回用戶定義的錯誤信息，可以指定嚴重級別，設置系統變量記錄所發生的錯誤。

   語法如下：

Raiserror({msg_id | msg_str | @local_variable}
{, severity, state}
[,argument[,…n]]
[with option[,…n]]
)
   # msg_id:在sysmessages系統表中指定的用戶定義錯誤信息

   # msg_str:用戶定義的信息，信息最大長度在2047個字符。

   # severity：用戶定義與該消息關聯的嚴重級別。當使用msg_id引發使用sp_addmessage創建的用戶定義消息時，raiserror上指定嚴重性將覆蓋sp_addmessage中定義的嚴重性。

    任何用戶可以指定0-18直接的嚴重級別。只有sysadmin固定服務器角色常用或具有alter trace權限的用戶才能指定19-25直接的嚴重級別。19-25之間的安全級別需要使用with log選項。

   # state：介於1至127直接的任何整數。State默認值是1。

raiserror('is error', 16, 1);
select * from sys.messages;
--使用sysmessages中定義的消息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);
6.SqlServer中的存儲過程有兩種返回參數的方式

SqlServer中的存儲過程有兩種返回參數的方式

1. Return
2. OutPut
第一種只能返回INT類型的數據，而第二種則可以是任意的（即Object類型）
今天用的方法是第二種方式取返回值，首先看一個簡單的存儲過程

CREATE Procedure [dbo].[pro_ty_getFlowByFbillID]
@FID INT OUTPUT--定義output變量，輸出變量
as
SET NOCOUNT ON

select @FID = Fite--進行賦值
from [dbo].[t_bm_exp]
where FbillID = 1
SET NOCOUNT OFF
存儲過程中已經定義了輸出變量了，下面之下net調用取值即可。
SqlParameter sp =
{--定義參數
new SqlParameter("@fid", SqlDbType.INT, 4)
};
--獲得參數
sp[0].Direction = ParameterDirection.Output;
/*net調用存儲過程*/
--略
return Convert.ToInt32 （sp[0] .value.ToString());

完成。

7.以前工作中寫的存儲過程【請輕噴，很久前寫的啦】

7.1工作流待辦事項分頁

-- =============================================
-- Author:        wangyanling   
-- Create date: 2014-06-03
-- Description:    工作流待辦事項分頁
-- =============================================
ALTER PROCEDURE [dbo].[PageWorkFlowToDo]
    @pageindex int,
    @pagesize int,
    @uid varchar(200),
    @strWhere varchar(500)
AS
BEGIN
    declare @sql varchar(4000)
    Set @sql=' select top '+CAST(@pagesize as varchar)+' db_userwork.uwid,db_userwork.uid,db_userwork.Title,db_userwork.IniUid,u_user.UName as IniName,
                      convert(varchar(100),db_userwork.IniStartTime,20) as IniStartTime,convert(varchar(100),
                      db_userwork.endTime,20) as endTime,db_userwork.State,
                      S_FInfo.ID as FormID,db_userwork.ImpLevel,db_userwork.DueTime,db_userwork.wsuid,
                      db_userwork.NotifyType,db_userwork.IfTrans,db_userwork.ID, 
                      case  S_FInfo.FrmType when ''1'' then ''WorkFlowForm.aspx''
                      when ''2'' then ''DeWorkFlowForm.aspx''
                      when ''3'' then ''AddTeamWork.aspx''
                      else S_FInfo.FrmHtml end as FrmHtml,db_Work_Case.ExeStep,db_userwork.IfUpdate,db_userwork.FID,
                      db_userwork.NodeID,ExtParam from db_userwork
                      left join S_FInfo on db_userwork.wsuid=S_FInfo.WID
                      left join db_Work_Case on db_userwork.ID=db_Work_Case.ID
                      left join u_user on db_userwork.IniUid=u_user.UId 
                      where db_userwork.uid='+@uid+' and db_userwork.State=1 and db_userwork.uwid not in (
                      select top '+CAST(((@pageindex-1)*@pagesize) as varchar)+'db_userwork.uwid from  db_userwork
                      left join S_FInfo on db_userwork.wsuid=S_FInfo.WID
                      left join db_Work_Case on db_userwork.ID=db_Work_Case.ID
                      left join u_user on db_userwork.IniUid=u_user.UId 
                      where db_userwork.uid='+@uid+' and db_userwork.State=1 and 1=1 '+@strWhere+' order by IniStartTime desc
                      ) and 1=1 '
  if(@strWhere<>'')
  begin
    Set @sql=@sql+@strWhere+' order by IniStartTime desc';
  end
  else
  begin
    Set @sql=@sql+' order by IniStartTime desc';
  end
  exec(@sql)
END

 

7.2.通過部門名稱獲取對應組號

</pre>游標：<pre code_snippet_id="570631" snippet_file_name="blog_20150103_3_3319497" name="code" class="sql">-- =============================================
-- Author:    wangyanling   
-- Create date: 2013-05-23
-- Description:    通過部門名稱獲取對應組號
-- =============================================
ALTER PROCEDURE [dbo].[GetGroupIDByOnLine]
    -- Add the parameters for the stored procedure here
    @GName varchar(200)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    Create table #temp
    (
       GroupID int
    )
    Create table #tempTwoTable
    (
       GroupID int
    )
    insert into #temp select GId from g_group where PId =
    (
       select GId from g_group where GName=@GName
    )
    declare @count int
    select @count=COUNT(*) from #temp
    if(@count>0)
    begin
       declare @GroupID int
       declare cur cursor for  select GroupID from #temp
       open cur
       fetch next from cur into @GroupID
                 while @@FETCH_STATUS=0  --判斷是否成功獲取數據
                 begin
                 insert into #tempTwoTable select GId from g_group where PId =@GroupID
                 insert into #tempTwoTable select @GroupID
                 fetch next from cur into @GroupID
                 end
                 close cur --關閉游標
    deallocate cur
    insert into #tempTwoTable  select GId from g_group where GName=@GName
    end
    else
    begin
       insert into #tempTwoTable  select GId from g_group where GName=@GName
    end
    ----
    Declare @GroupIDs varchar(100)
    Declare @DouHao nvarchar(max)
    Set @DouHao=''
    Declare curDouHao cursor for
          select GroupID from #tempTwoTable
    open curDouHao
     fetch next from curDouHao into @GroupIDs
                 while @@FETCH_STATUS=0  --判斷是否成功獲取數據
                 begin
                 Set @DouHao=@DouHao+Convert(varchar(20),@GroupIDs)+','
                 fetch next from curDouHao into @GroupIDs
                 end
                  close curDouHao --關閉游標
    deallocate curDouHao
    select SUBSTRING(@DouHao,1,LEN(@DouHao)-1)
    drop table #tempTwoTable
    drop table #temp
END

7.3.存儲過程作業操作

ALTER PROCEDURE [dbo].[add_job] @portal_dbName varchar(200)
AS
BEGIN
    DECLARE @jobid uniqueidentifier, @jobname sysname,@sysdatetime varchar(500)
    SET select @sysdatetime = convert(varchar(23),Datename(yyyy,getdate()))+convert(varchar(23),Datename(mm,getdate()))+convert(varchar(23),Datename(dd,getdate()))+Datename(hh,getdate())+Datename(n,getdate())+Datename(ss,getdate())
    SET @jobname = N'portal_safe_job'+@sysdatetime

    IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname)
    EXEC msdb.dbo.sp_delete_job @job_name=@jobname

    EXEC msdb.dbo.sp_add_job
    @job_name = @jobname,
    @job_id = @jobid OUTPUT
    --定義作業步驟
    DECLARE @sql nvarchar(4000),@dbname sysname
    select @dbname=@portal_dbName, --作業步驟在當前數據庫中執行 重要(根據不同的表來替換表名)
    @sql=N'delete from db_userOnline where hartTime< dateadd(minute ,-2,getdate()) or logined=1' --一般定義的是使用TSQL處理的作業,這裡定義要執行的Transact-SQL語句
    EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobid,
    @step_name = N'step1',
    @subsystem = 'TSQL', --步驟的類型,一般為TSQL
    @database_name=@dbname,
    @command = @sql

    --創建調度(使用後面專門定義的幾種作業調度模板)
    EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid,@name = N'調度名稱',
    @freq_type=4,                --每天
    @freq_interval=1,            --指定每多少天發生一次,這裡是1天.
    @freq_subday_type=0x4,       --重復方式,0x1=在指定的時間,0x4=多少分鐘,0x8=多少小時執行一次
    @freq_subday_interval=1,     --重復周期數,這裡每小時執行一次
    @active_start_date = NULL,   --作業執行的開始日期,為NULL時表示當前日期,格式為YYYYMMDD
    @active_end_date = 99991231,
    @active_start_time = 00000,@active_end_time = 235959

    -- 添加目標服務器
      EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
END