存儲過程

存儲過程（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，它存儲在資料庫中，一次編譯後永久有效，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要對象。

基本介紹

• 中文名：存儲過程
• 外文名：Stored Procedure

格式

sql中的存儲過程及相關介紹：

CREATE PROCEDURE [擁有者.]存儲過程名[;程式編號]

[(參數#1,…參數#1024)]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

]

[FOR REPLICATION]

AS 程式行

其中存儲過程名不能超過128個字。每個存儲過程中最多設定1024個參數

(SQL Server 7.0以上版本),參數的使用方法如下：

@參數名數據類型[VARYING] [=內定值] [OUTPUT]

每個參數名前要有一個“@”符號,每一個存儲過程的參數僅為該程式內部使用,參數的類型除了IMAGE外，其他SQL Server所支持的數據類型都可使用。

[內定值]相當於我們在建立資料庫時設定一個欄位的默認值，這裡是為這個參數設定默認值。[OUTPUT]是用來指定該參數是既有輸入又有輸出值的，也就是在調用了這個存儲過程時，如果所指定的參數值是我們需要輸入的參數，同時也需要在結果中輸出的，則該項必須為OUTPUT，而如果只是做輸出參數用，可以用CURSOR，同時在使用該參數時，必須指定VARYING和OUTPUT這兩個語句。

例子：

CREATE PROCEDURE order_tot_amt@o_id int,@p_tot int outputASSELECT @p_tot = sum(Unitprice*Quantity)FROM orderdetailsWHERE orderid=@o_idGO

例子說明：

該例子是建立一個簡單的存儲過程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遠程存儲過程

在SQL Server2005中，遠程存儲過程(Remote Stored Procedures)是位於遠程伺服器上的存儲過程，通常可以使用分散式查詢和EXECUTE命令執行一個遠程存儲過程。

5擴展存儲過程

擴展存儲過程(Extended Stored Procedures)是用戶可以使用外部程式語言編寫的存儲過程，而且擴展存儲過程的名稱通常以xp_開頭。

基本語法

創建存儲過程

create procedure sp_name

@[參數名] [類型],@[參數名] [類型]

as

begin

.........

end

以上格式還可以簡寫成：

create proc sp_name

@[參數名] [類型],@[參數名] [類型]

as

begin

.........

end

/*註：“sp_name”為需要創建的存儲過程的名字，該名字不可以以阿拉伯數字開頭*/

調用存儲過程

1.基本語法：exec sp_name [參數名]

刪除存儲過程

1.基本語法：

drop procedure sp_name

2.注意事項

(1)不能在一個存儲過程中刪除另一個存儲過程，只能調用另一個存儲過程

其他常用命令

1.show procedure status

顯示資料庫中所有存儲的存儲過程基本信息，包括所屬資料庫，存儲過程名稱，創建時間等

2.show create procedure sp_name

顯示某一個mysql存儲過程的詳細信息

3、exec sp_helptext sp_name

顯示你這個sp_name這個對象創建文本

觸發器

概念及作用

觸發器是一種特殊類型的存儲過程，它不同於我們前面介紹過的存儲過程。觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過存儲過程名字而被直接調用。當對某一表進行諸如Update、 Insert、 Delete 這些操作時，SQL Server 就會自動執行觸發器所定義的SQL 語句，從而確保對數據的處理必須符合由這些SQL 語句所定義的規則。

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜的參照完整性和數據的一致性。除此之外，觸發器還有其它許多不同的功能：

(1) 強化約束(Enforce restriction)

觸發器能夠實現比CHECK 語句更為複雜的約束。

(2) 跟蹤變化Auditing changes

觸發器可以偵測資料庫內的操作，從而不允許資料庫中未經許可的指定更新和變化。

(3) 級聯運行(Cascaded operation)。

觸發器可以偵測資料庫內的操作，並自動地級聯影響整個資料庫的各項內容。例如，某個表上的觸發器中包含有對另外一個表的數據操作(如刪除，更新，插入)而該操作又導致該表上觸發器被觸發。

(4) 存儲過程的調用(Stored procedure invocation)。

為了回響資料庫更新,觸發器可以調用一個或多個存儲過程，甚至可以通過外部過程的調用而在DBMS(資料庫管理系統)本身之外進行操作。

由此可見，觸發器可以解決高級形式的業務規則或複雜行為限制以及實現定製記錄等一些方面的問題。例如，觸發器能夠找出某一表在數據修改前後狀態發生的差異，並根據這種差異執行一定的處理。此外一個表的同一類型(Insert、 Update、 Delete)的多個觸發器能夠對同一種數據操作採取多種不同的處理。

總體而言，觸發器性能通常比較低。當運行觸發器時，系統處理的大部分時間花費在參照其它表的這一處理上，因為這些表既不在記憶體中也不在資料庫設備上，而刪除表和插入表總是位於記憶體中。可見觸發器所參照的其它表的位置決定了操作要花費的時間長短。

觸發器種類

SQL Server 2000 支持兩種類型的觸發器：AFTER 觸發器和INSTEAD OF 觸發器。其中AFTER觸發器即為SQL Server 2000 版本以前所介紹的觸發器。該類型觸發器要求只有執行某一操作(Insert Update Delete) 之後，觸發器才被觸發，且只能在表上定義。可以為針對表的同一操作定義多個觸發器。對於AFTER觸發器，可以定義哪一個觸發器被最先觸發，哪一個被最後觸發，通常使用系統過程sp_settriggerorder 來完成此任務。

INSTEAD OF觸發器表示並不執行其所定義的操作(Insert、 Update、 Delete)，而僅是執行觸發器本身。既可在表上定義INSTEAD OF觸發器，也可以在視圖上定義INSTEAD OF 觸發器，但對同一操作只能定義一個INSTEAD OF 觸發器。

常用格式

Create procedure procedure_name

[@parameter data_type][output]

[with]{recompile|encryption}

as

sql_statement

解釋：

output：表示此參數是可傳回的

with {recompile|encryption}

recompile:表示每次執行此存儲過程時都重新編譯一次

encryption:所創建的存儲過程的內容會被加密

如：

表book的內容如下

編號 書名 價格

001 C語言入門 $30

002 PowerBuilder報表開發 $52

實例1:查詢表Book的內容的存儲過程

create proc query_book

as

select * from book

go

exec query_book

實例2：

加入一筆記錄到表book,並查詢此表中所有書籍的總金額

Create proc insert_book@param1char(10),@param2varchar(20),@param3money,@param4moneyoutputwith encryption---------加密asinsert into book(編號,書名,價格)Values(@param1,@param2,@param3)select @param4=sum(價格) from bookgo執行例子：declare @total_price moneyexec insert_book '003','Delphi控制項開發指南',$100,@total_price outputprint '總金額為'+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

請實現按編號為連線條件,將兩個表連線成一個臨時表,該表只含編號.產品名.客戶名.訂金.總金額,

總金額=訂金*訂數,臨時表放在存儲過程中

代碼如下：

Create procedure temp_sale

as

select a.產品編號,a.產品名稱,b.客戶名,b.客戶訂金,a.客戶訂數* b.客戶訂金 as總金額

into #temptable from Product a inner join Order_ b on a.產品編號=b.產品編號-----此處要用別名

if @@error=0

print 'Good'

else

print 'Fail'

go

優缺點

優點

①重複使用。存儲過程可以重複使用，從而可以減少資料庫開發人員的工作量。

②減少網路流量。存儲過程位於伺服器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網路傳輸的數據量。

③安全性。參數化的存儲過程可以防止SQL注入式攻擊，而且可以將Grant、Deny以及Revoke許可權套用於存儲過程。

簡單講：

1.存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。

2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時)，可將此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。

3.存儲過程可以重複使用,可減少資料庫開發人員的工作量

4.安全性高,可設定只有某些用戶才具有對指定存儲過程的使用權

有一點需要注意的是，一些網上盛傳的所謂的存儲過程要比sql語句執行更快的說法，實際上是個誤解，並沒有根據，包括微軟內部的人也不認可這一點，所以不能作為正式的優點，希望大家能夠認識到這一點。

缺點

1：調試麻煩，但是用 PL/SQL Developer 調試很方便！彌補這個缺點。

2：移植問題，資料庫端代碼當然是與資料庫相關的。但是如果是做工程型項目，基本不存在移植問題。

3：重新編譯問題，因為後端代碼是運行前編譯的，如果帶有引用關係的對象發生改變時，受影響的存儲過程、包將需要重新編譯（不過也可以設定成運行時刻自動編譯）。

4： 如果在一個程式系統中大量的使用存儲過程，到程式交付使用的時候隨著用戶需求的增加會導致數據結構的變化，接著就是系統的相關問題了，最後如果用戶想維護該系統可以說是很難很難、而且代價是空前的，維護起來更麻煩。

實例

資料庫存儲過程

資料庫存儲過程的實質就是部署在資料庫端的一組定義代碼以及SQL。將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱存儲起來，那么以後要叫資料庫提供與已定義好的存儲過程的功能相同的服務時，只需調用execute，即可自動完成命令。

利用SQL的語言可以編寫對於資料庫訪問的存儲過程，其語法如下：

CREATE PROC[EDURE] procedure_name [;number]

[

{@parameter data_type} ][VARYING] [= default] [OUTPUT]

]

[,...n]

[WITH

{

RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION

}

]

[FOR REPLICATION]

AS

sql_statement [...n]

[ ]內的內容是可選項，而（）內的內容是必選項，

例：若用戶想建立一個刪除表tmp中的記錄的存儲過程Select_delete可寫為：

Create Proc select_del As

Delete tmp

例：用戶想查詢tmp表中某年的數據的存儲過程

create proc select_query @year int as

select * from tmp where year=@year

在這裡@year是存儲過程的參數

例：該存儲過程是從某結點n開始找到最上層的父親結點，這種經常用到的過程可以由存儲過程來擔當，在網頁中重複使用達到共享。

空：表示該結點為頂層結點

fjdid(父結點編號)

結點n 非空：表示該結點的父親結點號

dwmc（單位名稱）

CREATE proc search_dwmc@dwidoldint,@dwmcresult varchar(100) outputasdeclare @stopintdeclare @result varchar(80)declare @dwmc varchar(80)declare @dwidintset nocountonset @stop=1set @dwmc=""select @dwmc = dwmc, @dwid = convert(int,fjdid) from jtdw where id = @dwidoldset @result = rtrim(@dwmc)if @dwid = 0set @stop = 0while(@stop = 1)and(@dwid<>0)beginset @dwidold = @dwidselect @dwmc = dwmc, @dwid = convert(int,fjdid) from jtdw where id = @dwidoldif @@rowcount = 0set @dwmc = ""elseset @result= @dwmc + @resultif(@dwid = 0) or (@@rowcount = 0)set @stop = 0elsecontinueendset @dwmcresult = rtrim(@result)使用execpro-name[pram1pram2.....]

SQL Server中存儲過程

sql語句執行的時候要先編譯，然後執行。存儲過程就是編譯好了的一些sql語句。用的時候直接就可以用了。

在SQL Server的查詢分析器中，輸入以下代碼:

declare @tot_amt int

execute order_tot_amt 1,@tot_amt output

select @tot_amt

以上代碼是執行order_tot_amt這一存儲過程，以計算出訂單編號為1的訂單銷售金額，我們定義@tot_amt為輸出參數，用來承接我們所要的結果。

Oracle中的存儲過程

1.創建過程

與其它的資料庫系統一樣，Oracle的存儲過程是用PL/SQL語言編寫的能完成一定處理功能的存儲在資料庫字典中的程式。

語法:

create [or replace] procedure procedure_name

[ (argment [ { in| in out }] type,

argment [ { in | out | in out } ] type

{ is | as }

<類型.變數的說明>

(注:不用 declare語句 )

Begin

<執行部分>

exception

<可選的異常處理說明>

end;

1.1 這裡的IN表示向存儲過程傳遞參數，OUT表示從存儲過程返回參數。而IN OUT 表示傳遞參數和返回參數；

1.2 在存儲過程內的參數只能指定參數類型；不能指定長度；

1.3 在AS或IS 後聲明要用到的變數名稱和變數類型及長度；

1.4 在AS或IS 後聲明變數不要加declare 語句。

2.使用過程

存儲過程建立完成後，只要通過授權，用戶就可以在SQLPLUS 、Oracle開發工具或第三方開發工具來調用運行。Oracle 使用CALL 語句來實現對存儲過程的調用。

語法：

call procedure_name( parameter1, parameter2…);

3.開發過程

如今的幾大資料庫廠商提供的編寫存儲過程的工具都沒有統一，雖然它們的編寫風格有些相似，但由於沒有標準，所以各家的開發調試過程也不一樣。下面編寫PL/SQL存儲過程、函式、包及觸發器的步驟如下：

3.1 編輯存儲過程源碼使用文字編輯處理軟體編輯存儲過程源碼，要用類似WORD文字處理軟體進行編輯時，要將源碼存為文本格式。

3.2 對存儲過程程式進行解釋在SQLPLUS或用調試工具將 存儲過程程式進行解釋；

在SQL>下調試，可用start 或get 等Oracle命令來啟動解釋。如：

SQL>start c:\stat1.sql

如果使用調試工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。

3.3 調試源碼直到正確我們不能保證所寫的存儲過程達到一次就正確。所以這裡的調試是每個程式設計師必須進行的工作之一。在SQLPLUS下來調試主要用的方法是：

1．使用 SHOW ERROR命令來提示源碼的錯誤位置；

2．使用 USER_ERRORS數據字典來查看各存儲過程的錯誤位置。

3.4 授權執行權給相關的用戶或角色如果調試正確的存儲過程沒有進行授權，那就只有建立者本人才可以運行。所以作為套用系統的一部分的存儲過程也必須進行授權才能達到要求。 在SQLPLUS下可以用GRANT命令來進行存儲過程的運行授權。

語法：

GRANT system_privilege | role TO user | role | PUBLIC

[WITH ADMIN OPTION]

或

GRANT object_privilege | ALL column ON schema.object

TO user | role | PUBLIC WITH GRANT OPTION

其中

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 語句使用 CREATE TABLE 語句中為 table_name 指定的名稱引用臨時表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

如果本地臨時表由存儲過程創建或由多個用戶同時執行的應用程式創建，則 SQL Server 必須能夠區分由不同用戶創建的表。為此，SQL Server 在內部為每個本地臨時表的表名追加一個數字後綴。存儲在 tempdb 資料庫的 sysobjects 表中的臨時表，其全名由 CREATE TABLE 語句中指定的表名和系統生成的數字後綴組成。為了允許追加後綴，為本地臨時表指定的表名 table_name 不能超過 116 個字元。

除非使用 DROP TABLE 語句顯式除去臨時表，否則臨時表將在退出其作用域時由系統自動除去：

當存儲過程完成時，將自動除去在存儲過程中創建的本地臨時表。由創建表的存儲過程執行的所有嵌套存儲過程都可以引用此表。但調用創建此表的存儲過程的進程無法引用此表。

所有其它本地臨時表在當前會話結束時自動除去。

全局臨時表在創建此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯只在單個 Transact-SQL 語句的生存周期內保持。換言之，當創建全局臨時表的會話結束時，最後一條引用此表的 Transact-SQL 語句完成後，將自動除去此表。