標籤:SQL語言

存儲過程(Stored Procedure)是在大型資料庫系統中,一組為了完成特定功能的SQL 語句集,經編譯后存儲在資料庫中,用戶通過指定存儲過程的名字並給出參數(如果該存儲過程帶有參數)來執行它。

1基本信息

格式
CREATE PROCEDURE [擁有者.]存儲過程名[;程序編號] sql中的存儲過程及相關介紹
[(參數#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系統存儲過程
以sp_開頭,用來進行系統的各項設定.取得信息.相關管理工作。
3臨時存儲過程
分為兩種存儲過程:
一是本地臨時存儲過程,以井字型大小(#)作為其名稱的第一個字元,則該存儲過程將成為一個存放在tempdb資料庫中的本地臨時存儲過程,且只有創建它的用戶才能執行它;
二是全局臨時存儲過程,以兩個井字型大小(##)號開始,則該存儲過程將成為一個存儲在tempdb資料庫中的全局臨時存儲過程,全局臨時存儲過程一旦創建,以後連接到伺服器的任意用戶都可以執行它,而且不需要特定的許可權。
擴展存儲過程
擴展存儲過程(Extended Stored Procedures)是用戶可以使用外部程序語言編寫的存儲過程,而且擴展存儲過程的名稱通常以xp_開頭。

2基本語法

調用存儲過程
1.基本語法:exec sp_name [參數名]
其他常用命令
1.show procedure status
顯示資料庫中所有存儲的存儲過程基本信息,包括所屬資料庫,存儲過程名稱,創建時間等
2.show create procedure sp_name
顯示某一個mysql存儲過程的詳細信息
3、exec sp_helptex tsp_name
顯示你這個sp_name這個對象創建文本

3觸發器

觸發器種類
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 觸發器。
優點
* 存儲過程的能力大大增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫,有很強的靈活性,可以完成複雜的判斷和較複雜的 運算。
* 可保證數據的安全性和完整性。
# 通過存儲過程可以使沒有許可權的用戶在控制之下間接地存取資料庫,從而保證數據的安全。
# 通過存儲過程可以使相關的動作在一起發生,從而可以維護資料庫的完整性。
* 在運行存儲過程前,資料庫已對其進行了語法和句法分析,並給出了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。由於執行SQL語句的大部分工作已經完成,所以存儲過程能以極快的速度執行。
* 可以降低網路的通信量。
* 使體現企業規則的運算程序放入資料庫伺服器中,以便:
# 集中控制。
# 當企業規則發生變化時在伺服器中改變存儲過程即可,無須修改任何應用程序。企業規則的特點是要經常變化,如果把體現企業規則的運算程序放入應用程序中,則當企業規則發生變化時,就需要修改應用程序工作量非常之大(修改、發行和安裝應用程序)。如果把體現企業規則的運算放入存儲過程中,則當企業規則發生變化時,只要修改存儲過程就可以了,應用程序無須任何變化。
簡單講:
1.存儲過程只在創造時進行編譯,以後每次執行存儲過程都不需再重新編譯,而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。
2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時),可將此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用。
3.存儲過程可以重複使用,可減少資料庫開發人員的工作量
4.安全性高,可設定只有某些用戶才具有對指定存儲過程的使用權
有一點需要注意的是,一些網上盛傳的所謂的存儲過程要比sql語句執行更快的說法,實際上是個誤解,並沒有根據,包括微軟內部的人也不認可這一點,所以不能作為正式的優點,希望大家能夠認識到這一點。
資料庫存儲過程
資料庫存儲過程的實質就是部署在資料庫端的一組定義代碼以及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 @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.....]
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 使用EXECUTE 語句來實現對存儲過程的調用。
語法:
EXEC[UTE] 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 語句完成後,將自動除去此表。
上一篇[矩陣變換]    下一篇 [偽代碼]

相關評論

同義詞:暫無同義詞