什麼叫預存程序呢?
將常用的或很複雜的工作，預先用sql語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫教程提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。
預存程序的優點
1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般sql語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。
2.當對資料庫進行複雜操作時(如對多個表進行update,insert,query,delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。
3.預存程序可以重複使用,可減少資料庫開發人員的工作量
4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權
建立預存程序
*************************************************
文法
create procedure [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ varying ] [ = default ] [ output ]
] [ ,...n ][ with
{ recompile | encryption | recompile , encryption } ][ for replication ]
as sql_statement [ ...n ]
參數owner
擁有預存程序的使用者 id 的名稱。owner 必須是目前使用者的名稱或目前使用者所屬的角色的名稱。
procedure_name
新預存程序的名稱。過程名必須符合標識符規則，且對於資料庫及其所有者必須唯一。
;number
是可選的整數，用來對同名的過程分組，以便用一條 drop procedure 語句即可將同組的過程一起除去。例如，名為 orders 的應用程式使用的過程可以命名為 orderproc;1、orderproc;2 等。drop procedure orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前後使用適當的定界符。
@parameter
過程中的參數。在 create procedure 語句中可以聲明一個或多個參數。使用者必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的預設值，或者該值設定為等於另一個參數）。預存程序最多可以有 2.100 個參數。
使用 @ 符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其它過程中。預設情況下，參數只能代替常量，而不能用於代替表名、列名或其它資料庫物件的名稱。
data_type
參數的資料類型。除 table 之外的其他所有資料類型均可以用作預存程序的參數。但是，cursor 資料類型只能用於 output 參數。如果指定 cursor 資料類型，則還必須指定 varying 和 output 關鍵字。對於可以是 cursor 資料類型的輸出參數，沒有最大數目的限制。
varying
指定作為輸出參數支援的結果集（由預存程序動態構造，內容可以變化）。僅適用於遊標參數。
default
參數的預設值。如果定義了預設值，不必指定該參數的值即可執行過程。預設值必須是常量或 null。如果過程將對該參數使用 like 關鍵字，那麼預設值中可以包含萬用字元（%、_、[] 和 [^]）。
output
表明參數是返回參數。該選項的值可以返回給 execute。使用 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 複製的一部分發布。
for replication
指定不能在訂閱伺服器上執行為複製建立的預存程序。.使用 for replication 選項建立的預存程序可用作預存程序篩選，且只能在複製過程中執行。本選項不能和 with recompile 選項一起使用。
as
指定過程要執行的操作。
sql_statement
過程中要包含的任意數目和類型的 transact-sql 語句。但有一些限制。
n
是表示此過程可以包含多條 transact-sql 語句的預留位置。
**********************************************
注:*所包圍部分來自ms的聯機叢書.
下面是我自己寫的幾個例子,協助自己理解一下
首先在資料庫中建立一個t_city表
id cityname short
1 北京 bj
2 武漢 wh
3 廣州 gz
1，選出表中的所有內容並返回一個結果集
create procedure myprocedure_all
as
select * from t_city
return
2.根據傳入的參數查詢並返回結果集
create procedure myprocedure_para
@cityname nvarchar(50),
@short nvarchar(50)
as
select * from t_city where cityname=@cityname and short=@short
return
3.帶有輸出參數的預存程序(返回前兩條記錄的id和)
create procedure myprocedure_output
@sum int output
as
select @sum=sum(id) from (select top 2 * from t_city)as tmptable
return
現在看一個c#中的應用預存程序執行個體
在c#代碼中，我們將使用新的類，system.data.sqlclient.parameter。該類的對象設計用於表示預存程序中的參數，因此建構函式需要知道名稱、資料類型和所討論的參數的大小。
<%@ import namespace="system.data" %>
<%@ import namespace="system.data.sqlclient" %><html>
<head><title>using stored procedures with parameters</title></head>
<body>
<form runat="server" method="post">
enter a state code:
<asp教程:textbox id="txtregion" runat="server" />
<asp:button id="btnsubmit" runat="server"
text="search" onclick="submit" />
<br/><br/>
<asp:datagrid id="dgoutput" runat="server" />
</form>
</body>
</html><script language="c#" runat="server">
private void submit(object sender, eventargs e)
{
string strconnection ="server=224numeca;database=northwind;user id=sa;password=sa";
sqlconnection objconnection = new sqlconnection(strconnection);
sqlcommand objcommand = new sqlcommand("sp_customersbystate", objconnection);
objcommand.commandtype = commandtype.storedprocedure;sqlparameter objparameter = new sqlparameter("@region", sqldbtype.nvarchar, 15);
/* 建立名為@region並聲明為nvchar(15)的參數，它與預存程序中的聲明相匹配。該版本的建構函式的第二個參數總是system.data.sqldbtype枚舉的成員，該枚舉有24個成員，表示您可能需要的所有資料類型的。*/
objcommand.parameters.add(objparameter);
/* 第二行將參數添加到命令對象的parameter集合，經常會忘記該操作 */
objparameter.direction = parameterdirection.input;/* 設定參數對象的direction屬性，以決定它是否會用於將資訊傳遞給預存程序，或接收來自它的資訊。parameterdirection.input實際上就是該屬性的預設值，但是從維護和可讀性的觀點出發，將它放入代碼中是很有協助的。 */
objparameter.value = txtregion.text;/* 我們將參數的value屬性設定為txtregion文字框的文字屬性。 */
objconnection.open();
objconnection.open();
dgoutput.datasource = objcommand.executereader();
dgoutput.databind();objconnection.close();
}
</script>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
