【存儲過程的概念】

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集。經(jīng)編譯后存儲在數(shù)據(jù)庫中。

存儲過程是數(shù)據(jù)庫中的一個重要對象，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。

存儲過程是由 流控制 和 SQL語句書寫的過程，這個過程經(jīng)編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中。

存儲過程 可由應(yīng)用程序通過一個調(diào)用來執(zhí)行，而且允許用戶聲明變量。

同時，存儲過程可以接收和輸出參數(shù)、返回執(zhí)行存儲過程的狀態(tài)值，也可以嵌套調(diào)用。

【存儲過程的功能】

這類語言主要提供以下功能，讓用戶可以設(shè)計出符合引用需求的程序：

① 變量說明

② ANSI兼容的SQL命令(如Select,Update….)

③ 一般流程控制命令(if…else…、while….)

④ 內(nèi)部函數(shù)

【存儲過程的優(yōu)點】

① 存儲過程的能力大大增強了SQL語言的功能和靈活性。

存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的 運算。

② 可保證數(shù)據(jù)的安全性和完整性。

理由1：通過存儲過程可以使沒有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫，從而保證數(shù)據(jù)的安全。

理由2：通過存儲過程可以使相關(guān)的動作在一起發(fā)生，從而可以維護數(shù)據(jù)庫的完整性。

③ 在運行存儲過程前，數(shù)據(jù)庫已對其進行了語法和句法分析，并給出了優(yōu)化執(zhí)行方案。

這種已經(jīng)編譯好的過程可極大地改善SQL語句的性能。

由于執(zhí)行SQL語句的大部分工作已經(jīng)完成，所以存儲過程能以極快的速度執(zhí)行。

④ 可以降低網(wǎng)絡(luò)的通信量。

理由：客戶端調(diào)用存儲過程只需要傳存儲過程名和相關(guān)參數(shù)即可，與傳輸SQL語句相比自然數(shù)據(jù)量少了很多。

⑤ 使體現(xiàn)企業(yè)規(guī)則的運算程序放入數(shù)據(jù)庫服務(wù)器中，以便：

1). 集中控制。

2). 當企業(yè)規(guī)則發(fā)生變化時在服務(wù)器中改變存儲過程即可，無須修改任何應(yīng)用程序。

企業(yè)規(guī)則的特點是要經(jīng)常變化，如果把體現(xiàn)企業(yè)規(guī)則的運算程序放入應(yīng)用程序中，則當企業(yè)規(guī)則發(fā)生變化時，就需要修改應(yīng)用程序工作量非常之大（修改、發(fā)行和安裝應(yīng)用程序）。

如果把體現(xiàn)企業(yè)規(guī)則的運算放入存儲過程中，則當企業(yè)規(guī)則發(fā)生變化時，只要修改存儲過程就可以了，應(yīng)用程序無須任何變化。

【存儲過程優(yōu)點概括】

① 存儲過程只在創(chuàng)造時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。

② 當對數(shù)據(jù)庫進行復雜操作時(如對多個表進行Update,Insert,Query,Delete時)，可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。

③ 存儲過程可以重復使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量。

④ 安全性高,可設(shè)定只有某些用戶才具有對指定存儲過程的使用權(quán)

【存儲過程缺點】

① 調(diào)試麻煩，但是用 PL/SQL Developer 調(diào)試很方便！彌補這個缺點。

② 移植問題，數(shù)據(jù)庫端代碼當然是與數(shù)據(jù)庫相關(guān)的。但是如果是做工程型項目，基本不存在移植問題。

③ 重新編譯問題，因為后端代碼是運行前編譯的，如果帶有引用關(guān)系的對象發(fā)生改變時，受影響的存儲過程、包將需要重新編譯（不過也可以設(shè)置成運行時刻自動編譯）。

④ 如果在一個程序系統(tǒng)中大量的使用存儲過程，到程序交付使用的時候隨著用戶需求的增加會導致數(shù)據(jù)結(jié)構(gòu)的變化，接著就是系統(tǒng)的相關(guān)問題了，最后如果用戶想維護該系統(tǒng)可以說是很難很難、而且代價是空前的，維護起來更麻煩。

【存儲過程的種類】

① 系統(tǒng)存儲過程：以sp_開頭,用來進行系統(tǒng)的各項設(shè)定.取得信息.相關(guān)管理工作。

② 本地存儲過程：用戶創(chuàng)建的存儲過程是由用戶創(chuàng)建并完成某一特定功能的存儲過程，事實上一般所說的存儲過程就是指本地存儲過程。

③ 臨時存儲過程：分為兩種存儲過程：

一是本地臨時存儲過程，以井字號(#)作為其名稱的第一個字符，則該存儲過程將成為一個存放在tempdb數(shù)據(jù)庫中的本地臨時存儲過程，且只有創(chuàng)建它的用戶才能執(zhí)行它;

二是全局臨時存儲過程，以兩個井字號(##)號開始，則該存儲過程將成為一個存儲在tempdb數(shù)據(jù)庫中的全局臨時存儲過程，全局臨時存儲過程一旦創(chuàng)建，以后連接到服務(wù)器的任意用戶都可以執(zhí)行它，而且不需要特定的權(quán)限。

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

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

【Oracle 數(shù)據(jù)庫的一個分頁存儲過程示例】

create or replace procedure fenye(

tableName in varchar2,--表名
pageSize in number,--1頁顯示的記錄數(shù)
pageNow in number,--當前為第幾頁
myrows out number, --總記錄數(shù)(一定要小心代碼中不能有Oracle的保留字，保留字是以青色顯示的)
myPageCount out number,--總頁數(shù)
p_cursor out testpackage.test_cursor--返回的記錄集
) is
--定義部分
--定義一個sql語句字符串
v_sql varchar2(1000);
--定義兩個整數(shù)
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--執(zhí)行部分(排序的話在最內(nèi)層加條件)
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
   ||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--把游標和sql關(guān)聯(lián)
open p_cursor for v_sql;
--要計算myrows和myPageCount
--組織一個sql
v_sql:='select count(*) from '|| tableName;
--執(zhí)行sql，并把返回的值，賦給myrows
execute immediate v_sql into myrows;
--計算myPageCount
if mod(myrows,pageSize)=0 then
  myPageCount:=myrows/pageSize;
else
  myPageCount:=myrows/pageSize+1;
end if;
--關(guān)閉游標(關(guān)了的話運行java程序竟然報游標已經(jīng)關(guān)閉的錯誤！)
--close p_cursor;
end;

【java調(diào)用存儲過程的示例】