SQL 存儲進程基本語法之一。本站提示廣大學習愛好者：（SQL 存儲進程基本語法之一）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL 存儲進程基本語法之一正文
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 個參數。
應用 @ 符號作為第一個字符來指定參數稱號。參數稱號必需相符標識符的規矩。每一個進程的參數僅用於該進程自己；雷同的參數稱號可以用在其它進程中。默許情形下，參數只能取代常量，而不克不及用於取代表名、列名或其它數據庫對象的稱號。有關更多信息，請拜見 EXECUTE。
data_type
參數的數據類型。一切數據類型（包含 text、ntext 和 image）都可以用作存儲進程的參數。不外，cursor 數據類型只能用於 OUTPUT 參數。假如指定的數據類型為 cursor，也必需同時指定 VARYING 和 OUTPUT 症結字。有關 SQL Server 供給的數據類型及其語法的更多信息，請拜見數據類型。
解釋 關於可所以 cursor 數據類型的輸入參數，沒有最年夜數量的限制。
VARYING
指定作為輸入參數支撐的成果集（由存儲進程靜態結構，內容可以變更）。僅實用於游標參數。
default
參數的默許值。假如界說了默許值，不用指定該參數的值便可履行進程。默許值必需是常量或 NULL。假如進程將對該參數應用 LIKE 症結字，那末默許值中可以包括通配符（%、_、[] 和 [^]）。
OUTPUT
注解參數是前往參數。該選項的值可以前往給 EXEC[UTE]。應用 OUTPUT 參數可將信息前往給挪用進程。Text、ntext 和 image 參數可用作 OUTPUT 參數。應用 OUTPUT 症結字的輸入參數可所以游標占位符。
表現最多可以指定 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 語句。但有一些限制。
是表現此進程可以包括多條 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 語句時，該設置只在存儲進程完成之前有用。以後，設置將恢復為挪用存儲進程時的值。這使個體的客戶端可以設置所需的選項，而不會影響存儲進程的邏輯。
解釋 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 數據庫中具有顯式 CREATE PROCEDURE 權限的用戶，才可以在該數據庫中顯式創立暫時進程（不應用編號符定名）。可以授與或破除這些進程中的權限。
解釋 頻仍應用暫時存儲進程會在 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 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
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'
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%'
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.
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
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
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'
