存儲過程

一 存儲過程的概念

存儲過程是在資料庫管理系統保存的，預先編譯的，能實現某種功能的SQL程式，它是資料庫應用中運用比較廣泛的一種數據對象。

為什麼需要存儲過程？

1.存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。
2.當對資料庫進行複雜操作時，可將此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。
3.存儲過程可以重覆使用,可減少資料庫開發人員的工作量。
4.安全性高,可設定只有某些用戶才具有對指定存儲過程的使用權

存儲過程的優點：

1.模塊化程式設計

2.執行速度塊，效率高

3.減少網路流量

4.具有良好的安全性

二 系統存儲過程

SQL_SERVER 提供系統存儲過程，它們是一組預編譯的T-SQL語句，系統存儲過程提供了管理資料庫和更新表的機制，並充當從系統表中檢索信息的快捷方式。

三 用戶自定義的存儲過程

1.創建不帶參數的存儲過程

Create proc usp_selectstu

As

Select StudentName,Gender,GradeId,Phone from dbo.Student

調用存儲過程：exec usp_selectstu

2.創建帶入參數的存儲過程

Create proc usp_stuInfo @gradeid int=2       （預設）

As

Select * from student where gradeId=@gradeid

調用存儲過程：exec usp_stuInfo 2

3.創建帶出參數的存儲過程

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

As

Select @gradeid=gradeid from student where  studentname=@name

print @gradeid

調用存儲過程：

declare @id int

exec usp_selectGrade '李小龍',@id output 

  4、 帶通配符參數存儲過程

Create proc usp_one  @name nvarchar(10)

as

select * from dbo.Student where StudentName like @name

exec usp_one '李%'

   5、 不緩存存儲過程

緩存就是數據交換的緩衝區(稱作Cache)，當某一硬體要讀取數據時，會首先從緩存中查找需要的數據，如果找到了則直接執行，找不到的話則從記憶體中找。由於緩存的運行速度比記憶體快得多，故緩存的作用就是幫助硬體更快地運行。

Sql Server系統記憶體管理在沒有配置記憶體最大值，很多時候我們會發現運行Sql Server的系統記憶體往往居高不下。這是由於他對於記憶體使用的策略是有多少閑置的記憶體就占用多少，直到記憶體使用慮達到系統峰值時（預留記憶體根據系統預設預留使用為準，至少4M），才會清除一些緩存釋放少量的記憶體為新的緩存騰出空間。

這些記憶體一般都是Sql Server運行時候用作緩存的，例如你運行一個select語句， 執行個存儲過程，調用函數；

1. 數據緩存：執行個查詢語句，Sql Server會將相關的數據頁（Sql Server操作的數據都是以頁為單位的）載入到記憶體中來，下一次如果再次請求此頁的數據的時候，就無需讀取磁碟了，大大提高了速度。

2.執行命令緩存：在執行存儲過程，自定函數時，Sql Server需要先二進位編譯再運行，編譯後的結果也會緩存起來， 再次調用時就無需再次編譯。

create proc proc_temp
with recompile
as
    select * from student


exec proc_temp

6，加密存儲過程

exec sp_helptext 儲存過程名      可以查看儲存過程代碼

create proc proc_temp_encryption

with encryption

as

    select * from student;

go

--存儲過程的內容不會被輕易看到（雖然解密也是有可能的）。

--應用這個，我們可以對某些關鍵的存儲過程進行加密。

--但此時，存儲過程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext 'proc_temp'

exec sp_helptext 'proc_temp_encryption'

（註意：加密存儲過程前應該備份原始存儲過程，且加密應該在部署到生產環境前完成。）