數據庫存儲過程實例分析

數據庫存儲過程

　　數據庫存儲過程的實質就是部署在數據庫端的一組定義代碼以及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.....]

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 使用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 　　如果使用調試工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。^[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 用戶的存儲過程、函數的源代碼存在錯誤的信息字典 　　

臨時表

　?。ㄡ槍QL2000/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.觸發器的概念及作用 　　觸發器是一種特殊類型的存儲過程，它不同于我們前面介紹過的存儲過程。觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過存儲過程名字而被直接調用。當對某一表進行諸如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)的多個觸發器能夠對同一種數據操作采取多種不同的處理。 　　總體而言，觸發器性能通常比較低。當運行觸發器時，系統處理的大部分時間花費在參照其它表的這一處理上，因為這些表既不在內存中也不在數據庫設備上，而刪除表和插入表總是位于內存中?？梢娪|發器所參照的其它表的位置決定了操作要花費的時間長短。 　　2.觸發器的種類 　　SQL Server 2000 支持兩種類型的觸發器：AFTER 觸發器和INSTEAD OF 觸發器。其中AFTER 觸發器即為SQL Server 2000 版本以前所介紹的觸發器。該類型觸發器要求只有執行某一操作(Insert Update Delete) 之后，觸發器才被觸發，且只能在表上定義?？梢詾獒槍Ρ淼耐徊僮鞫x多個觸發器。對于AFTER 觸發器，可以定義哪一個觸發器被先觸發，哪一個被后觸發，通常使用系統過程sp_settriggerorder 來完成此任務。 　　INSTEAD OF 觸發器表示并不執行其所定義的操作(Insert、 Update、 Delete)，而僅是執行觸發器本身。既可在表上定義INSTEAD OF 觸發器，也可以在視圖上定義INSTEAD OF 觸發器，但對同一操作只能定義一個INSTEAD OF 觸發器。

常用格式

　　Create procedure procedue_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 　　@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output 　　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 　　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 proc 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