SQL Server中如何寫預存程序

        一直都很想瞭解如何寫預存程序，對於不熟悉的東西，總是覺得很神秘，呵呵。特別是看著同學寫的那些預存程序，由於不瞭解預存程序的基本文法，看起來甚是吃力，很難理解。一直拖到今天，終於開啟了SQL Server的聯機叢書，瞭解了一些它的基本用法。以下是一些摘錄，希望自己以後再次看的時候，可以加深理解，更加好的掌握。

 

1、設計預存程序

幾乎任何可寫成批處理的 Transact-SQL 代碼都可用於建立預存程序。

2、預存程序的設計規則

預存程序的設計規則包括：

• CREATE PROCEDURE 定義本身可包括除下列 CREATE 語句以外的任何數量和類型的 SQL 陳述式，預存程序中的任意地方都不能使用下列語句：

  CREATE DEFAULT	CREATE TRIGGER
  CREATE PROCEDURE	CREATE VIEW
  CREATE RULE

• 可在預存程序中建立其它資料庫物件。可以引用在同一預存程序中建立的對象，前提是在建立對象後再引用對象。
• 可以在預存程序內引用暫存資料表。
• 如果在預存程序內建立本地暫存資料表，則該暫存資料表僅為該預存程序而存在；退出該預存程序後，暫存資料表即會消失。
• 如果執行調用其它預存程序的預存程序，那麼被調用預存程序可以訪問由第一個預存程序建立的、包括暫存資料表在內的所有對象。
• 如果執行在遠程 Microsoft SQL Server 2000 執行個體上變更的遠端預存程序，則不能復原這些更改。遠端預存程序不參與交易處理。
• 預存程序中參數的最大數目為 2100。
• 預存程序中局部變數的最大數目僅受可用記憶體的限制。
• 根據可用記憶體的不同，預存程序的最大大小可達 128 MB。

3、限定預存程序內的名稱

      在預存程序內部，如果用於諸如 SELECT 或 INSERT 這樣的語句的對象名沒有限定使用者，那麼使用者將預設為該預存程序的所有者。在預存程序內部，如果建立預存程序的使用者沒有限定 SELECT、INSERT、UPDATE 或 DELETE 語句中引用的表名，那麼通過該預存程序對這些表進行的訪問將預設地受到該過程的建立者許可權的限制。

        如果有其他使用者要使用預存程序，則用於語句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的對象名必須用該對象所有者的名稱限定。例如，Mary 擁有表 marytab，如果她希望其他使用者能夠執行使用該表的預存程序，必須在該表用於上述某一條語句時對其表名進行限定。

        此規則是必需的，因為運行預存程序時將解析對象的名稱。如果未限定 marytab，而 John 試圖執行該過程，SQL Server 將尋找 John 所擁有的名為 marytab 的表。

4、加密流程定義

        如果要建立預存程序，並且希望確保其他使用者無法查看該過程的定義，那麼可以使用 WITH ENCRYPTION 子句。這樣，流程定義將以不可讀的形式儲存。

        預存程序一旦加密其定義即無法解密，任何人（包括預存程序的所有者或系統管理員）都將無法查看預存程序定義。

5、SET 語句選項

當 ODBC 應用程式與 SQL Server 串連時，伺服器將自動化佈建會話的下列選項：

• SET QUOTED_IDENTIFIER ON
• SET TEXTSIZE 2147483647
• SET ANSI_DEFAULTS ON
• SET CURSOR_CLOSE_ON_COMMIT OFF
• SET IMPLICIT_TRANSACTIONS OFF

        這些設定將提高 ODBC 應用程式的可移植性。由於基於 DB-Library 的應用程式通常不設定這些選項，所以應在上述所列 SET 選項開啟和關閉的情況下都對預存程序進行測試。這樣可確儲存儲過程始終能正確工作，而不管特定的串連在喚醒調用該預存程序時可能設定的選項。需要特別設定其中一個選項的預存程序，應在開始該預存程序時發出一條 SET 語句。此 SET 語句將只對該預存程序的執行保持有效，當該預存程序結束時，將恢復原設定。

樣本

A. 建立使用參數的預存程序

下例建立一個在 pubs 資料庫中很有用的預存程序。給出一個作者的姓和名，該預存程序將顯示該作者的每本書的標題和出版商。

CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)ASSELECT au_lname, au_fname, title, pub_nameFROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_idJOIN titles ON titleauthor.title_id = titles.title_idJOIN publishers ON titles.pub_id = publishers.pub_idWHERE au_fname = @firstnameAND au_lname = @lastnameGO

將出現一條說明該命令未返回任何資料也未返回任何行的訊息，這表示已建立該預存程序。

現在執行 au_info 預存程序：

EXECUTE au_info Ringer, AnneGO

下面是結果集：

au_lname	au_fname	title	pub_name
---------	---------	---------------------	----------------
Ringer	Anne	The Gourmet Microwave	Binnet & Hardley
Ringer	Anne	Is Anger the Enemy?	New Moon Books

(2 row(s) affected)

B. 建立使用參數預設值的預存程序

下例建立一個預存程序 pub_info2，該預存程序顯示作為參數給出的出版商所出版的某本書的作者姓名。如果未提供出版商的名稱，該預存程序將顯示由 Algodata Infosystems 出版的書籍的作者。

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'ASSELECT au_lname, au_fname, pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_idJOIN titles t ON ta.title_id = t.title_idJOIN publishers p ON t.pub_id = p.pub_idWHERE @pubname = p.pub_name

執行未指定參數的 pub_info2：

EXECUTE pub_info2GO

下面是結果集：

au_lname	au_fname	pub_name
----------------	----------------	--------------------
Green	Marjorie	Algodata Infosystems
Bennet	Abraham	Algodata Infosystems
O'Leary	Michael	Algodata Infosystems
MacFeather	Stearns	Algodata Infosystems
Straight	Dean	Algodata Infosystems
Carson	Cheryl	Algodata Infosystems
Dull	Ann	Algodata Infosystems
Hunter	Sheryl	Algodata Infosystems
Locksley	Charlene	Algodata Infosystems

(9 row(s) affected)

C. 執行用顯式值替代參數預設值的預存程序

在下例中，預存程序 showind2 的 @table 參數預設值是 titles。

CREATE PROC showind2 @table varchar(30) = 'titles'ASSELECT TABLE_NAME = sysobjects.name,INDEX_NAME = sysindexes.name, INDEX_ID = indidFROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.idWHERE sysobjects.name = @table

欄位標題（例如，TABLE_NAME）可使結果更具可讀性。下面是該預存程序顯示的 authors 表的情況：

EXECUTE showind2 authorsGO

TABLE_NAME	INDEX_NAME	INDEX_ID
----------	----------	----------
authors	UPKCL_auidind	1
authors	aunmind	2

(2 row(s) affected)

如果使用者未提供值，則 SQL Server 將使用預設表 titles：

EXECUTE showind2GO

下面是結果集：

TABLE_NAME	INDEX_NAME	INDEX_ID
----------	----------	----------
titles	UPKCL_titleidind	1
titles	titleind	2

(2 row(s) affected)

D. 使用參數預設值 NULL 建立預存程序

參數預設值可以是 NULL 值。在這種情況下，如果未提供參數，則 SQL Server 將根據預存程序的其它語句執行預存程序。不會顯示錯誤資訊。

流程定義還可指定當不給出參數時要採取的其它某種措施。例如：

CREATE PROC showind3 @table varchar(30) = NULLAS IF @table IS NULLPRINT 'Give a table name'ELSESELECT TABLE_NAME = sysobjects.name,INDEX_NAME = sysindexes.name, INDEX_ID = indidFROM sysindexes INNER JOIN sysobjectsON sysobjects.id = sysindexes.idWHERE sysobjects.name = @table

E. 使用包含萬用字元的參數預設值建立預存程序

如果預存程序將參數用於 LIKE 關鍵字，那麼預設值可包括萬用字元（%、_、[] 和 [^]）。例如，可將 showind 修改為當不提供參數時顯示有關係統表的資訊：

CREATE PROC showind4 @table varchar(30) = 'sys%'AS SELECT TABLE_NAME = sysobjects.name,INDEX_NAME = sysindexes.name, INDEX_ID = indidFROM sysindexes INNER JOIN sysobjectsON sysobjects.id = sysindexes.idWHERE sysobjects.name LIKE @table

在預存程序 au_info 的下列變化形式中，兩個參數都有帶萬用字元的預設值：

CREATE PROC au_info2 @lastname varchar(30) = 'D%',@firstname varchar(18) = '%'ASSELECT au_lname, au_fname, title, pub_nameFROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_idJOIN titles ON titleauthor.title_id = titles.title_idJOIN publishers ON titles.pub_id = publishers.pub_idWHERE au_fname LIKE @firstnameAND au_lname LIKE @lastname

如果執行 au_info2 時不指定參數，將顯示姓以字母 D 開頭的所有作者：

EXECUTE au_info2GO

下面是結果集：

au_lname	au_fname	title	pub_name
--------	--------	---------------------	-------------------
Dull	Ann	Secrets of Silicon Val	Algodata Infosystems
del Castillo	Innes	Silicon Val Gastrono	Binnet & Hardley
DeFrance	Michel	The Gourmet Microwave	Binnet & Hardley

(3 row(s) affected)

下例在兩個參數的預設值已定義的情況下，省略了第二個參數，因此可找到姓為 Ringer 的所有作者的書和出版商：

EXECUTE au_info2 RingerGO

au_lname	au_fname	title	pub_name
---------	---------	----------------------	----------------
Ringer	Anne	The Gourmet Microwave	Binnet & Hardley
Ringer	Anne	Is Anger the Enemy?	New Moon Books
Ringer	Albert	Is Anger the Enemy?	New Moon Books
Ringer	Albert	Life Without Fear	New Moon Books

(4 row(s) affected)