一文詳解oracle存儲過程

簡介

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

使用存儲過程可以大大提高數據庫的性能和安全性。存儲過程可以減少網絡流量和請求，同時也可以減少與應用程序之間的接口調用，從而提高了數據庫的性能和可靠性。

1、基本語法

1.1 新建測試窗口

• new test window

1.2程序結構

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

-- created on 2023/5/10 by 肖 
declare 
--聲明變量 游標

begin
-- 執行語句

-- 異常處理
end;

1.3打印輸出

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

begin

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

end;

執行結果

• 如果你不能打印輸出，需要開啟 set serveroutput on 因為默認情況下，輸出選項是關閉狀態。

1.4 變量

變量分兩大類如:

• 普通數據類型（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 數據庫中的虛擬表的關鍵字

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

end;

執行結果:

1.4.2引用型變量

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

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

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

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

end;

執行結果:

推薦大家使用引用型變量區別:

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

總結:

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

1.4.3 記錄型變量

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

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

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

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

end;

總結:

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

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

• 記錄型變量只能存儲一個完整的行數據

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

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

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

1.5 流程控制

1.5.1 條件分支

• if條件判斷~語法

begin

if 條件 then
    執行語句
end if;

end;

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

begin

if 條件 then
  執行語句
else
  執行語句
end if;

end;

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

begin

if 條件1 then 執行1
  
 elsif 條件2 then 執行2

 else 執行3
  
end if; 

end;

案例:

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

begin

--查詢emp表中的記錄數賦值給變量

select count(1) into v_count from emp;

--判斷打印

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

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

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

end if;

end;

執行結果:

1.5.2 循環

• loop 語法

begin
loop
	exit when 退出循環條件  
end loop;
end;

loop語法案例:

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

begin

loop

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

  --循環變量自增
  v_num := v_num + 1;

end loop;

end;

• while 語法

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

while語法案例:

declare
  --聲明循環變量
  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;

--此循環會先判斷再執行語句

• for循環 語法

for 變量名 in 變量的初始值..結束值 loop
	循環語句;
end loop;

for循環語法 案例:

    --for循環打印 1-10
declare
  --聲明循環變量并賦初值
  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、游標

2.1游標說明

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

游標聲明：

cursor 游標名[(參數列表)] is 查詢語句;

游標的打開：

open 游標名;

游標的取值：

fetch 游標名 into 變量列表;

游標的關閉：

close 游標名;

注意: 游標名自身是可以帶參數的，如果有參數、參數會帶入到查詢語句中進行查詢，游標本質 就是 一個 is 查詢語句，也就是說查詢結果被放置到游標中。

2.3 游標屬性

游標的屬性

2.4無參數游標

• 使用游標查詢emp表中所有員工的姓名和工資，loop循環依次打印結果集。

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

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

v_sal emp.esalary%type;

begin

--打開游標
open c_emp;

--遍歷游標中的值
loop

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

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

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

end loop;

--關閉游標
close c_emp;

end;

執行結果:

2.5帶參數的游標

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

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

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

v_sal emp.esalary%type;

begin

--打開游標并傳遞參數
open c_emp(1001);

--遍歷游標中的值
loop

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


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

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


end loop;

--關閉游標
close c_emp;

end;

執行結果:

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

錯誤反例演示:

反例執行結果:

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

loop

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

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

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

end loop;

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

3、存儲過程

3.1 概念

• 之前編寫的plsql程序可以進行表的操作、判斷、循環等邏輯處理的工作，但無法重復調用。可以理解為代碼編寫在了java的main方法中，java可以通過封裝對象和方法來解決復用問題
• plsql是將一個個plsql的業務處理過程存儲起來進行復用,這些被存儲起來的plsql程序稱之為存儲過程

3.2 語法

參數的類型分為:

• 不帶參數的。
• 帶輸入參數的。
• 帶輸入輸出參數(返回值)的。

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

end 過程名稱;

3.3 無參存儲

3.3.1創建存儲

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

• 第二種方式: new → sql window

• 創建存儲過程語法

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

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

end p_xiao ;

1、is和as都可以用。

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

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

• 通過plsql工具查看創建好的存儲過程

3.3.2調用存儲過程

• 通過plsql程序調用new → text window

begin
--輸入調用存儲過程的名稱
p_xiao;

end;

查看結果:

3.4帶輸入參數的存儲過程 in

說明:

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

3.4.1 創建帶參數存儲過程

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

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

執行結果:

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

• 第二種方式: new → sql window

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

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

begin

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

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

end p_xiao_jian;

3.4.2 調用帶參數存儲過程

• 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;

查看執行結果:

3.5 帶輸入輸出參數的存儲過程 out

說明:

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

3.5.1 創建帶輸入輸出參數的存儲過程

• 參數傳遞方式分三類: in，out，in out
• in 表示輸入參數
• out 表示輸出參數
• in out 即可作輸入參數，也可作輸出參數。

--輸入員工號查詢某個員工(如:編號1001)信息，要求將薪水作為返回值輸出，給調用的程序使用。
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調用帶輸入輸出參數存儲過程

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

begin

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

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

end; 

執行結果:

3.6帶輸入輸出參數的存儲過程 in out

• in out 即可作輸入參數，也可作輸出參數。

3.6.1 創建帶輸入輸出參數的存儲過程

--輸入員工號查詢某個員工(如:編號1001)信息，要求將薪水作為返回值輸出，給調用的程序使用。
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賦值默認值為10
in_out_param:=10;

end p_xiao_jian;

3.6.2調用帶輸入輸出參數存儲過程

-- 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||'原始默認值: '||v_in_out_param);


end;

執行結果:

以上就是一文詳解oracle存儲過程的詳細內容，更多關于oracle存儲過程的資料請關注碩編程其它相關文章！