oracle儲存過程的作用有哪些

oracle儲存過程的作用有：1、簡化複雜的操作，降低總體開發成本；2、增加資料獨立性；3、可以有效降低錯誤出現的機率，提高安全性；4、提高效能。

本教學操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。

Oracle儲存過程在實際資料庫開發過程當中會經常使用到，作為一個資料庫開發者必備的技能，它有著SQL語句不可替代的作用。所謂儲存過程，就是一段儲存在資料庫中執行某塊業務功能的程式模組。它是由一段或者多段的PL/SQL程式碼塊或者SQL語句組成的一系列程式碼塊。

儲存過程的作用（優點）：

• 簡化複雜的操作，降低總體開發成本。

  儲存過程把實際執行的業務邏輯PL/SQL塊和多條SQL語句封裝到儲存過程當中，其它開發者只需要呼叫寫好的過程，獲取想要的結果，不需要重新理解業務。把業務抽取出來由專門的人來編寫。
  

• 增加資料獨立性。

  它的作用和檢視的作用類似，假如表的基礎資料發生變化，我們只需要修改過程當中的程式碼，而不需要修改呼叫程式。使得使用者程式不需要直接面對基礎資料進行編寫程式碼。使得程式碼內聚程度更高，耦合度更低。

• 提高安全性。

  使用儲存過程有效降低了錯誤出現的機率。如果不使用儲存過程要實現某項操作可能需要執行多條單獨的SQL語句，而過多的執行步驟很可能造成更高的出現錯誤機率。

• 提高效能。

  實際開發過程中，一個業務模組功能的開發可能需要用到多個SQL語句，多個PL/SQL程式塊才能解決問題。把它編寫進過程，Oracle只需要一次編譯，以後隨時可以呼叫。如果不使用過程，直接把許多SQL語句寫程序式當中，需要多次編譯，而且需要多次連線資料庫，大大的降低了效能。

建立Oracle儲存過程語法：

create [or replace] procedure 過程名
( p1 in|out datatype,
  p2 in|out datatype,
  ...
  pn in|out datatype
    
) is 
    
    ....--宣告部分
    begin
    
    ....--過程體
    end;

語法解析：

1、procedure 關鍵字是建立儲存過程的命令。

2、create [or replace] :如果儲存過程已經存在則覆蓋替代原有的過程。

3、in|out ：儲存過程具有入參和出參兩種引數選擇，in表示的是入參，out表示的是出參，在使用過程的時候，入參必須得有對應的變數傳入，出參得有對應的變數接收。

4、datatype表示出入參變數對應的資料型別。

5、is後面跟著的是過程當中使用到的宣告變數。

6、begin...end 中間編寫的就是儲存過程的具體操作。

例子1、建立一個儲存過程計算學生某一個課程中成績在班中的排名，使用儲存過程進行計算，返回對應的排名，程式碼如下：

create or replace procedure sp_score_pm(
p_in_stuid in varchar2,--學號
p_in_courseid in  varchar2, --課程ID
p_out_pm out number--排名
)
is
ls_score number:=0;
ls_pm number:=0;
begin
  --獲取該學生的成績
  select t.score into ls_score from score t
   where t.stuid = p_in_stuid
     and t.courseid = p_in_courseid;
  --獲取成績比該學生高的人數
  select count(1) into ls_pm from score t
   where t.courseid = p_in_courseid
   and  t.score>ls_score;
   --得到該學生的成績排名
   p_out_pm:=ls_pm+1;
exception
  when no_data_found  then
     dbms_output.put_line('該學生的課程：'||p_in_courseid|| '的成績在成績表中找不到');
end;

通過上面的程式碼，我們可以直接在SQL視窗執行編譯，編譯成功後，我們就可以呼叫儲存過程來獲取學生對應的課程成績排名了，儲存過程需要出入參賦值，因此我們可以通過PL/SQL語句塊進行測試，程式碼如下：

declare
ls_pm number;--排名
begin
  --SC201801001
  sp_score_pm('SC201801001','R20180101',ls_pm);
  dbms_output.put_line('學號：SC201801001,課程號：R20180101 的成績排名是：'||ls_pm);
  sp_score_pm('SC201801001','R20180102',ls_pm);
  dbms_output.put_line('學號：SC201801001,課程號：R20180102 的成績排名是：'||ls_pm);
  --SC201801002
  sp_score_pm('SC201801002','R20180101',ls_pm);
  dbms_output.put_line('學號：SC201801002,課程號：R20180101 的成績排名是：'||ls_pm);
  sp_score_pm('SC201801002','R20180102',ls_pm);
  dbms_output.put_line('學號：SC201801002,課程號：R20180102 的成績排名是：'||ls_pm);
  
end;

結果如下：