2.SQL存儲過程創建
創建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數的 Transact-SQL 語句的集合。
可以創建一個過程供永久使用，或在一個會話中臨時使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。
也可以創建在 Microsoft? SQL Server? 啟動時自動運行的存儲過程。
語法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
參數
procedure_name
新存儲過程的名稱。過程名必須符合標識符規則，且對于數據庫及其所有者必須唯一。
要創建局部臨時過程，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要創建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。
;number
是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前后使用適當的定界符。
@parameter
過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2.100 個參數。
使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。
data_type
參數的數據類型。所有數據類型（包括 text、ntext 和 image）均可以用作存儲過程的參數。不過，cursor 數據類型只能用于 OUTPUT 參數。如果指定的數據類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。
說明 對于可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。
VARYING
指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用于游標參數。
default
參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。
OUTPUT
表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將信息返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是游標占位符。
n
表示最多可以指定 2.100 個參數的占位符。
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計劃時，請使用 RECOMPILE 選項。
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發布。
說明 在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創建加密過程。
FOR REPLICATION
指定不能在訂閱服務器上執行為復制創建的存儲過程。.使用 FOR REPLICATION 選項創建的存儲過程可用作存儲過程篩選，且只能在復制過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。
AS
指定過程要執行的操作。
sql_statement
過程中要包含的任意數目和類型的 Transact-SQL 語句。但有一些限制。
n
是表示此過程可以包含多條 Transact-SQL 語句的占位符。
注釋
存儲過程的最大大小為 128 MB。
|
用戶定義的存儲過程只能在當前數據庫中創建（臨時過程除外，臨時過程總是在 tempdb 中創建）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 語句組合使用。
默認情況下，參數可為空。如果傳遞 NULL 參數值并且該參數在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產生一條錯誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數值，應向過程中添加編程邏輯或為該列使用默認值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關鍵字)。
建議在存儲過程的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在創建臨時表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個連接執行的存儲過程對這些選項的設置與創建該過程的連接的設置不同，則為第二個連接創建的表列可能會有不同的為空性，并且表現出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執行該存儲過程的連接使用相同的為空性創建臨時表。
在創建或更改存儲過程時，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設置。執行存儲過程時，將使用這些原始設置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設置在執行存儲過程時都將被忽略。在存儲過程中出現的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響存儲過程的功能。
其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創建或更改存儲過程時不保存。如果存儲過程的邏輯取決于特定的設置，應在過程開頭添加一條 SET 語句，以確保設置正確。從存儲過程中執行 SET 語句時，該設置只在存儲過程完成之前有效。之后，設置將恢復為調用存儲過程時的值。這使個別的客戶端可以設置所需的選項，而不會影響存儲過程的邏輯。
天涯風云 19:30:43
說明 SQL Server 是將空字符串解釋為單個空格還是解釋為真正的空字符串，由兼容級別設置控制。如果兼容級別小于或等于 65，SQL Server 就將空字符串解釋為單個空格。如果兼容級別等于 70，則 SQL Server 將空字符串解釋為空字符串。
獲得有關存儲過程的信息
若要顯示用來創建過程的文本，請在過程所在的數據庫中執行 sp_helptext，并使用過程名作為參數。
說明 使用 ENCRYPTION 選項創建的存儲過程不能使用 sp_helptext 查看。
若要顯示有關過程引用的對象的報表，請使用 sp_depends。
若要為過程重命名，請使用 sp_rename。
|
|
引用對象
SQL Server 允許創建的存儲過程引用尚不存在的對象。在創建時，只進行語法檢查。執行時，如果高速緩存中尚無有效的計劃，則編譯存儲過程以生成執行計劃。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的對象，則仍可以成功創建，但在運行時將失敗，因為所引用的對象不存在。
延遲名稱解析和兼容級別
SQL Server 允許 Transact-SQL 存儲過程在創建時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 存儲過程引用了該存儲過程中定義的表，而兼容級別設置（通過執行 sp_dbcmptlevel 來設置）為 65，則在創建時會發出警告信息。而如果在運行時所引用的表不存在，將返回錯誤信息。
執行存儲過程
成功執行 CREATE PROCEDURE 語句后，過程名稱將存儲在 sysobjects 系統表中，而 CREATE PROCEDURE 語句的文本將存儲在 syscomments 中。第一次執行時，將編譯該過程以確定檢索數據的最佳訪問計劃。
使用 cursor 數據類型的參數
存儲過程只能將 cursor 數據類型用于 OUTPUT 參數。如果為某個參數指定了 cursor 數據類型，也必須指定 VARYING 和 OUTPUT 參數。如果為某個參數指定了 VARYING 關鍵字，則數據類型必須是 cursor，并且必須指定 OUTPUT 關鍵字。
說明 cursor 數據類型不能通過數據庫 API（例如 OLE DB、ODBC、ADO 和 DB-Library）綁定到應用程序變量上。因為必須先綁定 OUTPUT 參數，應用程序才可以執行存儲過程，所以帶有 cursor OUTPUT 參數的存儲過程不能通過數據庫 API 調用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時，才可以通過 Transact-SQL 批處理、存儲過程或觸發器調用這些過程。
Cursor 輸出參數
在執行過程時，以下規則適用于 cursor 輸出參數：
對于只進游標，游標的結果集中返回的行只是那些存儲過程執行結束時處于或超出游標位置的行，例如：
在過程中的名為 RS 的 100 行結果集上打開一個非滾動游標。
過程提取結果集 RS 的頭 5 行。
過程返回到其調用者。
返回到調用者的結果集 RS 由 RS 的第 6 到 100 行組成，調用者中的游標處于 RS 的第一行之前。
對于只進游標，如果存儲過程完成后，游標位于第一行的前面，則整個結果集將返回給調用批處理、存儲過程或觸發器。返回時，游標將位于第一行的前面。
對于只進游標，如果存儲過程完成后，游標的位置超出最后一行的結尾，則為調用批處理、存儲過程或觸發器返回空結果集。
說明 空結果集與空值不同。
對于可滾動游標，在存儲過程執行結束時，結果集中的所有行均會返回給調用批處理、存儲過程或觸發器。返回時，游標保留在過程中最后一次執行提取時的位置。
對于任意類型的游標，如果游標關閉，則將空值傳遞回調用批處理、存儲過程或觸發器。如果將游標指派給一個參數，但該游標從未打開過，也會出現這種情況。
說明 關閉狀態只有在返回時才有影響。例如，可以在過程中關閉游標，稍后再打開游標，然后將該游標的結果集返回給調用批處理、存儲過程或觸發器。
臨時存儲過程
SQL Server 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只能由創建該過程的連接使用。全局臨時過程則可由所有連接使用。局部臨時過程在當前會話結束時自動除去。全局臨時過程在使用該過程的最后一個會話結束時除去。通常是在創建該過程的會話結束時。
臨時過程用 # 和 ## 命名，可以由任何用戶創建。創建過程后，局部過程的所有者是唯一可以使用該過程的用戶。執行局部臨時過程的權限不能授予其他用戶。如果創建了全局臨時過程，則所有用戶均可以訪問該過程，權限不能顯式廢除。只有在 tempdb 數據庫中具有顯式 CREATE PROCEDURE 權限的用戶，才可以在該數據庫中顯式創建臨時過程（不使用編號符命名）。可以授予或廢除這些過程中的權限。
說明 頻繁使用臨時存儲過程會在 tempdb 中的系統表上產生爭用，從而對性能產生負面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統表中存儲數據，因此可以避免這一問題。
自動執行存儲過程
SQL Server 啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員創建，并在 sysadmin 固定服務器角色下作為后臺過程執行。這些過程不能有任何輸入參數。
對啟動過程的數目沒有限制，但是要注意，每個啟動過程在執行時都會占用一個連接。如果必須在啟動時執行多個過程，但不需要并行執行，則可以指定一個過程作為啟動過程，讓該過程調用其它過程。這樣就只占用一個連接。
在啟動時恢復了最后一個數據庫后，即開始執行存儲過程。若要跳過這些存儲過程的執行，請將啟動參數指定為跟蹤標記 4022。如果以最低配置啟動 SQL Server（使用 -f 標記），則啟動存儲過程也不會執行。
若要創建啟動存儲過程，必須作為 sysadmin 固定服務器角色的成員登錄，并在 master 數據庫中創建存儲過程。
使用 sp_procoption 可以：
將現有存儲過程指定為啟動過程。
停止在 SQL Server 啟動時執行過程。
查看 SQL Server 啟動時執行的所有過程的列表。
存儲過程嵌套
存儲過程可以嵌套，即一個存儲過程可以調用另一個存儲過程。在被調用過程開始執行時，嵌套級將增加，在被調用過程執行結束后，嵌套級將減少。如果超出最大的嵌套級，會使整個調用過程鏈失敗。可用 @@NESTLEVEL 函數返回當前的嵌套級。
若要估計編譯后的存儲過程大小，請使用下列性能監視計數器。
* 各種分類的高速緩存對象均可以使用這些計數器，包括特殊 sql、準備 sql、過程、觸發器等。
sql_statement 限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（這兩個語句必須是批處理中僅有的語句），任何 SET 語句均可以在存儲過程內部指定。所選擇的 SET 選項在存儲過程執行過程中有效，之后恢復為原來的設置。
如果其他用戶要使用某個存儲過程，那么在該存儲過程內部，一些語句使用的對象名必須使用對象所有者的名稱限定。這些語句包括：
ALTER TABLE
CREATE INDEX
CREATE TABLE
所有 DBCC 語句
DROP TABLE
DROP INDEX
TRUNCATE TABLE
UPDATE STATISTICS
權限
CREATE PROCEDURE 的權限默認授予 sysadmin 固定服務器角色成員和 db_owner 和 db_ddladmin 固定數據庫角色成員。sysadmin 固定服務器角色成員和 db_owner 固定數據庫角色成員可以將 CREATE PROCEDURE 權限轉讓給其他用戶。執行存儲過程的權限授予過程的所有者，該所有者可以為其它數據庫用戶設置執行權限。
示例
A. 使用帶有復雜 SELECT 語句的簡單過程
下面的存儲過程從四個表的聯接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info_all\' AND type = \'P\')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all 存儲過程可以通過以下方法執行：
EXECUTE au_info_all
-- Or
EXEC au_info_all
如果該過程是批處理中的第一條語句，則可使用：
au_info_all
天涯風云 19:31:29
B. 使用帶有參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數精確匹配的值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info\' AND type = \'P\')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存儲過程可以通過以下方法執行：
EXECUTE au_info \'Dull\', \'Ann\'
-- Or
EXECUTE au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
EXECUTE au_info @firstname = \'Ann\', @lastname = \'Dull\'
-- Or
EXEC au_info \'Dull\', \'Ann\'
-- Or
EXEC au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
EXEC au_info @firstname = \'Ann\', @lastname = \'Dull\'
如果該過程是批處理中的第一條語句，則可使用：
au_info \'Dull\', \'Ann\'
-- Or
au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
au_info @firstname = \'Ann\', @lastname = \'Dull\'
天涯風云 19:31:41
C. 使用帶有通配符參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數進行模式匹配，如果沒有提供參數，則使用預設的默認值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info2\' AND type = \'P\')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = \'D%\',
@firstname varchar(18) = \'%\'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存儲過程可以用多種組合執行。下面只列出了部分組合：
EXECUTE au_info2
-- Or
EXECUTE au_info2 \'Wh%\'
-- Or
EXECUTE au_info2 @firstname = \'A%\'
-- Or
EXECUTE au_info2 \'[CK]ars[OE]n\'
-- Or
EXECUTE au_info2 \'Hunter\', \'Sheryl\'
-- Or
EXECUTE au_info2 \'H%\', \'S%\'
--------------------------------------------------------------------------------
天涯風云 19:32:22
D. 使用 OUTPUT 參數
OUTPUT 參數允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執行期間設置的某個值。下面的示例創建一個存儲過程 (titles_sum)，并使用一個可選的輸入參數和一個輸出參數。
首先，創建過程：
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = \'titles_sum\' AND type = \'P\')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = \'%\', @@SUM money OUTPUT
AS
SELECT \'Title Name\' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
接下來，將該 OUTPUT 參數用于控制流語言。
說明 OUTPUT 變量必須在創建表和使用該變量時都進行定義。
參數名和變量名不一定要匹配，不過數據類型和參數位置必須匹配（除非使用 @@SUM = variable 形式）。
DECLARE @@TOTALCOST money
EXECUTE titles_sum \'The%\', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT \' \'
PRINT \'All of these titles can be purchased for less than $200.\'
END
ELSE
SELECT \'The total cost of these titles is $\'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
下面是結果集：
Title Name
------------------------------------------------------------------------
The Busy Executive\'s Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
天涯風云 19:32:33
E. 使用 OUTPUT 游標參數
OUTPUT 游標參數用來將存儲過程的局部游標傳遞回調用批處理、存儲過程或觸發器。
首先，創建以下過程，在 titles 表上聲明并打開一個游標：
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'titles_cursor\' and type = \'P\')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
接下來，執行一個批處理，聲明一個局部游標變量，執行上述過程以將游標賦值給局部變量，然后從該游標提取行。
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
天涯風云 19:32:43
F. 使用 WITH RECOMPILE 選項
如果為過程提供的參數不是典型的參數，并且新的執行計劃不應高速緩存或存儲在內存中，WITH RECOMPILE 子句會很有幫助。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'titles_by_author\' AND type = \'P\')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = \'%\'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + \' \' + RTRIM(au_lname) AS \'Authors full name\',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
天涯風云 19:32:59
G. 使用 WITH ENCRYPTION 選項
WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創建加密過程，使用 sp_helptext 系統存儲過程獲取關于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關于該過程的信息。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'encrypt_this\' AND type = \'P\')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
EXEC sp_helptext encrypt_this
下面是結果集：
The object\'s comments have been encrypted.
接下來，選擇加密存儲過程內容的標識號和文本。
SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = \'encrypt_this\'
下面是結果集：
說明 text 列的輸出顯示在單獨一行中。執行時，該信息將與 id 列信息出現在同一行中。
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????
(1 row(s) affected)
天涯風云 19:33:10
H. 創建用戶定義的系統存儲過程
下面的示例創建一個過程，顯示表名以 emp 開頭的所有表及其對應的索引。如果沒有指定參數，該過程將返回表名以 sys 開頭的所有表（及索引）。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'sp_showindexes\' AND type = \'P\')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = \'sys%\'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes \'emp%\'
GO
下面是結果集：
TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
employee employee_ind 1
employee PK_emp_id 2
(2 row(s) affected)
天涯風云 19:33:24
I. 使用延遲名稱解析
下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時不存在，但每個存儲過程都可創建。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'proc1\' AND type = \'P\')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = \'P\' AND o.name = \'proc1\'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'proc2\' AND type = \'P\')
DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = \'P\' and o.name = \'proc2\'
3.sql存儲過程及應用
一、簡介：
存儲過程（Stored Procedure）， 是一組為了完成特定功能的SQL 語句，集經編譯后
存儲在數據庫中，用戶通過指定存儲過程的名字并給出參數，如果該存儲過程帶有參數來執行
它，
在SQL Server 的系列版本中，存儲過程分為兩類：系統提供的存儲過程和用戶自定義存儲過程
。
系統SP，主要存儲master 數據庫中，并以sp_為前綴并且系統存儲過程主要是從系統表中獲取
信息，從而為系統管理員管理SQL Server。 用戶自定義存儲過程是由用戶創建，并能完成
某一特定功能，如：查詢用戶所需數據信息的存儲過程。
存儲過程具有以下優點
1.存儲過程允許標準組件式編程(模塊化設計)
存儲過程在被創建以后，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句，而
且數
據庫專業人員可隨時對存儲過程進行修改，但對應用程序源代碼毫無影響。因為應用程序源代
碼只包含存
儲過程的調用語句，從而極大地提高了程序的可移植性。
2.存儲過程能夠實現快速的執行速度
如果某一操作包含大量的Transaction-SQL 代碼,，或分別被多次執行，那么存儲過程要比批處理
的
執行速度快很多，因為存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優化器對其進
行分析優
化，并給出最終被存在系統表中的執行計劃，而批處理的Transaction-SQL 語句在每次運行時
都要進行
編譯和優化，因此速度相對要慢一些。
3.存儲過程能夠減少網絡流量
對于同一個針對數據數據庫對象的操作，如查詢修改，如果這一操作所涉及到的Transaction-SQL
語句被組織成一存儲過程，那么當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調
用語句，否
則將是多條SQL 語句從而大大增加了網絡流量降低網絡負載。
4.存儲過程可被作為一種安全機制來充分利用
系統管理員通過，對執行某一存儲過程的權限進行限制，從而能夠實現對相應的數據訪問權限的
限
制。
二、變量
@I
三、流程控制語句(if else | select case | while )
Select ... CASE 實例
DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = '1'
Select @iRet =
CASE
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = '二' THEN 2
WHEN @PKDisp = '三' THEN 3
WHEN @PKDisp = '四' THEN 4
WHEN @PKDisp = '五' THEN 5
ELSE 100
END
四、存儲過程格式
創建存儲過程
Create Proc dbo.存儲過程名
存儲過程參數
AS
執行語句
RETURN
執行存儲過程
GO
*********************************************************/
-- 變量的聲明,sql里面聲明變量時必須在變量前加@符號
DECLARE @I INT
-- 變量的賦值，變量賦值時變量前必須加set
SET @I = 30
-- 聲明多個變量
DECLARE @s varchar(10),@a INT
-- Sql 里if語句
IF 條件 BEGIN
執行語句
END
ELSE BEGIN
執行語句
END
DECLARE @d INT
set @d = 1
IF @d = 1 BEGIN
-- 打印
PRINT '正確'
END
ELSE BEGIN
PRINT '錯誤'
END
-- Sql 里的多條件選擇語句.
DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = 1
Select @iRet =
CASE
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = '二' THEN 2
WHEN @PKDisp = '三' THEN 3
WHEN @PKDisp = '四' THEN 4
WHEN @PKDisp = '五' THEN 5
ELSE 100
END
-- 循環語句
WHILE 條件 BEGIN
執行語句
END
DECLARE @i INT
SET @i = 1
WHILE @i<1000000 BEGIN
set @i=@i+1
END
-- 打印
PRINT @i
-- TRUNCATE 刪除表中的所有行，而不記錄單個行刪除操作，不能帶條件
/*
TRUNCATE TABLE 在功能上與不帶 Where 子句的 Delete 語句相同：二者均刪除表中的全部行
。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系統和事務日志資源少。
Delete 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。TRUNCATE TABLE 通過
釋放存儲表數據所用的數據頁來刪除數據，并且只在事務日志中記錄頁的釋放。
TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用
的計數值重置為該列的種子。如果想保留標識計數值，請改用 Delete。如果要刪除表定義及其數據，請
使用 Drop TABLE 語句。
對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 Where 子句的
Delete 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發器。
TRUNCATE TABLE 不能用于參與了索引視圖的表。
示例
下例刪除 authors 表中的所有數據。*/
TRUNCATE TABLE authors
-- Select INTO 從一個查詢的計算結果中創建一個新表。 數據并不返回給客戶端，這一點和普通的
-- Select 不同。 新表的字段具有和 Select 的輸出字段相關聯（相同）的名字和數據類型。
select * into NewTable
from Uname
-- Insert INTO Select
-- 表ABC必須存在
-- 把表Uname里面的字段Username復制到表ABC
Insert INTO ABC Select Username FROM Uname
-- 創建臨時表
Create TABLE #temp(
UID int identity(1, 1) PRIMARY KEY,
UserName varchar(16),
Pwd varchar(50),
Age smallint,
Sex varchar(6)
)
-- 打開臨時表
Select * from #temp
-- 存儲過程
-- 要創建存儲過程的數據庫
Use Test
-- 判斷要創建的存儲過程名是否存在
if Exists(Select name From sysobjects Where name='csp_AddInfo' And
type='P')
-- 刪除存儲過程
Drop Procedure dbo.csp_AddInfo
Go
-- 創建存儲過程
Create Proc dbo.csp_AddInfo
-- 存儲過程參數
@UserName varchar(16),
@Pwd varchar(50),
@Age smallint,
@Sex varchar(6)
AS
-- 存儲過程語句體
insert into Uname (UserName,Pwd,Age,Sex)
values (@UserName,@Pwd,@Age,@Sex)
RETURN
-- 執行
GO
-- 執行存儲過程
EXEC csp_AddInfo 'Junn.A','123456',20,'男'
4.各種存儲過程使用指南
<%@ Language=VBScript %>
<%
'---開始鏈接數據庫
Dim strConnString
strConnString = "driver={SQL Server};server=songhp;uid=sa;pwd=;database=XVZDemo"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnstring
'---結束鏈接數據庫
'---開始為輸入參數賦值
Dim SelectSql , SelectRs
Dim SelectID , SelectName , SelectReturn
SelectSql = "Select Max(CompanyID) From Dim_Company"
Set SelectRs = Conn.Execute(SelectSql)
SelectID = SelectRs(0)
'---結束為輸入參數賦值
Dim TiggerType
TiggerType = 3
Set Cmd = Server.CreateObject("ADODB.Command")
Set Cmd.ActiveConnection = Conn
Cmd.CommandType = 4 '---聲明此過程為存儲過程
If TiggerType = 1 then
'---開始一個輸入參數的存儲過程調用
Cmd.CommandText = "TransCompany1"
Set CmdParam = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParam
Cmd("@TransID") = SelectID
Cmd.Execute
'---結束一個輸入參數的存儲過程調用
Elseif TiggerType = 2 then
'---開始一個輸入參數，一個輸出參數的存儲過程調用
Cmd.CommandText = "TransCompany2"
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute
SelectName = Cmd("@TransName")
'---結束一個輸入參數，一個輸出參數的存儲過程調用
Elseif TiggerType = 3 then
'---開始一個輸入參數，一個輸出參數，一個返回值的存儲過程調用
Cmd.CommandText = "TransCompany3"
Set CmdParamReturn = Cmd.CreateParameter("Return_Value",3,4)
Cmd.Parameters.Append CmdParamReturn
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute
SelectName = Cmd("@TransName")
SelectReturn = Cmd("Return_Value")
'---結束一個輸入參數，一個輸出參數，一個返回值的存儲過程調用
End if
Conn.Close
Set Conn = Nothing
Set Cmd = Nothing
Set CmdParamID = Nothing
Set CmdParamname = Nothing
Set CmdParamReturn = Nothing
%>
5.ASP中存儲過程調用的兩種方式及比較
本人用sql server 和asp寫了一個簡單的留言本，在不斷的嘗試中發現，分頁顯示留言的時候，不同的執行方式，時間上的一些差別。
下面通過對比來看看幾種方式的用時對比。
一，使用存儲過程分頁，這種情況又分為兩種方式：
第一種，使用command對象，如下：
Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="ycuu_gb_getmsg"
Cmd.CommandType=4'adCmdStoredProc
cmd.prepared=true'
set param=Cmd.CreateParameter("@iPageNo",adInteger,1,2,Page)
Cmd.Parameters.Append param
set param=Cmd.CreateParameter("@iPageSize",adInteger,1,2,PageSizeConst)
Cmd.Parameters.Append param
set rs=Cmd.execute
第二種，使用connection對象的執行方法直接執行，具體如下：
set rs=conn.execute ("execute ycuu_gb_getmsg "&page&", "&pagesizeConst)
二，不使用存儲過程，直接使用ADODB.RecordSet的功能來分頁，具體代碼如下：
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * FROM Guestbook Order By dateandtime Desc"
rs.open sql,conn,1,1
rs.pagesize = 150'每頁顯示的留言數量，
total = rs.RecordCount
mypagesize = rs.pagesize
rs.absolutepage = page
為了更加明顯地顯示出速度，我把每頁顯示的留言數量加大到150(事實上當然不會設置這么大的數值啦)。至于我機器的配置，就省略不說了，因為主要是速度對比。
發現，執行的時候時間分別如下：
第一種：穩定于0.1953125 秒到0.2109375 秒之間，平均值大概是：0.20秒
第二種：穩定于0.1716875 秒到0.1857秒之間，平均值大概是：0.177秒
第三種：穩定于0.4375 秒到0.4632秒之間，平均值大概是：0.45秒
但是，當讀取的記錄條數為20的時候，結果如下：
發現，執行的時候時間分別如下：
第一種：穩定于.0390625 秒到.0546875 秒之間，平均值大概是：0.045秒
第二種：穩定于0.046875 秒到.0546875 秒之間，平均值大概是：0.050秒
第三種：穩定于.09375 秒到0.1015625 秒之間，平均值大概是：0.97秒
在這樣看來，似乎conn.execute和command.execute這兩種方式似乎差別并不大，
而前者的調用方式好像更加簡單一點。
同時，在這里可以看出分頁的存儲過程速度確實比recordset的分頁速度要快很多。
PS：小弟第一次發文，嗚嗚嗚，發現寫一篇好的真難，我以后會努力的了。希望大家包涵我這次寫得不好。對了，我還想問問各位大俠conn.execute和command.execute這兩種方式中那種更加好的，呵呵，因為我在網上找到的都是后者這種方式執行存儲過程的。不知道為什么不用前面那種那么簡單的。
|
|
6.SQL存儲過程在.NET數據庫中的應用
一．前言：
存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。總的來說，存儲過程具有以下一些優點：
◆存儲過程允許標準組件式編程
◆存儲過程能夠實現較快的執行速度
◆存儲過程能夠減少網絡流量
◆存儲過程可被作為一種安全機制來充分利用
本文作者將向大家介紹.NET數據庫應用程序中存儲過程的應用，以及如何將它與ADO.NET中的SqlDataAdapter對象、DataSet對象等結合使用以提高.NET數據庫應用程序的總體性能。
二．系統要求：
開發工具：Visual Studio.NET
數據庫管理系統：SQL Server 2000（其中包含了示例程序所用到的Pubs數據庫）
三．創建一個簡單的存儲過程：
這里我將向大家介紹如何運用Visual Studio.NET IDE來創建存儲過程。運用Visual Studio.NET IDE創建存儲過程是非常容易和直觀的，你只要在服務器資源管理器中導向到Pubs數據庫并展開節點，就會發現包括存儲過程在內的各種數據庫對象，如圖1所示。
在存儲過程節點上點擊右鍵便可彈出一個菜單，其中包含了“新建存儲過程”的命令。新建一個存儲過程后，IDE中的代碼編輯窗口便出現如下所示的代碼模板：
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
上面的代碼模板符合簡化的創建存儲過程的語法規則，完整的語法規則如下：
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
限于篇幅，各個參數的含義在此就不多作介紹了，有興趣的讀者可以參考有關SQL Server 2000數據庫管理系統的資料。
下面我對該代碼模板中的各個語法成分略作介紹。CREATE PROCEDURE聲明創建一個存儲過程，后面跟著該存儲過程的名稱。“/*……*/”中的成分是該存儲過程的參數，可包括輸入參數和輸出參數。AS關鍵字后面的內容是該存儲過程的主體部分，其中是任何數量和類型的包含在存儲過程中的SQL語句。RETURN關鍵字表明存儲過程結束并能返回整型狀態值給調用者。下面我們就來創建一個簡單的不帶參數的存儲過程并運用之：
CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN
創建以上存儲過程后，保存之。保存完畢，與該存儲過程相對應的節點就會出現在服務器資源管理器中。同時請注意代碼編輯窗口中的CREATE關鍵字變為ALTER關鍵字了，該關鍵字是用于更改任何現有的存儲過程的。要運行上述存儲過程，只要點擊其節點并在右鍵彈出菜單中選擇“運行存儲過程”，運行的結果圖示如下：
四．創建一個帶參數的存儲過程：
以上我們創建了一個簡單的不帶參數的存儲過程，而在實際的應用中往往會用到很多帶有參數的存儲過程。帶有參數的存儲過程一般是用于更新數據或是插入數據的。下面我們可以運用同樣的操作方法創建一個帶參數的存儲過程：
CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state,
country = @country
WHERE ( pub_id = @pub_id )
RETURN
在上面的創建存儲過程的代碼中，我們通過在名稱前添加一個“@”標志來聲明存儲過程的局部變量－參數，同時還聲明了各個參數的類型，確定了各個參數的方向值，也即表明該參數是輸入型的還是輸出型的或者是輸入輸出型的或者是返回值型的。用戶通過相應的存儲過程名稱以及正確有效的參數便可調用該存儲過程了。還有，你可以通過運用OUTPUT關鍵字在參數中添加輸出型的參數，具體方法請參考上面的語法規則。輸出型的參數能返回給調用者相關的信息。
上面的存儲過程能更新publishers表中相應出版商的信息。你可以通過點擊該存儲過程的節點，在右鍵彈出菜單中選擇“運行存儲過程”來執行它。一旦執行，IDE中便彈出一個輸入出版商信息的對話框（如圖3所示）。在該對話框中填入正確有效的更新信息，注意pub_id的值在原來的表中必須存在，然后點擊“確定”按鈕便可更新數據了。
7.使用SQL存儲過程要特別注意的問題
存儲過程是一個運行于SQL數據庫之中最核心的事務,它通過長駐內存的形式,進行讀取\處理\寫入最為頻繁處理的數據.
ASP雖然在微軟的ASPX的侵襲下，仍舊是中小企業繼續在用的一種網頁語言，但是當要讀取海量數據的時候,如果仍舊使用普通的SQL進行讀取運行與寫入,將導致系統資源的嚴重浪費，所以我們在ASP中使用存儲過程,以提高數據的存取速度,同時通過SQL核心的獲取數據的方法,可以有效的減少垃圾數據(不被立即使用,而且也不備較短的時間里被使用的數據)操作.
在ASP中使用存儲過程也是相當的容易,例如以下一個通過SQL存儲過程進行分頁的方法:
存儲過程:
CREATE procedure p_splitpage
@sql nvarchar(4000), --要執行的sql語句
@page int=1, --要顯示的頁碼
@pageSize int, --每頁的大小
@pageCount int=0 out, --總頁數
@recordCount int=0 out --總記錄數
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
GO
ASP頁面的內容
sql = "Select id, c_s_name from tabNews where deleted<>1 Order By id Desc"
page = cint(page_get)
if page_post<>""then
page = cint(page_post)
end if
if not page > 0 then
page = 1
end if
pagesize=20’每頁的條數
set cmd = server.CreateObject("adodb.command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "p_SplitPage"
cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql)
cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page)
cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, pageSize)
cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount)
cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount)
set rs = cmd.Execute
set rs = rs.NextRecordSet
pageCount = cmd.Parameters("@pageCount").value
recordCount = cmd.Parameters("@recordCount").value
if pageCount = 0 then pageCount = 1
if page>pageCount then
response.Redirect("?page="&pageCount)
end if
set rs = cmd.Execute
我們如此就可以實現對數據的讀取并可以進行有效的分頁,但是我們往往會發現一個問題,如果我們構造的SQL語句如果使用的是select * from tab ...的話，就經常出現無法讀取數據的錯誤,或者是讀取出來,但是有的數據無法顯示的錯誤，經過仔細的檢查發現,如果是排列在SQL語句的前列的數據可以被讀取,而如果不按照SQL讀取出來的字段進行順序讀取,就會出現數據丟失的情況,所以唯一的途徑就是進行順序讀取.如:
對于select id, newsTitle, newsContent from tabNews where ...的SQL語句,就應當將所有的數據讀取到變量上來，并且要求是按照SQL語句的順序進行讀取，然后這些數據就可以自由的使用了.
id = rs("id")
newsTitle = rs("newsTitle")
...
分析出現這個的原因是:SQL數據庫在構造虛擬表的時候就是以一種先進先出的原則,把所有的數據排列在一個內存段之中,通過順序的讀取,將數據逐一的讀取,而如果跳過某個具體的字段獲取下一個字段的信息,系統就會將原來的那個字段的信息丟失,以釋放內存,這是出于系統構造的簡單性和系統的內存最低化的要求,所以這樣也保證了有限的內存資源得到最充分的發揮,這也是為什么存儲過程比普通的SQL要快的原因.
|