本文旨在把自己學(xué)到的有關(guān)存儲(chǔ)過程的知識(shí)和大家分享，并希望能夠幫助正在被存儲(chǔ)過程折磨的同學(xué)。

什么是存儲(chǔ)過程

官方定義：
A procedure is a subprogram that performs a specific action

A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation.

A subprogram is either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.

翻譯：
存儲(chǔ)過程是一個(gè)可以執(zhí)行特定行為的子程序

PL/SQL子程序是一個(gè)可以被重復(fù)調(diào)用的擁有名稱的PL/SQL塊（PL/SQL程序都是以塊為基本單位的）。如果這個(gè)子程序有參數(shù)，我們則可以在調(diào)用的時(shí)候賦予不同的參數(shù)

子程序可以是存儲(chǔ)過程或函數(shù)。通常情況下，你可以使用存儲(chǔ)過程來執(zhí)行一個(gè)動(dòng)作，使用函數(shù)來計(jì)算并返回一個(gè)值

總結(jié)：存儲(chǔ)過程對于不同的領(lǐng)域的人有不同的理解方式。可以把它理解為一個(gè)指令集，它可以幫助我們完成一系列復(fù)雜的數(shù)據(jù)操作，也可以把它看做一個(gè)專門處理SQL的批處理工具，在需要的時(shí)候執(zhí)行一些增刪改查的操作。

為什么學(xué)習(xí)存儲(chǔ)過程

相信在學(xué)習(xí)存儲(chǔ)過程的你一定已經(jīng)對普通SQL了如指掌了，簡單了解了存儲(chǔ)過程的概念之后，用普通的SQL與存儲(chǔ)過程進(jìn)行比較可以讓我們在工作中做出正確的選擇。兩者區(qū)別很大，詳細(xì)的對比可以問度娘，由于本文主講存儲(chǔ)過程，故羅列一些重要存儲(chǔ)過程的優(yōu)點(diǎn)如下：

• 降低網(wǎng)絡(luò)的通訊量。如果只是執(zhí)行簡單的SQL語句的話存儲(chǔ)過程和普通SQL沒有太大差別，但隨著時(shí)間的推移SQL量越來越大甚至達(dá)到上百行時(shí)，其優(yōu)越性明顯體現(xiàn)
• 提高執(zhí)行效率。我們都知道SQL是先編譯再執(zhí)行的，而存儲(chǔ)過程是預(yù)編譯在服務(wù)器中的，當(dāng)執(zhí)行的時(shí)候跳過編譯的環(huán)節(jié)效率自然會(huì)提高
• 可維護(hù)性高。更新存儲(chǔ)過程通常比更新，測試，重新部署需要較少的時(shí)間和精力

總之存儲(chǔ)過程是一個(gè)SQL提供的一個(gè)非常優(yōu)秀的功能，在工作中我們或多或少都會(huì)用到，學(xué)會(huì)存儲(chǔ)過程絕對會(huì)讓你受益匪淺。

PL/SQL存儲(chǔ)過程

基本語法

存儲(chǔ)過程在不同的數(shù)據(jù)庫語言中語法略有不同，本文針對ORACLE的PL/SQL，但其他數(shù)據(jù)庫語言也是大同小異

create_procedure

上圖是一個(gè)存儲(chǔ)過程的模型，由此可知一個(gè)最簡單的存儲(chǔ)過程必須包含以下關(guān)鍵字CREATE、PROCEDURE、存儲(chǔ)過程名稱、IS/AS、PL/SQLB標(biāo)準(zhǔn)執(zhí)行語句（BEGIN ... END;）。
通過不斷的實(shí)踐我們將會(huì)完全理解上圖的含義。首先從簡單的存儲(chǔ)過程開始(其功能是向EMP表中添加一條數(shù)據(jù))：

create        -- 存儲(chǔ)過程頭部區(qū)域開始 
or replace    --可選表示如果數(shù)據(jù)庫中已經(jīng)存在一條相同名稱的存儲(chǔ)過程就把它替換掉
procedure
proc_emp_create --存儲(chǔ)過程名稱 procedure_name
(
empno number, ename varchar2, job varchar2, mgr number, hiredate date, sal number, comm number, deptno number
) -- parameter_declaration 聲明參數(shù)（注意不需要寫長度），存儲(chǔ)過程頭部區(qū)域結(jié)束
as
--聲明區(qū)域，不需要聲明變量可以不寫
begin -- PL/SQL標(biāo)準(zhǔn)執(zhí)行語句
--執(zhí)行區(qū)域
  insert into emp values(empno, ename, job, mgr, hiredate, sal, comm, deptno);
end;

當(dāng)我們執(zhí)行上面的存儲(chǔ)過程之后這條存儲(chǔ)過程就被編譯到數(shù)據(jù)庫中了，進(jìn)入PLSQL Developer中的Procedures文件夾就可以看到我們剛才創(chuàng)建的存儲(chǔ)過程了

已經(jīng)編譯好的存儲(chǔ)過程

既然存儲(chǔ)過程已經(jīng)寫好并編譯完成了，接下來就是使用我們創(chuàng)建的存儲(chǔ)過程了。使用存儲(chǔ)過程有兩種方法：
第一種是直接在SQL窗口中，執(zhí)行SQL語句。

begin
  proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
end;

第二種是在命令窗口中使用execute命令

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott
 
SQL> execute   proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
 
PL/SQL procedure successfully completed
 
SQL> 

無論使用哪一種方法，我們都會(huì)發(fā)現(xiàn)在EMP表中已經(jīng)多了一條剛才插入的數(shù)據(jù)。也就是說，我們以后想要向EMP表中插入數(shù)據(jù)的話，就可以直接調(diào)用這條存儲(chǔ)過程來執(zhí)行插入操作。

從上面的例子可以看出，一個(gè)存儲(chǔ)過程可以分成三個(gè)區(qū)域

1. 頭部區(qū)域
   用于編寫最基本的存儲(chǔ)過程頭部標(biāo)記，定義是否要?jiǎng)?chuàng)建一個(gè)替代原有存儲(chǔ)過程的存儲(chǔ)過程；決定是否定義參數(shù)；定義參數(shù)的類型（in out inout）；定義執(zhí)行權(quán)限（Schema）。
2. 聲明區(qū)域
   用于聲明變量（要定義長度）包括cursor；
3. 執(zhí)行區(qū)域
   用于執(zhí)行業(yè)務(wù)邏輯代碼，可以使用條件語句（選擇、判斷、循環(huán)。。。）來進(jìn)行一些業(yè)務(wù)邏輯CRUD的處理

繼續(xù)實(shí)踐

了解了存儲(chǔ)過程的基本語法和用途之后，再通過一些簡單的實(shí)例可以幫助我們更好的理解存儲(chǔ)過程的語法和其含義。上面的例子簡單的完成了對EMP表的添加功能，接下來將會(huì)使用存儲(chǔ)過程對EMP進(jìn)行刪除、修改和查詢功能，實(shí)現(xiàn)完整的CRUD
D
創(chuàng)建用于根據(jù)EMPNO刪除EMP中一條數(shù)據(jù)的存儲(chǔ)過程

create or replace procedure proc_emp_delete(deleteid number) as
begin 
  delete from emp where empno = deleteid;
end;

執(zhí)行（兩種方法任選其一即可）

begin
  proc_emp_delete(7778);
end;

執(zhí)行完成之后，剛才我們在了解存儲(chǔ)過程語法的那條數(shù)據(jù)就被刪除了
U
接下來來寫更新的存儲(chǔ)過程，首先準(zhǔn)備一條數(shù)據(jù)

begin 
proc_emp_create(7778,'Alexander','analyst',6789,sysdate,4321,'',20);
end;

執(zhí)行之后剛才被我們刪掉的數(shù)據(jù)就又重新插入到EMP表中，接下來寫更新的存儲(chǔ)過程

create or replace procedure
proc_emp_update 
(
p_empno number, p_ename varchar2, p_job varchar2, p_mgr number, p_hiredate date, p_sal number, p_comm number, p_deptno number
) 
as
begin
  update emp set ename = p_ename,job = p_job,mgr = p_mgr,hiredate=p_hiredate,sal = p_sal,comm=p_comm,deptno = p_deptno where empno=p_empno;
end;

需要注意的是參數(shù)不能和字段表的名稱相同，接下來執(zhí)行

begin
  proc_emp_update(7778,'Alexander','analyst','6789',sysdate,4321,3000,10);
end;

再次查詢會(huì)發(fā)現(xiàn)Alexander的獎(jiǎng)金多了3000塊。
R
最后要做的就是查詢了，對于多條數(shù)據(jù)的查詢輸出要使用cursor，留在下篇文章中討論，我們先做一個(gè)最簡單的單條數(shù)據(jù)查詢

create or replace procedure proc_emp_read
as 
v_no number;--聲明變量
begin 
  select count(empno) into v_no from emp;--在執(zhí)行代碼塊里面查詢一定要使用into賦值
  dbms_output.put_line(v_no);
end;

接下來執(zhí)行

begin
  proc_emp_read;
end;

我的結(jié)果如下圖所示：

查詢EMP表總記錄數(shù)結(jié)果

至此，簡單的CRUD實(shí)踐就完成了，相信你已經(jīng)對存儲(chǔ)過程有了大概的理解并能寫出簡單存儲(chǔ)過程了。當(dāng)然這只是存儲(chǔ)過程最基本的使用方法，其高級特性（cursor、schema、控制語句、事務(wù)等）將在下篇文章中討論。