sql語(yǔ)句執行的時(shí)候要先編譯，然后執行。存儲過(guò)程（Stored Procedure）是一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲在數據庫中。用戶(hù)通過(guò)指定存儲過(guò)程的名字并給出參數（如果該存儲過(guò)程帶有參數）來(lái)執行它。存儲過(guò)程是數據庫中的一個(gè)重要對象，任何一個(gè)設計良好的數據庫應用程序都應該用到存儲過(guò)程。
一、存儲過(guò)程介紹
存儲過(guò)程是由流控制和SQL語(yǔ)句書(shū)寫(xiě)的過(guò)程，這個(gè)過(guò)程經(jīng)編譯和優(yōu)化后存儲在數據庫服務(wù)器中，應用程序使用時(shí)只要調用即可。在ORACLE中，若干個(gè)有聯(lián)系的過(guò)程可以組合在一起構成程序包。
存儲過(guò)程是利用SQL Server所提供的Tranact-SQL語(yǔ)言所編寫(xiě)的程序。Tranact-SQL語(yǔ)言是SQL Server提供專(zhuān)為設計數據庫應用程序的語(yǔ)言，它是應用程序和SQL Server數據庫間的主要程序式設計界面。它好比Oracle數據庫系統中的Pro-SQL和Informix的數據庫系統能夠中的Informix- 4GL語(yǔ)言一樣。這類(lèi)語(yǔ)言主要提供以下功能，讓用戶(hù)可以設計出符合引用需求的程序：
1)、變量說(shuō)明
2)、ANSI兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、內部函數
二、使用存儲過(guò)程有以下的優(yōu)點(diǎn)：
* 存儲過(guò)程的能力大大增強了SQL語(yǔ)言的功能和靈活性。存儲過(guò)程可以用流控制語(yǔ)句編寫(xiě)，有很強的靈活性，可以完成復雜的判斷和較復雜的 運算。
* 可保證數據的安全性和完整性。
# 通過(guò)存儲過(guò)程可以使沒(méi)有權限的用戶(hù)在控制之下間接地存取數據庫，從而保證數據的安全。
# 通過(guò)存儲過(guò)程可以使相關(guān)的動(dòng)作在一起發(fā)生，從而可以維護數據庫的完整性。
* 在運行存儲過(guò)程前，數據庫已對其進(jìn)行了語(yǔ)法和句法分析，并給出了優(yōu)化執行方案。這種已經(jīng)編譯好的過(guò)程可極大地改善SQL語(yǔ)句的性能。由于執行SQL語(yǔ)句的大部分工作已經(jīng)完成，所以存儲過(guò)程能以極快的速度執行。
* 可以降低網(wǎng)絡(luò )的通信量。
* 使體現企業(yè)規則的運算程序放入數據庫服務(wù)器中，以便：
# 集中控制。
# 當企業(yè)規則發(fā)生變化時(shí)在服務(wù)器中改變存儲過(guò)程即可，無(wú)須修改任何應用程序。企業(yè)規則的特點(diǎn)是要經(jīng)常變化，如果把體現企業(yè)規則的運算程序放入應用程序中，則當企業(yè)規則發(fā)生變化時(shí)，就需要修改應用程序工作量非常之大（修改、發(fā)行和安裝應用程序）。如果把體現企業(yè)規則的運算放入存儲過(guò)程中，則當企業(yè)規則發(fā)生變化時(shí)，只要修改存儲過(guò)程就可以了，應用程序無(wú)須任何變化。
三、存儲過(guò)程的種類(lèi)：
1)、系統存儲過(guò)程：以sp_開(kāi)頭,用來(lái)進(jìn)行系統的各項設定.取得信息.相關(guān)管理工作,
如 sp_help就是取得指定對象的相關(guān)信息
2)、擴展存儲過(guò)程以XP_開(kāi)頭,用來(lái)調用操作系統提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
3)、用戶(hù)自定義的存儲過(guò)程,這是我們所指的存儲過(guò)程
四、存儲過(guò)程的書(shū)寫(xiě)格式：
CREATE PROCEDURE [擁有者.]存儲過(guò)程名[;程序編號]
[(參數#1,…參數#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行
其中存儲過(guò)程名不能超過(guò)128個(gè)字。每個(gè)存儲過(guò)程中最多設定1024個(gè)參數
(SQL Server 7.0以上版本),參數的使用方法如下：
@參數名 數據類(lèi)型 [VARYING] [=內定值] [OUTPUT]
每個(gè)參數名前要有一個(gè)“@”符號,每一個(gè)存儲過(guò)程的參數僅為該程序內部使用,參數的類(lèi)型除了IMAGE外，其他SQL Server所支持的數據類(lèi)型都可使用。
[=內定值]相當于我們在建立數據庫時(shí)設定一個(gè)字段的默認值，這里是為這個(gè)參數設定默認值。[OUTPUT]是用來(lái)指定該參數是既有輸入又有輸出值的，也就是在調用了這個(gè)存儲過(guò)程時(shí)，如果所指定的參數值是我們需要輸入的參數，同時(shí)也需要在結果中輸出的，則該項必須為OUTPUT，而如果只是做輸出參數用，可以用CURSOR，同時(shí)在使用該參數時(shí)，必須指定VARYING和OUTPUT這兩個(gè)語(yǔ)句。
例子：
CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE ordered=@o_id
例子說(shuō)明：
該例子是建立一個(gè)簡(jiǎn)單的存儲過(guò)程order_tot_amt,這個(gè)存儲過(guò)程根據用戶(hù)輸入的定單ID號碼(@o_id),由定單明細表 (orderdetails)中計算該定單銷(xiāo)售總額[單價(jià)(Unitprice)*數量(Quantity)],這一金額通過(guò)@p_tot這一參數輸出給調用這一存儲過(guò)程的程序。
五、存儲過(guò)程的常用格式：
Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
解釋?zhuān)?br> output：表示此參數是可傳回的
with {recompile|encryption}
recompile:表示每次執行此存儲過(guò)程時(shí)都重新編譯一次
encryption:所創(chuàng )建的存儲過(guò)程的內容會(huì )被加密
如：
表book的內容如下
編號 書(shū)名 價(jià)格
001 C語(yǔ)言入門(mén) $30
002 PowerBuilder報表開(kāi)發(fā) $52
實(shí)例1:查詢(xún)表Book的內容的存儲過(guò)程
create proc query_book
as
select * from book
go
exec query_book
實(shí)例2：
加入一筆記錄到表book,并查詢(xún)此表中所有書(shū)籍的總金額
Create proc insert_book
@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
with encryption ---------加密
as
insert book(編號,書(shū)名，價(jià)格） Values(@param1,@param2,@param3)
select @param4=sum(價(jià)格) from book
go
執行例子：
declare @total_price money
exec insert_book '003','Delphi 控件開(kāi)發(fā)指南',$100,@total_price
print '總金額為'+convert(varchar,@total_price)
go
存儲過(guò)程的3種傳回值：
1)、以Return傳回整數
2)、以output格式傳回參數
3)、Recordset
傳回值的區別：
output和return都可在批次程式中用變量接收，而recordset則傳回到執行批次的客戶(hù)端中。
實(shí)例3：
設有兩個(gè)表為Product,Order,其表內容如下：
Product
產(chǎn)品編號 產(chǎn)品名稱(chēng) 客戶(hù)訂數
001 鋼筆 30
002 毛筆 50
003 鉛筆 100
Order
產(chǎn)品編號 客戶(hù)名 客戶(hù)訂金
001 南山區 $30
002 羅湖區 $50
003 寶安區 $4
請實(shí)現按編號為連接條件,將兩個(gè)表連接成一個(gè)臨時(shí)表,該表只含編號.產(chǎn)品名.客戶(hù)名.訂金.總金額,
總金額=訂金*訂數,臨時(shí)表放在存儲過(guò)程中
代碼如下：
Create proc temp_sale
as
select a.產(chǎn)品編號,a.產(chǎn)品名稱(chēng),b.客戶(hù)名,b.客戶(hù)訂金,a.客戶(hù)訂數* b.客戶(hù)訂金 as總金額
into #temptable from Product a inner join Order b on a.產(chǎn)品編號=b.產(chǎn)品編號
if @@error=0
print 'Good'
else
&n bsp; print 'Fail'
go
六、編寫(xiě)對數據庫訪(fǎng)問(wèn)的存儲過(guò)程：
數據庫存儲過(guò)程的實(shí)質(zhì)就是部署在數據庫端的一組定義代碼以及SQL。將常用的或很復雜的工作，預先用SQL語(yǔ)句寫(xiě)好并用一個(gè)指定的名稱(chēng)存儲起來(lái)，那么以后要叫數據庫提供與已定義好的存儲過(guò)程的功能相同的服務(wù)時(shí)，只需調用execute，即可自動(dòng)完成命令。
利用SQL的語(yǔ)言可以編寫(xiě)對于數據庫訪(fǎng)問(wèn)的存儲過(guò)程，其語(yǔ)法如下：
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} ][VARYING] [= default] [OUTPUT]
]
[,...n]
[WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[FOR REPLICATION]
AS
sql_statement [...n]
[ ]內的內容是可選項，而（）內的內容是必選項，
例：若用戶(hù)想建立一個(gè)刪除表tmp中的記錄的存儲過(guò)程Select_delete可寫(xiě)為：
Create Proc select_del As
Delete tmp
例：用戶(hù)想查詢(xún)tmp表中某年的數據的存儲過(guò)程
create proc select_query @year int as
select * from tmp where year=@year
在這里@year是存儲過(guò)程的參數
例：該存儲過(guò)程是從某結點(diǎn)n開(kāi)始找到最上層的父親結點(diǎn)，這種經(jīng)常用到的過(guò)程可以由存儲過(guò)程來(lái)?yè)?，在網(wǎng)頁(yè)中重復使用達到共享。
空：表示該結點(diǎn)為頂層結點(diǎn)
fjdid(父結點(diǎn)編號)
結點(diǎn)n 非空：表示該結點(diǎn)的父親結點(diǎn)號
dwmc（單位名稱(chēng)）
CREATE proc search_dwmc @dwidold int,@dwmcresult varchar(100) output
as
declare @stop int
declare @result varchar(80)
declare @dwmc varchar(80)
declare @dwid int
set nocount on
set @stop=1
set @dwmc=""
select @dwmc=dwmc,@dwid=convert(int,fjdid) from jtdw where id=@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) from jtdw where id=@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)
使用exec pro-name [pram1 pram2.....]
七、在SQL Server中執行存儲過(guò)程：
sql語(yǔ)句執行的時(shí)候要先編譯，然后執行。存儲過(guò)程就是編譯好了的一些sql語(yǔ)句。用的時(shí)候直接就可以用了。
在SQL Server的查詢(xún)分析器中，輸入以下代碼:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
select @tot_amt
以上代碼是執行order_tot_amt這一存儲過(guò)程，以計算出定單編號為1的定單銷(xiāo)售金額，我們定義@tot_amt為輸出參數，用來(lái)承接我們所要的結果。
存儲過(guò)程具有以下特點(diǎn)：
1.具有立即訪(fǎng)問(wèn)數據庫的能力；
2.是數據庫服務(wù)器端的執行代碼，在服務(wù)器執行操作時(shí)，減少網(wǎng)絡(luò )通訊，提高執行效率。
3.保證數據庫安全，自動(dòng)完成提前設定的作業(yè)。
本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請
點(diǎn)擊舉報。
