簡介
sql語句執行的時候要先編譯，然後執行。存儲過程就是編譯好了的一些sql語句。用的時候直接就可以用了，所以效率會高。
基本信息
概述
在大型資料庫系統中，存儲過程和觸發器具有很重要的作用。無論是存儲過程還是觸發器，都是SQL語句和流程控制語句的集合。就本質而言，觸發器也是一種存儲過程。存儲過程在運算時生成執行方式，所以，以後對其再運行時其執行速度很快。SQLServer2005不僅提供了用戶自定義存儲過程的功能，而且也提供了許多可作為工具使用的系統存儲過程。格式
CREATEPROCEDURE[擁有者.]存儲過程名[;程式編號]sql中的存儲過程及相關介紹[(參數#1,…參數#1024)]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}
]
[FORREPLICATION]
AS程式行
其中存儲過程名不能超過128個字。每個存儲過程中最多設定1024個參數
(SQLServer7.0以上版本),參數的使用方法如下：
@參數名數據類型[VARYING][=內定值][OUTPUT]
每個參數名前要有一個“@”符號,每一個存儲過程的參數僅為該程式內部使用,參數的類型除了IMAGE外，其他SQLServer所支持的數據類型都可使用。
[=內定值]相當於我們在建立資料庫時設定一個欄位的默認值，這裡是為這個參數設定默認值。[OUTPUT]是用來指定該參數是既有輸入又有輸出值的，也就是在調用了這個存儲過程時，如果所指定的參數值是我們需要輸入的參數，同時也需要在結果中輸出的，則該項必須為OUTPUT，而如果只是做輸出參數用，可以用CURSOR，同時在使用該參數時，必須指定VARYING和OUTPUT這兩個語句。
例子：
CREATEPROCEDUREorder_tot_amt
@o_idint,
@p_totintoutput
AS
SELECT@p_tot=sum(Unitprice*Quantity)
FROMorderdetails
WHEREorderid=@o_id
GO
例子說明：
該例子是建立一個簡單的存儲過程order_tot_amt,這個存儲過程根據用戶輸入的定單ID號碼(@o_id),由訂單明細表(orderdetails)中計算該定單銷售總額[單價(Unitprice)*數量(Quantity)],這一金額通過@p_tot這一參數輸出給調用這一存儲過程的程式。
功能
這類語言主要提供以下功能，讓用戶可以設計出符合引用需求的程式：1)、變數說明
2)、ANSI（美國國家標準化組織）兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、內部函式
種類
1系統存儲過程
以sp_開頭,用來進行系統的各項設定.取得信息.相關管理工作。2本地存儲過程
用戶創建的存儲過程是由用戶創建並完成某一特定功能的存儲過程，事實上一般所說的存儲過程就是指本地存儲過程。3臨時存儲過程
分為兩種存儲過程：一是本地臨時存儲過程，以井字號(#)作為其名稱的第一個字元，則該存儲過程將成為一個存放在tempdb資料庫中的本地臨時存儲過程，且只有創建它的用戶才能執行它;
二是全局臨時存儲過程，以兩個井字號(##)號開始，則該存儲過程將成為一個存儲在tempdb資料庫中的全局臨時存儲過程，全局臨時存儲過程一旦創建，以後連線到伺服器的任意用戶都可以執行它，而且不需要特定的許可權。
4遠程存儲過程
在SQLServer2005中，遠程存儲過程(RemoteStoredProcedures)是位於遠程伺服器上的存儲過程，通常可以使用分散式查詢和EXECUTE命令執行一個遠程存儲過程。5擴展存儲過程
擴展存儲過程(ExtendedStoredProcedures)是用戶可以使用外部程式語言編寫的存儲過程，而且擴展存儲過程的名稱通常以xp_開頭。
基本語法
創建存儲過程
createproceduresp_name@[參數名][類型],@[參數名][類型]
as
begin
.........
end
以上格式還可以簡寫成：
createprocsp_name
@[參數名][類型],@[參數名][類型]
as
begin
.........
end
/*註：“sp_name”為需要創建的存儲過程的名字，該名字不可以以阿拉伯數字開頭*/
調用存儲過程
1.基本語法：execsp_name[參數名]刪除存儲過程
1.基本語法：dropproceduresp_name
2.注意事項
(1)不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程
其他常用命令
1.showprocedurestatus顯示資料庫中所有存儲的存儲過程基本信息，包括所屬資料庫，存儲過程名稱，創建時間等
2.showcreateproceduresp_name
顯示某一個mysql存儲過程的詳細信息
3、execsp_helptextsp_name
顯示你這個sp_name這個對象創建文本
存儲過程的優點
存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和最佳化後存儲在資料庫伺服器中，使用時只要調用即可。在ORACLE中，若干個 有聯繫的過程可以組合在一起構成程式
使用存儲過程有以下的優點：
* 存儲過程的能力大大增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的 運算。
* 可保證數據的安全性和完整性。
# 通過存儲過程可以使沒有許可權的用戶在控制之下間接地存取資料庫，從而保證數據的安全。
# 通過存儲過程可以使相關的動作在一起發生，從而可以維護資料庫的完整性。
* 再運行存儲過程前，資料庫已對其進行了語法和句法分析，並給出了最佳化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。 由於執行SQL語句的大部分工作已經完成，所以存儲過程能以極快的速度執行。
* 可以降低網路的通信量。
* 使體現企業規則的運算程式放入資料庫伺服器中，以便：
# 集中控制。
# 當企業規則發生變化時在伺服器中改變存儲過程即可，無須修改任何應用程式。企業規則的特點是要經常變化，如果把體現企業規則的運 算程式放入應用程式中，則當企業規則發生變化時，就需要修改應用程式工作量非常之大（修改、發行和安裝應用程式）。如果把體現企業規則的 運算放入存儲過程中，則當企業規則發生變化時，只要修改存儲過程就可以了，應用程式無須任何變化。
存儲過程的建立
在SqlServer的企業管理器中建立存儲過程：
（1）打開企業管理器Enterprisemanager
（2）選擇伺服器組(SQLServerGroup)、伺服器、資料庫(Database)以及相就的資料庫，滑鼠右擊對應資料庫下的StoredProcdures項，在彈出的選單中選擇NewStoredProcedure，在StoredProceduresProperties中輸入建立存儲過程的語句。下面是一個例子：
Create PROCEDURE proctest
@mycola Char(10),
@mycolb Char(10),
@mycolc text
AS
Insert into chatdata( mycola,mycolb,mycolc ) values ( @mycola,@mycolb,@mycolc )
在SqlServer的文檔中它的語法為：
Create PROC[EDURE] procedure_name [;number][{@parameterdata_type}[VARYING][=default][OUTPUT]] [,...n]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FORREPLICATION]
AS sql_statement[...n]
如果你對Sql語法不熟悉，可以使用CheckSyntax來檢查語法。在上例中，表示建立存儲過程名為mycola，帶３個參數的存儲過過程，其中第一個參數mycola數據類型為char，寬度10；第２個參數數據類型為char，寬度為10，第３個參數數據類型為text，在這裡使用的是SqlServer的數據類型。
觸發器
概念及作用
觸發器是一種特殊類型的存儲過程，它不同於我們前面介紹過的存儲過程。觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過存儲過程名字而被直接調用。當對某一表進行諸如Update、Insert、Delete這些操作時，SQLServer就會自動執行觸發器所定義的SQL語句，從而確保對數據的處理必須符合由這些SQL語句所定義的規則。觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜的參照完整性和數據的一致性。除此之外，觸發器還有其它許多不同的功能：
(1)強化約束(Enforcerestriction)
觸發器能夠實現比CHECK語句更為複雜的約束。
(2)跟蹤變化Auditingchanges
觸發器可以偵測資料庫內的操作，從而不允許資料庫中未經許可的指定更新和變化。
(3)級聯運行(Cascadedoperation)。
觸發器可以偵測資料庫內的操作，並自動地級聯影響整個資料庫的各項內容。例如，某個表上的觸發器中包含有對另外一個表的數據操作(如刪除，更新，插入)而該操作又導致該表上觸發器被觸發。
(4)存儲過程的調用(Storedprocedureinvocation)。
為了回響資料庫更新,觸發器可以調用一個或多個存儲過程，甚至可以通過外部過程的調用而在DBMS(資料庫管理系統)本身之外進行操作。
由此可見，觸發器可以解決高級形式的業務規則或複雜行為限制以及實現定製記錄等一些方面的問題。例如，觸發器能夠找出某一表在數據修改前後狀態發生的差異，並根據這種差異執行一定的處理。此外一個表的同一類型(Insert、Update、Delete)的多個觸發器能夠對同一種數據操作採取多種不同的處理。
總體而言，觸發器性能通常比較低。當運行觸發器時，系統處理的大部分時間花費在參照其它表的這一處理上，因為這些表既不在記憶體中也不在資料庫設備上，而刪除表和插入表總是位於記憶體中。可見觸發器所參照的其它表的位置決定了操作要花費的時間長短。
觸發器種類
SQLServer2000支持兩種類型的觸發器：AFTER觸發器和INSTEADOF觸發器。其中AFTER觸發器即為SQLServer2000版本以前所介紹的觸發器。該類型觸發器要求只有執行某一操作(InsertUpdateDelete)之後，觸發器才被觸發，且只能在表上定義。可以為針對表的同一操作定義多個觸發器。對於AFTER觸發器，可以定義哪一個觸發器被最先觸發，哪一個被最後觸發，通常使用系統過程sp_settriggerorder來完成此任務。INSTEADOF觸發器表示並不執行其所定義的操作(Insert、Update、Delete)，而僅是執行觸發器本身。既可在表上定義INSTEADOF觸發器，也可以在視圖上定義INSTEADOF觸發器，但對同一操作只能定義一個INSTEADOF觸發器。
常用格式
Createprocedureprocedure_name[@parameterdata_type][output]
[with]{recompile|encryption}
as
sql_statement
解釋：
output：表示此參數是可傳回的
with{recompile|encryption}
recompile:表示每次執行此存儲過程時都重新編譯一次
encryption:所創建的存儲過程的內容會被加密
如：
表book的內容如下
編號書名價格
001C語言入門$30
002PowerBuilder報表開發$52
實例1:查詢表Book的內容的存儲過程
createprocquery_book
as
select*frombook
go
execquery_book
實例2：
加入一筆記錄到表book,並查詢此表中所有書籍的總金額
Createprocinsert_book
@param1char(10),@param2varchar(20),@param3money,@param4moneyoutput
withencryption---------加密
as
insertintobook(編號,書名,價格)Values(@param1,@param2,@param3)
select@param4=sum(價格)frombook
go
執行例子：
declare@total_pricemoney
execinsert_book'003','Delphi控制項開發指南',$100,@total_price
print'總金額為'+convert(varchar,@total_price)
go
存儲過程的3種傳回值：
1)、以Return傳回整數
2)、以output格式傳回參數
3)、Recordset
傳回值的區別：
output和return都可在批次程式中用變數接收，而recordset則傳回到執行批次的客戶端中。
實例3：
設有兩個表為Product,Order_,其表內容如下：
Product
產品編號產品名稱客戶訂數
001鋼筆30
002毛筆50
003鉛筆100
Order_
產品編號客戶名客戶訂金
001南山區$30
002羅湖區$50
003寶安區$4
請實現按編號為連線條件,將兩個表連線成一個臨時表,該表只含編號.產品名.客戶名.訂金.總金額,
總金額=訂金*訂數,臨時表放在存儲過程中
代碼如下：
Createproctemp_sale
as
selecta.產品編號,a.產品名稱,b.客戶名,b.客戶訂金,a.客戶訂數*b.客戶訂金as總金額
into#temptablefromProductainnerjoinOrder_bona.產品編號=b.產品編號-----此處要用別名
if@@error=0
print'Good'
else
print'Fail'
go
優缺點
優點
①重複使用。存儲過程可以重複使用，從而可以減少資料庫開發人員的工作量。②提高性能。存儲過程在創建的時候在進行了編譯，將來使用的時候不再重新翻譯。一般的SQL語句每執行一次就需要編譯一次，所以使用存儲過程提高了效率。
③減少網路流量。存儲過程位於伺服器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網路傳輸的數據量。
④安全性。參數化的存儲過程可以防止SQL注入式攻擊，而且可以將Grant、Deny以及Revoke許可權套用於存儲過程。
簡單講：
1.存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。
2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時)，可將此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。
3.存儲過程可以重複使用,可減少資料庫開發人員的工作量
4.安全性高,可設定只有某些用戶才具有對指定存儲過程的使用權
有一點需要注意的是，一些網上盛傳的所謂的存儲過程要比sql語句執行更快的說法，實際上是個誤解，並沒有根據，包括微軟內部的人也不認可這一點，所以不能作為正式的優點，希望大家能夠認識到這一點。
缺點
1：調試麻煩，但是用PL/SQLDeveloper調試很方便！彌補這個缺點。2：移植問題，資料庫端代碼當然是與資料庫相關的。但是如果是做工程型項目，基本不存在移植問題。
3：重新編譯問題，因為後端代碼是運行前編譯的，如果帶有引用關係的對象發生改變時，受影響的存儲過程、包將需要重新編譯（不過也可以設定成運行時刻自動編譯）。
4：如果在一個程式系統中大量的使用存儲過程，到程式交付使用的時候隨著用戶需求的增加會導致數據結構的變化，接著就是系統的相關問題了，最後如果用戶想維護該系統可以說是很難很難、而且代價是空前的，維護起來更麻煩。
實例
資料庫存儲過程
資料庫存儲過程的實質就是部署在資料庫端的一組定義代碼以及SQL。將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱存儲起來，那么以後要叫資料庫提供與已定義好的存儲過程的功能相同的服務時，只需調用execute，即可自動完成命令。利用SQL的語言可以編寫對於資料庫訪問的存儲過程，其語法如下：
CREATEPROC[EDURE]procedure_name[;number]
[
{@parameterdata_type}][VARYING][=default][OUTPUT]
]
[,...n]
[WITH
{
RECOMPILE
|ENCRYPTION
|RECOMPILE,ENCRYPTION
}
]
[FORREPLICATION]
AS
sql_statement[...n]
[]內的內容是可選項，而（）內的內容是必選項，
例：若用戶想建立一個刪除表tmp中的記錄的存儲過程Select_delete可寫為：
CreateProcselect_delAs
Deletetmp
例：用戶想查詢tmp表中某年的數據的存儲過程
createprocselect_query@yearintas
select*fromtmpwhereyear=@year
在這裡@year是存儲過程的參數
例：該存儲過程是從某結點n開始找到最上層的父親結點，這種經常用到的過程可以由存儲過程來擔當，在網頁中重複使用達到共享。
空：表示該結點為頂層結點
fjdid(父結點編號)
結點n非空：表示該結點的父親結點號
dwmc（單位名稱）
CREATEprocsearch_dwmc@dwidoldint,@dwmcresultvarchar(100)output
as
declare@stopint
declare@resultvarchar(80)
declare@dwmcvarchar(80)
declare@dwidint
setnocounton
set@stop=1
set@dwmc=""
select@dwmc=dwmc,@dwid=convert(int,fjdid)fromjtdwwhereid=@dwidold
set@result=rtrim(@dwmc)
if@dwid=0
set@stop=0
while(@stop=1)and(@dwid<>0)
begin
set@dwidold=@dwid
select@dwmc=dwmc,@dwid=convert(int,fjdid)fromjtdwwhereid=@dwidold
if@@rowcount=0
set@dwmc=""
else
set@result=@dwmc+@result
if(@dwid=0)or(@@rowcount=0)
set@stop=0
else
continue
end
set@dwmcresult=rtrim(@result)
使用execpro-name[pram1pram2.....]、
SQLServer中存儲過程
sql語句執行的時候要先編譯，然後執行。存儲過程就是編譯好了的一些sql語句。用的時候直接就可以用了。在SQLServer的查詢分析器中，輸入以下代碼:
declare@tot_amtint
executeorder_tot_amt1,@tot_amtoutput
select@tot_amt
以上代碼是執行order_tot_amt這一存儲過程，以計算出定單編號為1的定單銷售金額，我們定義@tot_amt為輸出參數，用來承接我們所要的結果。
Oracle中的存儲過程
1.創建過程與其它的資料庫系統一樣，Oracle的存儲過程是用PL/SQL語言編寫的能完成一定處理功能的存儲在資料庫字典中的程式。
語法:
create[orreplace]procedureprocedure_name
[(argment[{in|inout}]type,
argment[{in|out|inout}]type
{is|as}
<類型.變數的說明>
(注:不用declare語句)
Begin
<執行部分>
exception
<可選的異常處理說明>
end;
1.1這裡的IN表示向存儲過程傳遞參數，OUT表示從存儲過程返回參數。而INOUT表示傳遞參數和返回參數；
1.2在存儲過程內的參數只能指定參數類型；不能指定長度；
1.3在AS或IS後聲明要用到的變數名稱和變數類型及長度；
1.4在AS或IS後聲明變數不要加declare語句。
2.使用過程
存儲過程建立完成後，只要通過授權，用戶就可以在SQLPLUS、Oracle開發工具或第三方開發工具來調用運行。Oracle使用EXECUTE語句來實現對存儲過程的調用。
語法：
EXEC[UTE]procedure_name(parameter1,parameter2…);
3.開發過程
如今的幾大資料庫廠商提供的編寫存儲過程的工具都沒有統一，雖然它們的編寫風格有些相似，但由於沒有標準，所以各家的開發調試過程也不一樣。下面編寫PL/SQL存儲過程、函式、包及觸發器的步驟如下：
3.1編輯存儲過程源碼使用文字編輯處理軟體編輯存儲過程源碼，要用類似WORD文字處理軟體進行編輯時，要將源碼存為文本格式。
3.2對存儲過程程式進行解釋在SQLPLUS或用調試工具將存儲過程程式進行解釋；
在SQL>下調試，可用start或get等Oracle命令來啟動解釋。如：
SQL>startc:\stat1.sql
如果使用調試工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。
3.3調試源碼直到正確我們不能保證所寫的存儲過程達到一次就正確。所以這裡的調試是每個程式設計師必須進行的工作之一。在SQLPLUS下來調試主要用的方法是：
1．使用SHOWERROR命令來提示源碼的錯誤位置；
2．使用USER_ERRORS數據字典來查看各存儲過程的錯誤位置。
3.4授權執行權給相關的用戶或角色如果調試正確的存儲過程沒有進行授權，那就只有建立者本人才可以運行。所以作為套用系統的一部分的存儲過程也必須進行授權才能達到要求。在SQLPLUS下可以用GRANT命令來進行存儲過程的運行授權。
語法：
GRANTsystem_privilege|roleTOuser|role|PUBLIC
[WITHADMINOPTION]
或
GRANTobject_privilege|ALLcolumnONschema.object
TOuser|role|PUBLICWITHGRANTOPTION
其中
system_privilege:系統許可權
role:角色名
user:被授權的用戶名
object_privilege:所授予的許可權名字，可以是
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
Column:列名
schema:模式名
object:對象名
4.數據字典
USER_SOURCE用戶的存儲過程、函式的原始碼字典
DBA_SOURCE整個系統所有用戶的存儲過程、函式的原始碼字典
ALL_SOURCE當前用戶能使用的存儲過程(包括其她用戶授權)、函式的原始碼字典
USER_ERRORS用戶的存儲過程、函式的原始碼存在錯誤的信息字典
臨時表
（針對SQL2000/2005）可以創建本地和全局臨時表。本地臨時表僅在當前會話中可見；全局臨時表在所有會話中都可見。
本地臨時表的名稱前面有一個編號符(#table_name)，而全局臨時表的名稱前面有兩個編號符(##table_name)。
SQL語句使用CREATETABLE語句中為table_name指定的名稱引用臨時表：
CREATETABLE#MyTempTable(colaINTPRIMARYKEY)
INSERTINTO#MyTempTableVALUES(1)
如果本地臨時表由存儲過程創建或由多個用戶同時執行的應用程式創建，則SQLServer必須能夠區分由不同用戶創建的表。為此，SQLServer在內部為每個本地臨時表的表名追加一個數字後綴。存儲在tempdb資料庫的sysobjects表中的臨時表，其全名由CREATETABLE語句中指定的表名和系統生成的數字後綴組成。為了允許追加後綴，為本地臨時表指定的表名table_name不能超過116個字元。
除非使用DROPTABLE語句顯式除去臨時表，否則臨時表將在退出其作用域時由系統自動除去：
當存儲過程完成時，將自動除去在存儲過程中創建的本地臨時表。由創建表的存儲過程執行的所有嵌套存儲過程都可以引用此表。但調用創建此表的存儲過程的進程無法引用此表。
所有其它本地臨時表在當前會話結束時自動除去。
全局臨時表在創建此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯只在單個Transact-SQL語句的生存周期內保持。換言之，當創建全局臨時表的會話結束時，最後一條引用此表的Transact-SQL語句完成後，將自動除去此表。
