Sql Server 存儲過程執行個體講解

一、遇到的問題

        我們在之前學習的課程寫了不少的批處理語句，這些批處理語句存在兩個問題：

        1.沒法像函數那樣傳參數運作（自定義化）

        2.沒法像函數那樣可以反複地調用（功能化子產品化）

        說到這裡，我們可以猜到，資料庫中應該可以建立函數形式的資料庫對象來解決這樣的問題。但是介紹這種資料庫對象之前，我們再來看幾個需要解決的問題：

        我們已經學會把一條select語句封裝在視圖中，但是它隻能用來查詢，如果我們希望進行其它操作，比如增删改記錄、建删庫表等，是不能用視圖來完成的。

另一方面，如果把一個帶有更改操作的批處理整個存儲成資料庫對象，還可以把處理資料的程式移動到離資料盡可能近的地方，而不是總把這些操作寫在用戶端程式（如java、c#）中。通過将處理資料的程式從客戶應用程式移動到伺服器，可以降低網絡流量，并提高性能和資料的完整性。

二、存儲過程的概念

解決上面的問題，我們可以使用一種叫做“存儲過程”的資料庫對象。

存儲過程(stored procedure)把我們經常用到的一串複雜sql語句儲存成一個資料庫對象，并給它起一個名字。每次使用存儲過程隻需要使用如下的形式即可：

Ø  存儲過程并不神秘，它就是批處理。之前提到視圖是儲存在伺服器上的命名select語句，與之類似，存儲過程是儲存在伺服器上的命名批處理，系統将預先對它進行編譯。

Ø  存儲過程可以包含幾乎所有的t-sql語句，如資料存取語句、流程控制語句、錯誤處理語句等，使用起來彈性很大。

Ø  資料庫中也存在着系統函數和使用者定義函數這兩種對象，使用者定義函數的功能和存儲過程很像，但是有一定的差別。

【存儲過程的分類】

Ø  系統存儲過程 system stored procedure 字首sp_ 例如sp_help sp、helpdb

Ø  擴充存儲過程 extended stored procedure 字首xp_ 例如xp_cmdshell

Ø  使用者自定義存儲過程 user-defined stored procedure 也就是我們自己建立的

三、使用者自定義存儲過程的建立、修改、删除

【建立存儲過程】

【修改存儲過程】

可以看到，修改存儲過程的文法和建立的文法隻差一個單詞，把create換成alt即可。

【删除存儲過程】

        我們還可使用management studio來管理存儲過程，展開菜單樹中的“可程式設計性”，在“存儲過程”的子節點中可以進行各種操作。這裡要說一下建立：當點選“建立存儲過程”之後，會出現一個基于模闆的建立語句。這時點選菜單中的“查詢→指定模闆參數的值”，即可彈出對話框來對模闆進行設定，進而建立我們想要的存儲過程。另外，點選菜單中的“視圖→模闆資料總管”，可以看到sql server為我們提供的各種sql語句模闆。

【一個簡單的例子】

--插入一個以時間為使用者名的使用者

        選中exec這一行，然後按f5快速地反複執行，你會發現在同一秒内隻能插入一個使用者。

        這個存儲過程一旦建立就不能再次執行這段代碼了，可以把create改成alter來修改。

        注意存儲過程的代碼中不能go語句，因為go是用來送出批的，一旦遇到go系統會認為這個存儲過程的代碼已經書寫完畢，會送出create或者alter的批處理。如果希望在存儲過程中執行另一個批處理，請把該批處理寫成另一個存儲過程并調用。

四、使用者自定義存儲過程的參數傳遞和傳回值

【傳遞參數】

還記得我們前面做過的一個案例嗎？

現在我們把它寫成存儲過程。這樣我們每次都可以從一個指定表中提取我們想要的記錄了

調用的方法：

        存儲過程不使用exec也可以調用，但是不推薦這麼做。存儲過程參數的名字可以在調用時寫出來，但是這是完全沒必要的，是以@idvalue=10直接寫成10就可以了。

        exec加不加括号效果不一樣。加括号是執行sql語句，不加括号是執行存儲過程。

【傳回值】

1.以retrun傳回，始終是整數值

        return隻能傳回整數，即使不顯式寫出“return 整數值”這樣的語句，存儲過程也會自動傳回一個數值0表示成功。我們可以在發生錯誤時傳回非0值，表示有錯誤發生。不要試圖使用return傳回一個在存儲過程中處理的結果，比如姓名、生日之類的内容，因為它是整數，功能極為有限。我們隻用它傳回存儲過程執行的狀态就足夠了。請看例子：

2.以output參數傳回資料

        output可以用來傳回任何類型的資料，嚴格來說，它并不是一個“傳回值”，而是一個能夠被存儲過程調用代碼處看到的“外部變量”。這樣說的原因看下面的例子就明白了：

--通過id查詢藝人的姓名和年齡

        在調用處，我們先定義了兩個變量，然後我們以output的方式把兩個變量傳遞給了存儲過程。于是存儲過程就可以看到這兩個來自外部的變量了。那麼存儲過程中對這兩個變量的一切修改都可以立刻展現到調用處的代碼中，因為它修改的實際上就是調用處的兩個變量。

3.select語句的結果集

        如果在存儲過程中執行了select語句并顯示結果集（并不是使用select語句給變量指派），那麼這個結果集也可以看做是一種傳回值（不能被批處理語句用，但是作為結果集可以被c#等程式設計語言使用）。

        這種存儲過程可以用來實作“帶有參數的視圖”，在上面【傳遞參數】中舉出的例子就是。

【存儲過程的常用功能】

        從存儲過程參數和傳回值的用法我們可以看出，存儲過程通常用來處理一些對資料庫的更新操作、或者是按照特定的需要從資料庫中查詢資訊，并以變量的形式（而不是結果集的形式）傳回給調用處、或者是以結果集的形式傳回，但并不能被調用處的語句所使用。我們可以使用return的數值來監控存儲過程執行得是否順利。

五、存儲過程的嵌套調用

        在一個存儲過程中還可以執行另一個存儲過程，即嵌套調用。可以多次嵌套，但最多32層。可以用@@nestlevel來檢視嵌套目前層數：

六、系統存儲過程

        系統存儲過程是sql server系統建立的存儲過程，其作用是友善查詢系統資訊或完成系統管理任務。常用系統sp如下（更多内容查閱聯機叢書）

        sp_databases             列出伺服器上的所有資料庫（無參數）

        sp_server_info            列出伺服器資訊（可以有參數）

        sp_stored_procedures              列出目前環境中的所有存儲過程（可以有參數）

        sp_tables              傳回目前環境下可查詢的對象的清單（無參數）

        sp_configure              顯示或更改目前伺服器的全局配置設定

        sp_help         顯示有關資料庫對象的資訊（可以有參數）如sp_help yiren

        sp_helpdb          顯示有關資料庫的資訊（可以有參數）如sp_helpdb superstar

        sp_helptext       顯示規則、預設值、未加密的存儲過程、使用者定義函數、觸發器或視圖的文本（有參數）如sp_helptext proc_1

        sp_renamedb      重命名資料庫（有參數）如sp_renamedb 'pubs','出版社'

七、存儲過程的注意事項

【存儲過程的優點】

Ø  隻在建立時編譯，執行速度快效率高

Ø  減少網絡傳輸流量

Ø  提高安全性

Ø  子產品式程式設計，可以重複使用

Ø  統一每次的操作流程

【偷偷說一句】

        前面不管學什麼都要提一下缺點，但是存儲過程這裡卻沒提。這并不意味着存儲過程沒有任何缺點，畢竟任何東西都不能濫用，但是可以看出來，存儲過程的确是t-sql程式設計的核心内容，是最重要的部分。而它本身又是如此地容易掌握，相信你現在的心情不錯吧？

【在存儲過程中使用事務】

        存儲過程中是可以使用事務的，這毫無疑問。在存儲過程中使用事務不必用goto語句，在rollback或者commit語句之後直接return即可終止存儲過程的執行。前面我們也提到過：在普通批進行中實際上也是可以使用return語句的。

八、使用存儲過程實作分頁查詢

Ø  sp_executesql這個系統存儲過程是另一種執行sql語句的方法。它比exec(@sql)的功能進階一些。在這裡我們為它傳入3個參數，分别是等待處理的@sql（必須是nvarchar類型的）、使用字元串為@sql語句定義新的變量名、為字元串中定義的新變量指派（@rc是字元串中定義的新變量，把它賦為@rowcount，又因為希望通過@rc為@rowcount傳回值，是以指定為output）。

Ø  ceiling函數得到大于某小數的最小整數，如ceiling(3.5)會得到4。這裡把@rowcount和@pagesize相除，得到的數字是個小數。小數部分無法組成一個完整的分頁但是不代表沒有記錄，是以使用ceiling函數，讓@totalpages的值能夠正确指派。

Ø  當傳入的currentpage并不在正确的分頁編号範圍内時，我們的代碼做出了處理，給@currentpage賦了一個正确範圍内的數值。

調用上面的存儲過程：