反饋

存儲過程

存儲過程（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 output
AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE orderid=@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遠程存儲過程

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

5擴展存儲過程

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

存儲過程基本語法

存儲過程創建存儲過程

create procedure sp_name

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

begin

.........

end

以上格式還可以簡寫成：

create proc sp_name

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

begin

.........

end

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

存儲過程調用存儲過程

存儲過程可以在三種環境下被調用：

command命令下，基本語法為：exec sp_name [參數名]；
SQL環境下，基本語法為：call sp_name [參數名]；
PL/SQL環境下，基本語法為：begin sp_name [參數名] end；

存儲過程刪除存儲過程

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}

sql_statement

解釋：

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

with {recompile|encryption}

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

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

如：

表book的內容如下

編號書名價格

001 C語言入門 $30

002 PowerBuilder報表開發 $52

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

create proc query_book

select * from book

exec query_book

實例2：

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

Create proc insert_book
@param1char(10),@param2varchar(20),@param3money,@param4moneyoutput
with encryption---------加密
as
insert into book(編號,書名,價格)Values(@param1,@param2,@param3)
select @param4=sum(價格) from book
go
執行例子：
declare @total_price money
exec insert_book '003','Delphi控件開發指南',$100,@total_price output
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

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

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

代碼如下：

Create procedure temp_sale

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'

存儲過程優缺點

存儲過程優點

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

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

③安全性。參數化的存儲過程可以防止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]

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) output
as
declare @stopint
declare @result varchar(80)
declare @dwmc varchar(80)
declare @dwidint
set nocounton
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)
使用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

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

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 語句完成後，將自動除去此表。

參考資料

1. 《Oracle9i數據庫基礎》

存儲過程的概述圖（1張）

詞條統計

瀏覽次數：次
編輯次數：116次歷史版本
最近更新： Lemon西厢（2023-12-23）

1 格式
2 功能
3 種類
4 基本語法: 4.1 創建存儲過程; 4.2 調用存儲過程; 4.3 刪除存儲過程; 4.4 其他常用命令
5 觸發器: 5.1 概念及作用; 5.2 觸發器種類; 5.3 常用格式
6 優缺點: 6.1 優點; 6.2 缺點
7 實例