基本介紹
- 中文名：PROCEDURE
- 參數：procedure_name
WCDMA,Transact-SQL 參考,Pascal/Delphi,
WCDMA
User Equipment (UE) procedures in idle mode and procedures for cell reselection in connected mode
Transact-SQL 參考
定義：the rules for people to follow when using software,hardware,and data
CREATE PROCEDURE
創建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數的 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 關鍵字。有關 SQL Server 提供的數據類型及其語法的更多信息，請參見數據類型。
說明 對於可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。
VARYING
default
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。
建議在存儲過程的任何 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 語句時，該設定只在存儲過程完成之前有效。之後，設定將恢復為調用存儲過程時的值。這使個別的客戶端可以設定所需的選項，而不會影響存儲過程的邏輯。
說明 SQL Server 是將空字元串解釋為單個空格還是解釋為真正的空字元串，由兼容級別設定控制。如果兼容級別小於或等於 65，SQL Server 就將空字元串解釋為單個空格。如果兼容級別等於 70，則 SQL Server 將空字元串解釋為空字元串。有關更多信息，請參見 sp_dbcmptlevel。
獲得有關存儲過程的信息
若要顯示用來創建過程的文本，請在過程所在的資料庫中執行 sp_helptext，並使用過程名作為參數。
說明 使用 ENCRYPTION 選項創建的存儲過程不能使用 sp_helptext 查看。
若要顯示有關過程引用的對象的報表，請使用 sp_depends。
若要為過程重命名，請使用 sp_rename。
引用對象
SQL Server 允許創建的存儲過程引用尚不存在的對象。在創建時，只進行語法檢查。執行時，如果高速快取中尚無有效的計畫，則編譯存儲過程以生成執行計畫。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的對象，則仍可以成功創建，但在運行時將失敗，因為所引用的對象不存在。有關更多信息，請參見延遲名稱解析和編譯。
延遲名稱解析和兼容級別
SQL Server 允許 Transact-SQL 存儲過程在創建時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 存儲過程引用了該存儲過程中定義的表，而兼容級別設定（通過執行 sp_dbcmptlevel 來設定）為 65，則在創建時會發出警告信息。而如果在運行時所引用的表不存在，將返回錯誤信息。有關更多信息，請參見 sp_dbcmptlevel 和延遲名稱解析和編譯。
執行存儲過程
成功執行 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 中的系統表上產生爭用，從而對性能產生負面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統表中存儲數據，因此可以避免這一問題。
自動執行存儲過程
SQL Server 啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員創建，並在 sysadmin 固定伺服器角色下作為後台過程執行。這些過程不能有任何輸入參數。
對啟動過程的數目沒有限制，但是要注意，每個啟動過程在執行時都會占用一個連線。如果必須在啟動時執行多個過程，但不需要並行執行，則可以指定一個過程作為啟動過程，讓該過程調用其它過程。這樣就只占用一個連線。
在啟動時恢復了最後一個資料庫後，即開始執行存儲過程。若要跳過這些存儲過程的執行，請將啟動參數指定為跟蹤標記 4022。如果以最低配置啟動 SQL Server（使用 -f 標記），則啟動存儲過程也不會執行。有關更多信息，請參見跟蹤標記。
若要創建啟動存儲過程，必須作為 sysadmin 固定伺服器角色的成員登錄，並在 master 資料庫中創建存儲過程。
使用 sp_procoption 可以：
將現有存儲過程指定為啟動過程。
停止在 SQL Server 啟動時執行過程。
查看 SQL Server 啟動時執行的所有過程的列表。
存儲過程嵌套
存儲過程可以嵌套，即一個存儲過程可以調用另一個存儲過程。在被調用過程開始執行時，嵌套級將增加，在被調用過程執行結束後，嵌套級將減少。如果超出最大的嵌套級，會使整個調用過程鏈失敗。可用 @@NESTLEVEL 函式返回當前的嵌套級。
若要估計編譯後的存儲過程大小，請使用下列性能監視計數器。
性能監視器對象名 性能監視計數器名稱
SQLServer：緩衝區管理器 高速快取大小（頁面數）
SQLServer：高速快取管理器 高速快取命中率
高速快取頁
高速快取對象計數*
* 各種分類的高速快取對象均可以使用這些計數器，包括特殊 sql、準備 sql、過程、觸發器等。
有關更多信息，請參見 SQL Server:Buffer Manager 對象和 SQL Server:Cache Manager 對象。
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 EⅪSTS (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 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
B. 使用帶有參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數精確匹配的值。
USE pubs
IF EⅪSTS (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⒇
AS
SELECT au_lname,au_fname,title,pub_name
FROM authors a INNER JOIN titleauthor ta
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'
C. 使用帶有通配符參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數進行模式匹配，如果沒有提供參數，則使用預設的默認值。
USE pubs
IF EⅪSTS (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⒅= '%'
AS
SELECT au_lname,au_fname,title,pub_name
FROM authors a INNER JOIN titleauthor ta
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%'
D. 使用 OUTPUT 參數
OUTPUT 參數允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執行期間設定的某個值。下面的示例創建一個存儲過程 (titles_sum)，並使用一個可選的輸入參數和一個輸出參數。
首先，創建過程：
USE pubs
GO
IF EⅪSTS(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⒇))
下面是結果集：
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.
E. 使用 OUTPUT 游標參數
OUTPUT 游標參數用來將存儲過程的局部游標傳遞迴調用批處理、存儲過程或觸發器。
首先，創建以下過程，在 titles 表上聲明並打開一個游標：
USE pubs
IF EⅪSTS (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
F. 使用 WITH RECOMPILE 選項
如果為過程提供的參數不是典型的參數，並且新的執行計畫不應高速快取或存儲在記憶體中，WITH RECOMPILE 子句會很有幫助。
USE pubs
IF EⅪSTS (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 ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
G. 使用 WITH ENCRYPTION 選項
WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創建加密過程，使用 sp_helptext 系統存儲過程獲取關於加密過程的信息，然後嘗試直接從 syscomments 表中獲取關於該過程的信息。
IF EⅪSTS (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.
下面是結果集：
說明 text 列的輸出顯示在單獨一行中。執行時，該信息將與 id 列信息出現在同一行中。
id text
---------- ------------------------------------------------------------
1413580074????????????????????????????????e??????????????????????????????????????????????????????????????????????????
（1 row(s) affected)
H. 創建用戶定義的系統存儲過程
下面的示例創建一個過程，顯示錶名以 emp 開頭的所有表及其對應的索引。如果沒有指定參數，該過程將返回表名以 sys 開頭的所有表（及索引）。
IF EⅪSTS (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
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)
I. 使用延遲名稱解析
下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。儘管引用的表或列在編譯時不存在，但每個存儲過程都可創建。
IF EⅪSTS (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.
USE master
GO
IF EⅪSTS (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⑴
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.
ALTER PROCEDURE
批處理
控制流語言
游標
DBCC
DECLARE @local_variable
DROP PROCEDURE
函式
GRANT
設計存儲過程
SELECT
sp_addextendedproc
sp_depends
sp_helptext
sp_procoption
sp_recompile
系統表
使用注釋
使用變數和參數
Pascal/Delphi
Procedure 過程名(參數表);
Type
私有類型說明
var
私有變數聲明
begin
過程主體
end;
在參數表中，可用var關鍵字寫入變數形參，程式中可以對其進行操作
如：
Procedure swap(a,b:longint);
var
y:longint;
begin
y:=a;
a:=b;
b:=y;
end;
這個過程在編譯時會報錯，因為默認參數表是形參，存儲的只是它的值，而不是地址，無法更改
應改為：
Procedure swap(var a,b:longint);
var
y:longint;
begin
y:=a;
a:=b;
b:=y;
end;
注意：在pascal/delphi中，過程不可返回值；
下面是一個例子：
//快速排序
program qs;
Var
i,n:longint;
a:array[1..300000] of longint;
Procedure qsort(l,r:longint);
Var
i,j,x,y:longint;
begin
i:=l;
j:=r;
x:=a[(l+r) div 2];
Repeat
while a[i]<xdo inc(i);
while a[j]>xdo dec(j);
if i<=jthen
begin
y:=a[i];
a[i]:=a[j];
a[j]:=y;
inc(i);
dec(j);
end;
until i>j;
if i<rthen qsort(i,r);
if j>lthen qsort(l,j);
end;
begin
readln(n);
for i:=1 to n do
readln(a[i]);
qsort（1,n);
for i:=1 to n do
writeln(a[i]);
readln
end.
