一文詳解oracle存儲過程

簡介

oracle 存儲過程是 oracle 數(shù)據(jù)庫中的一種數(shù)據(jù)處理對象，它可以在數(shù)據(jù)庫中定義一組預(yù)定義的 sql 語句，用于完成特定的數(shù)據(jù)庫操作。存儲過程可以被授權(quán)的用戶調(diào)用，并且可以執(zhí)行多個語句，這些語句可以被視為一個單獨的操作，也可以被視為一系列的操作。

使用存儲過程可以大大提高數(shù)據(jù)庫的性能和安全性。存儲過程可以減少網(wǎng)絡(luò)流量和請求，同時也可以減少與應(yīng)用程序之間的接口調(diào)用，從而提高了數(shù)據(jù)庫的性能和可靠性。

1、基本語法

1.1 新建測試窗口

• new test window

1.2程序結(jié)構(gòu)

• 在java編程中是區(qū)分大小寫，這里不用區(qū)分大小寫。
• declare部分聲明變量或游標(biāo)（結(jié)果集類型變量)，程序沒有變量聲明的可以省略或刪除。
• plsql可分為三個部分: 變量聲明部分，執(zhí)行部分，異常處理部分。

-- created on 2023/5/10 by 肖 
declare 
--聲明變量 游標(biāo)

begin
-- 執(zhí)行語句

-- 異常處理
end;

1.3打印輸出

• dbms_output為oracle內(nèi)置程序包，類似java中的system.out,而put_line() 是調(diào)用的方法,相當(dāng)于println()方法。
• 需要注意的是: put_line('hello mr.xiao'); 中 一定是 ' ' 號，否則會報錯的。

begin

--打印 hello mr.xiao
dbms_output.put_line('hello mr.xiao');

end;

執(zhí)行結(jié)果

• 如果你不能打印輸出，需要開啟 set serveroutput on 因為默認(rèn)情況下，輸出選項是關(guān)閉狀態(tài)。

1.4 變量

變量分兩大類如:

• 普通數(shù)據(jù)類型（char,varchar2, date, number, boolean, long）
• 特殊變量類型（引用型變量、記錄型變量）

聲明變量的方式如:

• 變量名 變量類型（變量長度） 例如： v_name varchar2(30);

1.4.1普通變量

變量賦值的方式有兩種如：

• 直接賦值語句 := 比如: v_name := '你才是臭弟弟'
• 語句賦值，使用select …into … 賦值：（語法 select 值 into 變量）

  -- 打印個人信息，包括： 姓名、薪水、地址
declare
-- 姓名
v_name varchar2(30) := '你才是臭弟弟'; -- 聲明變量直接賦值
--薪水
v_sal number;
--地址
v_addr varchar2(200);

begin

--在程序中直接賦值
v_sal := 1800; --工資每月1800 每天笑哈哈

--語句賦值
select 'csdn你才是臭弟弟' into v_addr from dual; --不會有人不知道dual吧，dual 是一個用于描述 oracle 數(shù)據(jù)庫中的虛擬表的關(guān)鍵字

--打印變量  注意 || 是拼接
dbms_output.put_line('姓名：' || v_name || ',薪水：' || v_sal || ',地址：' ||v_addr);

end;

執(zhí)行結(jié)果:

1.4.2引用型變量

• 變量的類型和長度取決于表中字段的類型和長度
• 通過 表名.列名%type 指定變量的類型和長度，例如： v_name emp.ename%type

  -- 查詢emp表中1001號員工的個人信息，打印姓名和薪水
declare
-- 姓名
v_name emp.ename%type; -- 聲明變量直接賦值
--薪水
v_sal  emp.esalary%type;

begin
--查詢表中的姓名和薪水并賦值給變量
--注意查詢的字段和賦值的變量的順序、個數(shù)、類型要一致
select ename, esalary into v_name, v_sal from emp where employeeid = 1001;

--打印變量
dbms_output.put_line('姓名：' || v_name || ',薪水：' || v_sal);

end;

執(zhí)行結(jié)果:

推薦大家使用引用型變量區(qū)別:

• 普通型變量: v_name varchar2(30); 你怎么知道一定varchar2類型，你又怎么知道長度一定是30呢，假設(shè)v_name varchar2(1); 就對應(yīng)不上select ename, esalary into v_name, v_sal from emp 這條語句中的 ename 的長度了，也就接收不到值了，就會報錯。如果要使用普通變量前提是 ，了解查詢表中對應(yīng)字段的 類型 及 長度， 才能基于他們來確定類型長度 這比較繁瑣。
• 引用型變量: 聲明一個變量 不再定義類型長度，而是基于接收表字段的類型及長度 來定義。如:v_name emp.ename%type;

總結(jié):

使用普通變量定義方式，需要知道表中列的類型，而使用引用類型，不需要考慮列的類型，使用%type是非常好的編程風(fēng)格，因為引用型變量更加靈活。

1.4.3 記錄型變量

• 記錄型變量 接受表中的一整行記錄，相當(dāng)于java中的一個對象
• 語法： 變量名稱 表名%rowtype， 例如：v_emp emp%rowtype;

  -- 查詢emp表中1001號員工的個人信息，打印姓名和薪水
declare
-- 記錄型變量接受一行
v_emp emp%rowtype;

begin
--記錄型變量默認(rèn)接受表中的一行數(shù)據(jù)，不能指定字段。
select * into v_emp from emp where employeeid = 1001;

--打印變量，通過變量名.屬性的方式獲取變量中的值
dbms_output.put_line('姓名：' || v_emp.ename || ',薪水：' || v_emp.esalary);

end;

總結(jié):

• 如果有一張表，有50個字段，那么你程序如果要使用這50字段話，如果你使用引用型變量一個個聲明，會特別繁瑣，記錄型變量可以方便的解決這個問題。

注意錯誤的使用案例如下:

• 記錄型變量只能存儲一個完整的行數(shù)據(jù)

我把 * 換成 單個字段執(zhí)行報錯，因為上面的變量定義的是一行，而現(xiàn)在只給一個是不行的。

• 返回的行太多了，記錄型變量也接收不了

現(xiàn)在這條sql 為什么報錯，因為現(xiàn)在是全表查詢 返回的行數(shù)超出了 一個變量只能接收一行，這就和java 類似了，應(yīng)該用集合去裝才可以 ，裝進集合在取出來 是不是就跟java 中循環(huán)取值差不多。

1.5 流程控制

1.5.1 條件分支

• if條件判斷~語法

begin

if 條件 then
    執(zhí)行語句
end if;

end;

• if...else 條件判斷~語法

begin

if 條件 then
  執(zhí)行語句
else
  執(zhí)行語句
end if;

end;

• if...elsif...else條件判斷~語法，注意關(guān)鍵字：elsif。

begin

if 條件1 then 執(zhí)行1
  
 elsif 條件2 then 執(zhí)行2

 else 執(zhí)行3
  
end if; 

end;

案例:

  --判斷emp表中記錄是否超過20條，10-20之間，或者10條以下
declare
--聲明變量接受emp表中的記錄數(shù)
v_count number;

begin

--查詢emp表中的記錄數(shù)賦值給變量

select count(1) into v_count from emp;

--判斷打印

if v_count > 20 then
  dbms_output.put_line('emp表中的記錄數(shù)超過了20條為：' || v_count || '條。');

elsif v_count >= 10 then
  dbms_output.put_line('emp表中的記錄數(shù)在10~20條之間為：' || v_count || '條。');

else
  dbms_output.put_line('emp表中的記錄數(shù)在10條以下為：' || v_count || '條。');

end if;

end;

執(zhí)行結(jié)果:

1.5.2 循環(huán)

• loop 語法

begin
loop
	exit when 退出循環(huán)條件  
end loop;
end;

loop語法案例:

  --循環(huán)打印 1-5
declare
--聲明循環(huán)變量并賦初值
v_num number := 1;

begin

loop

  exit when v_num > 5;
  
  dbms_output.put_line(v_num);

  --循環(huán)變量自增
  v_num := v_num + 1;

end loop;

end;

• while 語法

while(判斷循環(huán)的條件) loop
	循環(huán)的語句;
end loop;

while語法案例:

declare
  --聲明循環(huán)變量
  v_num number;
begin
  -- 必須給一個初始值
  v_num := 1;
  while(v_num < 10) loop
      dbms_output.put_line('值為: ' || v_num);
      v_num := v_num + 1;
  end loop;
end;

--此循環(huán)會先判斷再執(zhí)行語句

• for循環(huán) 語法

for 變量名 in 變量的初始值..結(jié)束值 loop
	循環(huán)語句;
end loop;

for循環(huán)語法 案例:

    --for循環(huán)打印 1-10
declare
  --聲明循環(huán)變量并賦初值
  v_num number ;
begin
	  --此語句會自動將1到10賦值給v_num
  for v_num in 1..10 loop
      dbms_output.put_line('值為: ' || v_num);
  end loop;
end;

2、游標(biāo)

2.1游標(biāo)說明

• 用于臨時存儲一個查詢返回的多行數(shù)據(jù)，通過遍歷游標(biāo)，可以逐行訪問處理該結(jié)果集的數(shù)據(jù)。
• 游標(biāo)的使用方式：聲明→打開→讀取→關(guān)閉 2.2 語法

游標(biāo)聲明：

cursor 游標(biāo)名[(參數(shù)列表)] is 查詢語句;

游標(biāo)的打開：

open 游標(biāo)名;

游標(biāo)的取值：

fetch 游標(biāo)名 into 變量列表;

游標(biāo)的關(guān)閉：

close 游標(biāo)名;

注意: 游標(biāo)名自身是可以帶參數(shù)的，如果有參數(shù)、參數(shù)會帶入到查詢語句中進行查詢，游標(biāo)本質(zhì) 就是 一個 is 查詢語句，也就是說查詢結(jié)果被放置到游標(biāo)中。

2.3 游標(biāo)屬性

游標(biāo)的屬性

2.4無參數(shù)游標(biāo)

• 使用游標(biāo)查詢emp表中所有員工的姓名和工資，loop循環(huán)依次打印結(jié)果集。

--使用游標(biāo)查詢emp表中所有員工的姓名和工資，依次打印結(jié)果集。
declare
--聲明游標(biāo)
cursor c_emp is
  select ename, esalary from emp;

--聲明變量用來接受游標(biāo)中的元素
v_ename emp.ename%type;

v_sal emp.esalary%type;

begin

--打開游標(biāo)
open c_emp;

--遍歷游標(biāo)中的值
loop

  --通過fetch語句獲取游標(biāo)中的值并賦值給變量
  fetch c_emp
    into v_ename, v_sal;

  --通過%notfound判斷是否有值,有值打印,沒有則退出循環(huán)
  exit when c_emp%notfound;

  dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end loop;

--關(guān)閉游標(biāo)
close c_emp;

end;

執(zhí)行結(jié)果:

2.5帶參數(shù)的游標(biāo)

• 使用游標(biāo)查詢并打印某部門的員工的姓名和薪資，部門編號為運行時手動輸入。

 --使用游標(biāo)查詢并打印某部門的員工的姓名和薪資，部門編號為運行時手動輸入。
declare
--聲明游標(biāo)傳遞參數(shù)
cursor c_emp(v_emploid emp.employeeid%type) is
  select ename, esalary from emp where employeeid = v_emploid; 

--聲明變量用來接受游標(biāo)中的元素
v_ename emp.ename%type;

v_sal emp.esalary%type;

begin

--打開游標(biāo)并傳遞參數(shù)
open c_emp(1001);

--遍歷游標(biāo)中的值
loop

--通過fetch語句獲取游標(biāo)中的值并賦值給變量
  fetch c_emp
    into v_ename, v_sal;


     --通過%notfound判斷是否有值,有值打印,沒有則退出循環(huán)
     exit when c_emp%notfound;
     
     

  dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);


end loop;

--關(guān)閉游標(biāo)
close c_emp;

end;

執(zhí)行結(jié)果:

注意:%notfound屬性默認(rèn)值為flase,所以在循環(huán)中要注意判斷條件的位置.如果先判斷在fetch會導(dǎo)致最后一條記錄的值被打印兩次(多循環(huán)一次默認(rèn));

錯誤反例演示:

反例執(zhí)行結(jié)果:

原因:%notfound 默認(rèn)值是 false，false意味著游標(biāo)里面默認(rèn)是有值，到底有值還是沒值 需要fetch 好之后才知道有沒有值，%notfound 默認(rèn)做了一個有值的假設(shè) ，看下面代碼:

loop

--通過%notfound判斷是否有值,有值打印,沒有則退出循環(huán)
exit when c_emp%notfound;

--通過fetch語句獲取游標(biāo)中的值并賦值給變量
fetch c_emp
into v_ename, v_sal;

dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end loop;

exit when c_emp%notfound; 判斷有值打印，出去之后 又帶著有值的進入到循環(huán)中exit when c_emp%notfound;判斷沒值打印， 所以這次打印的是上次值的。注意存放的位置。

3、存儲過程

3.1 概念

• 之前編寫的plsql程序可以進行表的操作、判斷、循環(huán)等邏輯處理的工作，但無法重復(fù)調(diào)用?？梢岳斫鉃榇a編寫在了java的main方法中，java可以通過封裝對象和方法來解決復(fù)用問題
• plsql是將一個個plsql的業(yè)務(wù)處理過程存儲起來進行復(fù)用,這些被存儲起來的plsql程序稱之為存儲過程

3.2 語法

參數(shù)的類型分為:

• 不帶參數(shù)的。
• 帶輸入?yún)?shù)的。
• 帶輸入輸出參數(shù)(返回值)的。

create or replace procedure 過程名稱[(參數(shù)列表)] is
begin

end 過程名稱;

3.3 無參存儲

3.3.1創(chuàng)建存儲

• 第一種方式: new→program window→ procedure

• 第二種方式: new → sql window

• 創(chuàng)建存儲過程語法

--通過調(diào)用存儲過程打印hello 臭弟弟
create or replace procedure p_xiao is
--聲明變量
begin

dbms_output.put_line('hello 臭弟弟');

end p_xiao ;

1、is和as都可以用。

2、存儲過程中沒有declare關(guān)鍵字，declare用在語句塊中。也就是說匿名程序才需要， 存儲過程沒有可以直接帶上方--聲明變量。

• 注意點擊△執(zhí)行后 會進行存儲 ，procedures 中會以p_xiao 這個名稱進行存儲。

• 通過plsql工具查看創(chuàng)建好的存儲過程

3.3.2調(diào)用存儲過程

• 通過plsql程序調(diào)用new → text window

begin
--輸入調(diào)用存儲過程的名稱
p_xiao;

end;

查看結(jié)果:

3.4帶輸入?yún)?shù)的存儲過程 in

說明:

• 帶參數(shù)的存儲過程跟我們在java中的方法就可以對應(yīng)上，比如查詢并打印某個員工姓名薪水 ，在調(diào)用存儲過程的時候自己指定傳參， 比如我傳一個員工編號，基于傳的編號將結(jié)果返回。
• 實現(xiàn)查詢并打印某個員工(如:編號1001)的姓名和薪水， 調(diào)用存儲過程的時候傳入員工編號，自動控制臺打印。

3.4.1 創(chuàng)建帶參數(shù)存儲過程

• 第一種方式: 重新編輯存儲過程右擊 → edit(這是基于之前創(chuàng)建的，還可以進行編輯)

• 需要注意的是如果有or replace當(dāng)存儲過程名字被更改時，如果plsql中存在此存儲過程名稱會被刪除替換創(chuàng)建當(dāng)前的，果不存在則創(chuàng)建一個新的存儲過程。

執(zhí)行結(jié)果:

• 如果沒有or replace語句plsql也不存相同的名字在則會新創(chuàng)建。如果存在則會報錯。

• 第二種方式: new → sql window

• 查詢并打印某個員工(如:編號1001)的姓名和薪水， 要調(diào)用存儲過程的時候傳入員工編號，自動控制臺打印。
• 注意:參數(shù)要與定義的參數(shù)的順序和類型一致

--查詢并打印某個員工(如:編號1001)的姓名和薪水， 要調(diào)用存儲過程的時候傳入員工編號，自動控制臺打印。
create or replace procedure p_xiao_jian(in_employeeid in emp.employeeid%type) as
--聲明變量接受查詢結(jié)果
v_ename emp.ename%type;
v_sal   emp.esalary%type;

begin

--根據(jù)用戶傳遞的員工號查詢姓名和薪水   
--注意:參數(shù)要與定義的參數(shù)的順序和類型一致 如: ename  into  v_ename 
select ename, esalary into v_ename, v_sal from emp where employeeid = in_employeeid;

--打印結(jié)果
dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end p_xiao_jian;

3.4.2 調(diào)用帶參數(shù)存儲過程

• new → text window 直接賦值

-- created on 2023/5/13 by 肖 
declare 
-- local variables here
i integer;
begin
-- test statements here
p_xiao_jian(1001);--直接賦值  做一個值的傳遞

end;

• 也可以 聲明變量 賦值

-- created on 2023/5/13 by 肖 
declare 
-- local variables here
 v_param number :=1001; --聲明變量  賦值
begin
-- test statements here
p_xiao_jian(v_param);

end;

查看執(zhí)行結(jié)果:

3.5 帶輸入輸出參數(shù)的存儲過程 out

說明:

• 帶輸入輸出的這種存儲過程通常是給第三方程序調(diào)用的，就比如java或其他編程語言，也就是說把這個存儲過程的 計算結(jié)果進行返回不是在數(shù)據(jù)庫打印打印就完事了
• 舉例說明比如輸入員工編號查詢某個員工信息，要求將薪水作為返回值輸出，給調(diào)用的程序使用。這個調(diào)用的程序可以是plsq自身程序，也可以是第三方比如java 程序。

3.5.1 創(chuàng)建帶輸入輸出參數(shù)的存儲過程

• 參數(shù)傳遞方式分三類: in，out，in out
• in 表示輸入?yún)?shù)
• out 表示輸出參數(shù)
• in out 即可作輸入?yún)?shù)，也可作輸出參數(shù)。

--輸入員工號查詢某個員工(如:編號1001)信息，要求將薪水作為返回值輸出，給調(diào)用的程序使用。
create or replace procedure p_xiao_jian(in_employeeid in emp.employeeid%type,out_esalary out emp.esalary%type) as

begin

--查詢 esalary into 給 out_esalary 輸出變量
select esalary into out_esalary from emp where employeeid = in_employeeid;


end p_xiao_jian;

3.5.2調(diào)用帶輸入輸出參數(shù)存儲過程

declare
--聲明一個變量接受存儲過程的輸出參數(shù)
v_esalary emp.esalary%type;

begin

p_xiao_jian(1001, v_esalary); --注意參數(shù)的順序

dbms_output.put_line('工資:'||v_esalary);

end; 

執(zhí)行結(jié)果:

3.6帶輸入輸出參數(shù)的存儲過程 in out

• in out 即可作輸入?yún)?shù)，也可作輸出參數(shù)。

3.6.1 創(chuàng)建帶輸入輸出參數(shù)的存儲過程

--輸入員工號查詢某個員工(如:編號1001)信息，要求將薪水作為返回值輸出，給調(diào)用的程序使用。
create or replace procedure p_xiao_jian(in_employeeid in  emp.employeeid%type,out_esalary  out emp.esalary%type,in_out_param in out number) as

begin


--查詢 esalary into 給 out_esalary 輸出變量
select esalary into out_esalary from emp where employeeid = in_employeeid;
--打印被傳入的值
dbms_output.put_line('我是被傳入的值'||in_out_param);
--in_out_param賦值默認(rèn)值為10
in_out_param:=10;

end p_xiao_jian;

3.6.2調(diào)用帶輸入輸出參數(shù)存儲過程

-- created on 2023/5/16 by 肖 
declare 
-- local variables here

v_esalary emp.esalary%type;

v_in_out_param number:=6;--傳入的值


begin
-- test statements here
p_xiao_jian(1001,v_esalary,v_in_out_param);

dbms_output.put_line('薪水:'||v_esalary||'原始默認(rèn)值: '||v_in_out_param);


end;

執(zhí)行結(jié)果:

以上就是一文詳解oracle存儲過程的詳細(xì)內(nèi)容，更多關(guān)于oracle存儲過程的資料請關(guān)注碩編程其它相關(guān)文章！