Oracle PL/SQL (10) - 存儲過程的創(chuàng)建和調(diào)用

子程序包括存儲過程和函數(shù)。
子程序包括：
　　1、聲明部分：聲明部分包括類型、游標(biāo)、常量、變量、異常和嵌套子程序的聲明。這些項(xiàng)都是局部的，在退出后就不復(fù)存在。
　　2、可執(zhí)行部分：可執(zhí)行部分包括賦值、控制執(zhí)行過程以及操縱ORacle數(shù)據(jù)的語句。
　　3、異常處理部分：　　異常處理部分包括異常處理程序，負(fù)責(zé)處理執(zhí)行存儲過程中出現(xiàn)的異常。

存儲過程是執(zhí)行某些操作的子程序，是執(zhí)行特定任務(wù)的模塊。從根本上講，存儲過程就是PLSQL塊，它可以被賦予參數(shù)，存儲在數(shù)據(jù)庫中，然后由一個(gè)應(yīng)用程序或其他PLSQL程序調(diào)用。

存儲過程存放在數(shù)據(jù)庫服務(wù)器中，而且是已經(jīng)編譯好的，且在服務(wù)器端執(zhí)行，因此執(zhí)行效率高；
存儲過程把商業(yè)邏輯固化在存儲過程中，還隱藏了數(shù)據(jù)，因此增強(qiáng)了數(shù)據(jù)安全性；
存儲過程增加了程序開發(fā)的靈活性和模塊化；
存儲過程是存儲在服務(wù)器端，且在服務(wù)器端執(zhí)行，減少了網(wǎng)絡(luò)通信量。

create or replace procedure 存儲過程名稱
(
--定義輸入、輸出參數(shù)--
參數(shù)名1 in 參數(shù)類型,
參數(shù)名2 in 參數(shù)類型,
參數(shù)名3 in 參數(shù)類型,
參數(shù)名4 out 參數(shù)類型
)
as
--定義變量--
--變量名 變量數(shù)據(jù)類型；如：
-- numCount integer; 
begin   
    --處理方法-
end;

CREATE OR REPLACE PROCEDURE 存儲過程名(param1 in type,param2 out type)
　IS
　變量1 類型(值范圍);
　變量2 類型(值范圍);　　
　BEGIN
　　　select count(*) into 變量1 from 表名 where 列名=param1;
　　　if (判斷條件) then
　　　　　select 列名 into 變量2 from 表名 where 列名=param1;
　　　　　DBMS_OUTPUT.put_line('打印信息');
　　　Elsif (判斷條件) then
　　　　　dbms_output.put_line('打印信息');
　　　Else
　　　　　Raise 異常名 (NO_DATA_FOUND);
　　　End if;
　Exception
　　　　　When others then
　　　　　　　Rollback; 　　
　END;

創(chuàng)建一個(gè)最簡單的存儲過程。

create or replace procedure pro_1(id varchar2)
is
   name varchar2(20); -- 聲明一個(gè)變量
begin
   -- 以下就是存儲過程的主體部分
   dbms_output.put_line('id:' || id || ' name:' || name);
end;

1、create or replace表示如果這個(gè)存儲過程不存在就創(chuàng)建一個(gè)新的存儲過程，而如果這個(gè)存儲過程存在了，就覆蓋這個(gè)存儲過程；
2、id varchar2是傳遞的一個(gè)參數(shù)，默認(rèn)是IN類型。
3、在存儲過程（PROCEDURE）和函數(shù)(FUNCTION)中使用IS和AS并沒有太大區(qū)別，在視圖（VIEW）中只能用AS不能用IS，在游標(biāo)(CURSOR)中只能用IS不能用AS。后面一般跟變量聲明。
4、begin和end之間是PL/SQL程序體，其中exception來指定失敗處理流程。
調(diào)用存儲過程。
一般使用的比較多的是在PL/SQL中調(diào)用存儲過程，在PL/SQL調(diào)用存儲過程就好比調(diào)用一個(gè)函數(shù)一樣，例如：

begin
   pro_1(00813045);
end;

查詢存儲過程

select * from user_source where name='pro_1';

查看存儲過程的狀態(tài)

select * from user_objects where object_name = 'pro_1';

刪除存儲過程

drop procedure pro_1;

對于參數(shù)的模式有以下三種：
IN參數(shù)
語法：參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值;
定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程。在調(diào)用存儲過程時(shí)，主程序的實(shí)際參數(shù)可以是常量、有值變量或表達(dá)式等。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。如果在調(diào)用存儲過程時(shí)不指明參數(shù)，則參數(shù)變量取默認(rèn)值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進(jìn)行賦值。

OUT參數(shù)
語法：參數(shù)名 OUT 數(shù)據(jù)類型;
定義一個(gè)輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)，即變量從存儲過程中返回值給主程序。
在調(diào)用存儲過程時(shí)，主程序的實(shí)際參數(shù)只能是一個(gè)變量，而不能是常量或表達(dá)式。在存儲過程中，參數(shù)變量只能被賦值而不能將其用于賦值，在存儲過程中必須給輸出變量至少賦值一次。

IN OUT參數(shù)
語法：參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值;
定義一個(gè)輸入、輸出參數(shù)變量，兼有以上兩者的功能。在調(diào)用存儲過程時(shí)，主程序的實(shí)際參數(shù)只能是一個(gè)變量，而不能是常量或表達(dá)式。DEFAULT 關(guān)鍵字為可選項(xiàng)，用來設(shè)定參數(shù)的默認(rèn)值。在存儲過程中，變量接收主程序傳遞的值，同時(shí)可以參加賦值運(yùn)算，也可以對其進(jìn)行賦值。在存儲過程中必須給變量至少賦值一次。

實(shí)例1，沒有參數(shù)的存儲過程
根據(jù)不同的廠牌 把對應(yīng)的車系數(shù)據(jù)輸出

create or replace procedure series_cur_prc
 as
  v_sql long;
  v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type;

 cursor cur_make is 
 select distinct s.vehicle_make_id  from t_md_vehicle_series s ;
 
 cursor cur_seriesinfo(v_make_id varchar2) is 
    select s.vehicle_series_name 
    from t_md_vehicle_series s where s.vehicle_make_id=v_make_id ;

begin
      for  M_CUR IN cur_make LOOP
        v_sql:='select m.vehicle_manuf_make_name  from t_md_vehicle_make m where m.vehicle_make_id='''||M_CUR.vehicle_make_id||'''';
        EXECUTE IMMEDIATE v_sql into  v_vehicle_make_name;
        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
        DBMS_OUTPUT.PUT_LINE(v_vehicle_make_name || ':');
        for P_CUR IN cur_seriesinfo(M_CUR.vehicle_make_id) LOOP
              DBMS_OUTPUT.PUT_LINE('車系名稱： ' || P_CUR.vehicle_series_name );
        end loop; 
      END LOOP;
end ;
--存儲過程調(diào)用(下面只是調(diào)用存儲過程語法)
BEGIN 
   series_cur_prc;
END;
image.png

輸出結(jié)果：


image.png

實(shí)例2，僅帶傳入?yún)?shù)的過程
根據(jù)輸入的車輛品牌 從表車系表中搜索符合要求的數(shù)據(jù)，并將其輸出

create or replace procedure series_intype_pro(parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type)
 as
  v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type;

 cursor cur_make is 
 select  m.vehicle_make_id  from t_md_vehicle_make m where m.vehicle_manuf_make_name=parm_make_name;
 
 cursor cur_seriesinfo(v_make_id varchar2) is 
    select s.vehicle_series_id,s.vehicle_series_name 
    from t_md_vehicle_series s where s.vehicle_make_id=v_make_id ;

begin
      for  M_CUR IN cur_make LOOP
       
        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
        DBMS_OUTPUT.PUT_LINE(parm_make_name || ':');
        for P_CUR IN cur_seriesinfo(M_CUR.vehicle_make_id) LOOP
              DBMS_OUTPUT.PUT_LINE('車系名稱： ' || P_CUR.vehicle_series_name||'車系id： ' || P_CUR.vehicle_series_id);
        end loop; 
      END LOOP;
end ;

存儲過程調(diào)用

declare
 v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type; 
begin
 v_vehicle_make_name:='一汽大眾';
 series_intype_pro(v_vehicle_make_name);
end;
image.png

輸出結(jié)果：


image.png

代碼解析：
第1～2行表示創(chuàng)建存儲過程。存儲過程包括IN類型的參數(shù)，表示該參數(shù)為輸入類型的參數(shù)。此時(shí)可以省略關(guān)鍵字IN。
第3行表示聲明存儲過程的內(nèi)部變量。其中，v_vehicle_make_name表示品牌名稱。
第5～6行表示創(chuàng)建cur_make游標(biāo),通過品牌名稱查詢品牌ID。
第8～10行表示創(chuàng)建cur_seriesinfo游標(biāo),帶參游標(biāo)，通過傳入的品牌ID查詢車系表中對應(yīng)的車系ID,名稱
第12～16行表示循環(huán)cur_make游標(biāo)，并輸出品牌名稱。
第17～19行表示cur_seriesinfo游標(biāo)，并將品牌ID傳入，根據(jù)品牌ID從車系表中中查詢數(shù)據(jù)，并把得到的數(shù)據(jù)輸出到屏幕。

實(shí)例3，帶輸入，輸出參數(shù)的存儲過程
根據(jù)輸入的車輛品牌查詢出對應(yīng)的車輛品牌id，并將得到車輛品牌id放到輸出參數(shù)中

create or replace procedure make_outype_pro
(
  parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type,
  parm_make_id out t_md_vehicle_make.vehicle_make_id%type
)
 as
begin
        select  m.vehicle_make_id into parm_make_id from t_md_vehicle_make m where m.vehicle_manuf_make_name=parm_make_name; 
        DBMS_OUTPUT.PUT_LINE('車輛品牌： ' ||parm_make_name||'車輛品牌id： ' ||parm_make_id);
end ;

存儲過程調(diào)用
根據(jù)輸入的車輛品牌以及 車系id的部分 從表車系表 中查詢符合要求的數(shù)據(jù)并輸出到屏幕

create or replace procedure serise_clouttype_pro(
    parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type,
    parm_series  varchar2
)
as  
  cursor cur_series(v_series_id t_md_vehicle_series.vehicle_series_id%type) is
    select s.vehicle_series_name from t_md_vehicle_series s where s.vehicle_series_id like v_series_id||'%';
  parm_make_id    t_md_vehicle_make.vehicle_make_id%type;
begin
   make_outype_pro(parm_make_name,parm_make_id);

   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
  for S_CUR in cur_series(parm_series) loop
      DBMS_OUTPUT.PUT_LINE('車系名稱： ' ||S_CUR.vehicle_series_name);
  end loop;

end;

測試調(diào)用


image.png

輸出結(jié)果：


image.png

實(shí)例4，在存儲過程調(diào)用函數(shù)
創(chuàng)建加、減、乘、除計(jì)算的存儲過程
輸入?yún)?shù)： 數(shù)字1，數(shù)字2，計(jì)算類型
輸出參數(shù)： 數(shù)字3

create or replace function fun_Test(num_1  in number, num_2 in number, num_3 in number)
return number
as
 num_A   number:=num_1;
 num_B   number:=num_2;
 numType number:=num_3;
 num_C   number;
begin
if numType=1 then
      return num_C := num_A + num_B;
    elsif numType=2 then
      return num_C := num_A - num_B;
    elsif numType=3 then
     return  num_C := num_A * num_B; 
    elsif numType=4 then
     return  num_C := num_A / num_B; 
    end if;
END fun_Test;

create or replace procedure Proc_Test1
(
--定義輸入、輸出參數(shù)--
num_A in integer,
num_B in integer,
numType in integer,
num_C out integer
)
as
--定義變量--
-- numStr varchar(20);  
begin   
    --調(diào)用函數(shù) 并賦值到num_C
    num_C:=fun_Test(num_A,num_B,numType);
end;

--調(diào)用存儲過程---
declare 
  num_C integer;
begin
  Proc_Test1(12,2,4,num_C);
  dbms_output.put_line('輸出結(jié)果：'|| num_C);
end;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容