Oracle存儲過程語法詳解—及8道案例練習

這企鵝真可愛

本文主要講解ORACLE數(shù)據(jù)庫的存儲過程乃正，如果想學習了解MYSQL數(shù)據(jù)庫的存儲過程可參考本篇文章mysql存儲過程學習筆記荧飞，在網(wǎng)上找過很多ORACLE存儲過程的文章势篡，有語法、有練習題類帅掘。
都不是很友好吧修档，所以決定寫一篇針對入門的文章，文章結(jié)構(gòu)是參考的一篇mysql存儲過程院峡。

文章目錄：
一照激、創(chuàng)建一個簡單的存儲過程
二、存儲過程的變量
三望艺、存儲過程參數(shù)
四浅缸、存儲過程條件語句
五、存儲過程循環(huán)語句
六吹榴、存儲過程游標的使用
七像鸡、8道存儲過程——案例實戰(zhàn)
八桑涎、語法及案例使用數(shù)據(jù)

備注：數(shù)據(jù)在文章末尾

什么是存儲過程

存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集虱饿，存儲在數(shù)據(jù)庫中，經(jīng)過第一次編譯后調(diào)用不需要再次編譯嘁信，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它涮坐。存儲過程是數(shù)據(jù)庫中的一個重要對象。

存儲過程的特點

1、能完成較復(fù)雜的判斷和運算
2愕掏、可編程行強众羡，靈活
3睬关、SQL編程的代碼可重復(fù)使用
4、執(zhí)行的速度相對快一些
5敢会、減少網(wǎng)絡(luò)之間的數(shù)據(jù)傳輸友酱，節(jié)省開銷

MYSQL和ORACLE存儲過程的語法差異還是比較大的，下面我們一起學習ORACLE存儲過程的語法吧填渠。

一洒缀、創(chuàng)建一個簡單的存儲過程

1职车、創(chuàng)建存儲過程的簡單語法

create procedure 名稱  as
begin
.........
end

2馍悟、創(chuàng)建一個簡單的存儲過程

create or replace procedure test1 as
begin
  dbms_output.put_line('hello word');
end

3承粤、調(diào)用存儲過程

call test1()

運行結(jié)果如圖

二暴区、存儲過程的變量

目錄
1、先通過一個簡單的例子來學習變量的聲明和賦值
2辛臊、變量分類
2.1、普通變量
2.2彻舰、引用變量
2.3伐割、記錄型變量

1、先通過一個簡單的例子來學習變量的聲明和賦值

create or replace procedure select_emp as
--定義變量my_income
my_income varchar(20);

begin
-- 使用select語句給變量賦值刃唤，將emp_test 表中worker_no = '200010'的income賦值給變量my_income
  select  income into my_income from emp_test where worker_no = '200010';

--打印輸出變量my_income值
  dbms_output.put_line('工資'||my_income);
end;

總結(jié)
(1)隔心、在begin程序體前聲明變量，變量必須先聲明后使用尚胞；
(2)硬霍、變量具有數(shù)據(jù)類型和長度，與ORACLE的數(shù)據(jù)類型保持一致
(3)笼裳、變量可以通過select into的方式賦值唯卖，也可以通過：=賦值

通過上面的案例，大家對變量有了簡單的了解躬柬，下面詳細介紹一下變量的分類拜轨。

2、變量分類

1允青、普通數(shù)據(jù)類型（char,varchar2,date,number,boolean,long)
2橄碾、特殊變量類型（引用型變量，記錄型變量）
引用型變量，變量的數(shù)據(jù)類型取決表中的數(shù)據(jù)類型堪嫂；記錄型變量偎箫，變量不是接受一個值，是一行值皆串。

聲明變量語法:變量名變量類型（變量長度）

---普通變量         v_name     varcar2(20);
---引用型變量       v_income   emp_test.income%TYPE;
---記錄型變量       v_emp      emp_test%ROWTYPE  //表示變量v_emp存的是表中emp_test一整行的數(shù)據(jù)

2.1淹办、普通變量

當我們想使用普通變量的時候，需要先給普通變量命名恶复，制定變量的類型和長度怜森。我們在賦值的時候可以在聲明變量的時候直接賦值:=，也可以在程序中復(fù)制谤牡，通過select語句賦值副硅。

存儲過程參數(shù)

【示例】打印人員個人信息，包括：工號翅萤、工資恐疲、部門

create or replace procedure test1 as
//定義變量
my_number varchar2(20);              --工號
my_income int  := 3000;             --聲明變量直接賦值
my_depart varchar2(50);       --部門
begin
---通過SELECT語句給變量賦值
  select '5號部門' into my_depart from dual;

---打印輸出
  dbms_output.put_line('姓名'||my_number|| '工資'||my_income||'部門'||my_depart); 
end ;

變量賦值的方式有兩種：
1.直接賦值語句 :=
2.語句復(fù)制，使用select……into……賦值：（語法select值into變量）

2.2套么、引用變量

引用變量變量的類型和長度取決于表中字段的類型和長度培己，通過表名.列名%TYPE指定變量的類型和長度
例如： v_worker emp_test.worker_no%TYPE

【示例】定義引用變量，打印工號為200010員工的個人信息胚泌，包括：工資省咨、部門

--定義引用變量，打印工號為200010員工的個人信息玷室，包括：工資零蓉、部門
create or replace procedure test2 as
----一、定義變量
my_income emp_test.income%TYPE;--工資,引用型變量
my_depart emp_test.department%TYPE;--部門,引用型變量
begin
  select income,department into my_income,my_depart from emp_test where worker_no='200010';
  dbms_output.put_line( '工資'||my_income||'部門'||my_depart);  
end ;

總結(jié)：使用普通變量定義方式穷缤，需要知道表中列的類型敌蜂，而使用引用類型，不考慮列的類型津肛，適用于數(shù)據(jù)庫定義的更新紊册。

2.3、記錄型變量

記錄型變量接受表中的一整行記錄快耿，語法：變量名稱表名%ROWTYPR
例如：v_emp emp_test%ROWTYPE
表示變量v_emp存的是表中emp_test一整行的數(shù)據(jù)

【示例】定義記錄型變量，打印工號為200010員工的個人信息芳绩，包括：工資掀亥、部門

--定義記錄型變量，打印工號為200010員工的個人信息妥色，包括：工資搪花、部門
create or replace procedure test3 as
----一、定義變量
v_emp emp_test%ROWTYPE;---v_emp記錄型變量，接受表中的一整行記錄

begin
  select * into v_emp from emp_test where worker_no='200010';
  dbms_output.put_line( '工資'||v_emp.income||'部門'||v_emp.department);  
end ;

總結(jié)：記錄型變量只能存儲一個完整的行數(shù)據(jù)撮竿，記錄型變量使用場景：如果有一個表吮便，有100個字段，如果程序要使用這100個字段幢踏，使用引用型變量一個個聲明髓需，會特別麻煩，記錄型變量可以方便解決這個問題房蝉。

三僚匆、存儲過程參數(shù)

目錄：
1、基本語法
1.1搭幻、存儲過程的傳入?yún)?shù)IN
1.2咧擂、存儲過程的傳出參數(shù)out

1、基本語法

create procedure 名稱([IN|OUT|INOUT] 參數(shù)名 參數(shù)數(shù)據(jù)類型 )
begin
.........
end

存儲過程的參數(shù)類型有：IN,OUT,INOUT檀蹋，下面分別介紹這個三種類型：

1.1松申、存儲過程的傳入?yún)?shù)IN

（1）傳入?yún)?shù)，類型為in,表示該參數(shù)的值必須在調(diào)用存儲過程時指定俯逾，如果不顯示指定為in,那么默認就是in類型贸桶。
（2）IN類型參數(shù)一般只用于傳入，在調(diào)用過程中一般不作為修改和返回

通過一個實例來演示：
需求：編寫存儲過程纱昧，傳入員工工號刨啸，根據(jù)工號輸出該員工的工資

CREATE OR REPLACE PROCEDURE test_income(worker_id varchar2) as
my_income  VARCHAR2(100); 
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

//調(diào)用存儲過程
call test_income(200013)

1.2、存儲過程的傳出參數(shù)out

(1) out類型參數(shù)只能接收賦值识脆，不能給其他變量賦值设联。
(2) 輸出模式的參數(shù)，用于輸出值灼捂，會忽略傳入的值,在子程序內(nèi)部可以對其進行修改离例。
(3) 調(diào)用時參數(shù)需要使用變量.

需求：調(diào)用存儲過程時，傳入worker_id悉稠，返回該用戶的工資income

CREATE OR REPLACE PROCEDURE test_out(worker_id in varchar2,my_income out emp_test.income%TYPE) as
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

-- 調(diào)用
declare
my_income   int;
begin
  test_out(200010,my_income);
end;

運行如圖

1.3宫蛆、存儲過程的可變參數(shù)in out

(1) 與out類型相比不同是默認初始化參數(shù)不為null，傳的是什么就是什么的猛。
(2) 調(diào)用時耀盗，參數(shù)需要使用變量.

create or replace procedure pro_in_out(p_num in out number)
is
begin
  dbms_output.put_line(p_num);
  p_num:=10;
end;

-- 調(diào)用
declare
test number:=1;
begin
  pro_in_out(test);
  dbms_output.put_line(test);
end;

運行如圖

關(guān)于存儲過程 in、out卦尊、in out 參數(shù)的使用方法還模糊的可以看這篇文章《Oracle 存儲過程 in叛拷、out、in out 參數(shù)的使用方法》岂却，把文章中的代碼執(zhí)行一遍就理解含義忿薇。

四裙椭、存儲過程條件語句

目錄
1、基本語法結(jié)構(gòu)
2署浩、存儲過程條件語句—案例
2.1揉燃、簡單條件語句—應(yīng)用案例
2.2、多條件語句—應(yīng)用案例

1筋栋、基本語法結(jié)構(gòu)

(1）條件語句基本結(jié)構(gòu)

if() then...else...end if;

(2）多條件判斷語句

if()   then...
elsif()   then...
else ...
end if;

2炊汤、存儲過程條件語句—案例

2.1、簡單條件語句—應(yīng)用案例

編寫存儲過程二汛，如果員工工號worker_no是偶數(shù)則返回工資income,否則返回部門department

create or replace procedure test_worker(worker_id varchar2) as
my_income varchar(20);
my_department varchar(20);
begin
  if(mod(to_number(worker_id),2)=0)  then
      select income into my_income  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_income);
  else
      select department into my_department  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_department);
   end if;
end;

//調(diào)用存儲過程
call test_worker(200013);
call test_worker(200012)

運行如圖

2.2婿崭、多條件語句—應(yīng)用案例

創(chuàng)建一個存儲過程，以員工號為參數(shù)肴颊，修改該員工的工資氓栈。
若該員工屬于10號部門，則工資增加150婿着；
若屬于20號部門授瘦，則工資增加200；
若屬于30號部門竟宋，則工資增加250提完；
若屬于其他部門，則增加300丘侠。

CREATE OR REPLACE PROCEDURE add_income(worker_id varchar2) as
--1徒欣、定義變量部門 my_department
my_department varchar(20);
--2、查出變量值
begin
select  department into my_department  from emp_test  where worker_no =worker_id;
if (my_department='10號部門') then
  update emp_test set  income = income+150 where worker_no =worker_id;
  --commit;
elsif (my_department='20號部門') then
   update emp_test set  income = income+200 where worker_no =worker_id;
elsif (my_department='30號部門') then
   update emp_test set  income = income+250 where worker_no =worker_id;
else
   update emp_test set  income = income+300 where worker_no =worker_id;
   commit;
   end if;
end;

//調(diào)用存儲過程
call add_income(200010);
call add_income(200015)
---執(zhí)行后結(jié)果
select *  from emp_test

五蜗字、存儲過程循環(huán)語句

oracle中的循環(huán)語句大致分三種：While打肝、For、Loop

目錄
1挪捕、while語句的基本結(jié)構(gòu)
2粗梭、LOOP語句的基本結(jié)構(gòu)
3、for語句的基本結(jié)構(gòu)及案例

1级零、while語句的基本結(jié)構(gòu)及案例

while(條件)   loop
     ……
end loop;

示例：使用循環(huán)語句断医，向表emp_test中插入十條數(shù)據(jù)，僅給工號字段插入數(shù)據(jù)奏纪，其它字段不插入數(shù)據(jù)鉴嗤，插入工號為12001、12002序调、12003躬窜、12004、12005至120010

create or replace procedure test_inset as
my_worker int;
begin
  my_worker :=0;
  while my_worker<10 loop
    my_worker :=my_worker+1;
    insert into emp_test(worker_no) values('1200'||to_char(my_worker));
    commit;
  end loop;
end;

//調(diào)用存儲過程
call test_inset();
select *  from  emp_test

2炕置、LOOP語句的基本結(jié)構(gòu)及案例

  LOOP
    Exit When(退出條件);
   ……
  END LOOP

示例：使用LOOP循環(huán)荣挨，打印輸出0至5的數(shù)字

create or replace procedure loop_test is
i number;
begin
  i := 0;
   LOOP
    Exit When(i > 5);
    Dbms_Output.put_line(i);
    i := i + 1;
  END LOOP;
end ;

----調(diào)用存儲過程
call loop_test()

運行如圖

3、for語句的基本結(jié)構(gòu)及案例

for （） in （）
  loop
    ……
  end loop;

示例：使用FOR循環(huán)朴摊，打印輸出0至5的數(shù)字

create or replace procedure for_test is
i number;
begin
  i:=0;
  for i in 1..5 loop
  dbms_output.put_line(i);
  end loop;
end ;

----調(diào)用存儲過程
call for_test()

運行結(jié)果如圖

六默垄、存儲過程游標的使用

目錄
1、什么是游標
2甚纲、語法
3口锭、案例

1、什么是游標

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

2鹃操、語法

//游標聲明：
cursor 游標名[(參數(shù)列表)] is 查詢語句；
//游標打開：
open 游標名春哨；
//游標取值：
fetch 游標名 into 變量列表荆隘；
//游標關(guān)閉：
close 游標名；

3赴背、案例

示例1：編寫存儲過程椰拒，使用游標，把emp_test表中20號部門的員工工號逐一打印

create or replace procedure cur_test as
my_workerno varchar(20);
---游標聲明：
cursor cur_worker is select  worker_no from emp_test where department='20號部門';

begin
--游標打開：
open cur_worker;
LOOP
  ---獲取游標中的數(shù)據(jù)
  fetch cur_worker into my_workerno;--提取cursor,提取結(jié)果集中的記錄
  
  --退出循環(huán)條件
  Exit When cur_worker%notfound;
  dbms_output.put_line('my_workerno:'||my_workerno);
  
end loop;
close cur_worker;

end;

---調(diào)用存儲過程
call cur_test()

運行結(jié)果

七凰荚、8道存儲過程——案例實戰(zhàn)

--建表：
create table emp_test(
worker_no  varchar2(50),---員工工號
income  int,---員工工資
department varchar2(50)--部門
)燃观；

---插入數(shù)據(jù)
insert into emp_test
select '200010',5000,'10號部門'  from dual union all
select '200011',5000,'20號部門'  from dual union all
select '200012',5000,'30號部門'  from dual union all
select '200013',5000,'40號部門'  from dual union all
select '200014',5000,'20號部門'  from dual union all
select '200015',5000,'40號部門'  from dual

1、創(chuàng)建一個存儲過程便瑟，以員工號為參數(shù)缆毁，輸出該員工的工資

CREATE OR REPLACE PROCEDURE test_income(worker_id varchar2) as
my_income  VARCHAR2(100); 
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

//調(diào)用存儲過程
call test_income(200013)

2、創(chuàng)建一個存儲過程到涂，以員工號為參數(shù)脊框，修改該員工的工資。若該員工屬于10號部門养盗，則工資增加150缚陷；若屬于20號部門，則工資增加200往核；若屬于30號部門箫爷，則工資增加250；若屬于其他部門聂儒，則增加300虎锚。

CREATE OR REPLACE PROCEDURE add_income(worker_id varchar2) as
--1、定義變量部門 my_department
my_department varchar(20);
--2衩婚、查出變量值
begin
select  department into my_department  from emp_test  where worker_no =worker_id;
if (my_department='10號部門') then
  update emp_test set  income = income+150 where worker_no =worker_id;
  --commit;
elsif (my_department='20號部門') then
   update emp_test set  income = income+200 where worker_no =worker_id;
elsif (my_department='30號部門') then
   update emp_test set  income = income+250 where worker_no =worker_id;
else
   update emp_test set  income = income+300 where worker_no =worker_id;
   commit;
   end if;
end;

//調(diào)用存儲過程
call add_income(200010);
call add_income(200015)
---執(zhí)行后結(jié)果
select *  from emp_test

3窜护、編寫存儲過程，如果員工工號worker_no是偶數(shù)則返回工資income,否則返回部門department

create or replace procedure test_worker(worker_id varchar2) as
my_income varchar(20);
my_department varchar(20);
begin
  if(mod(to_number(worker_id),2)=0)  then
      select income into my_income  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_income);
  else
      select department into my_department  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_department);
   end if;
end;

//調(diào)用存儲過程
call test_worker(200013);
call test_worker(200012)

4非春、創(chuàng)建一個存儲過程柱徙，用來統(tǒng)計表emp_test表中行數(shù)數(shù)量

---創(chuàng)建一個存儲過程缓屠，用來統(tǒng)計表emp_test表中行數(shù)數(shù)量
create or replace procedure test_count_line as
my_line int;
begin
  select count(1) into my_line from emp_test;
  dbms_output.put_line('表emp_test行數(shù)為：'||my_line);
end;

//調(diào)用存儲過程
call test_count_line()

5、根據(jù)員工工號护侮，輸出員工的性別（F男性敌完，M女性），部門羊初。輸出格式如下：員工200013為男性滨溉，在40號部門

---新增性別字段，F(xiàn)男性长赞，M女性
alter table emp_test  add sex varchar(2);
---插入數(shù)據(jù)
update emp_test set sex='F' where department in('40號部門','10號部門');
update emp_test set sex='M' where department not in('40號部門','10號部門');
---在PLSQL中執(zhí)行上面語句之后記住提交

create or replace procedure test_sex_dpat(worker_id varchar2) as
my_sex varchar2(20);
my_department varchar2(30);
begin
  select  sex,department into my_sex,my_department  from  emp_test where worker_no =worker_id ;
  if (my_sex='F') then
    dbms_output.put_line('員工'||worker_id||'為男性晦攒，在'||my_department);
  else
    dbms_output.put_line('員工'||worker_id||'為女性，在'||my_department);
  end if;
end;

6得哆、使用WHILE ... LOOP ... END LOOP 語法脯颜，輸出1到5的數(shù)字

create or replace procedure test_while as
my_number int;
BEGIN
  my_number := 0; 
  WHILE my_number < 5 LOOP-------當my_number 小于5時執(zhí)行下面的循 環(huán)語句，否則終止程序
    my_number := my_number + 1;
    DBMS_OUTPUT.PUT_LINE(my_number);
  END LOOP;
END;

----調(diào)用存儲過程
call test_while()

7柳恐、向表emp_test中插入十條數(shù)據(jù)伐脖，僅給工號字段插入數(shù)據(jù)，其它字段不插入數(shù)據(jù)乐设，插入工號為12001讼庇、12002、12003近尚、12004蠕啄、12005至120010

create or replace procedure test_inset as
my_worker int;
begin
  my_worker :=0;
  while my_worker<10 loop
    my_worker :=my_worker+1;
    insert into emp_test(worker_no) values('1200'||to_char(my_worker));
    commit;
  end loop;
end;

//調(diào)用存儲過程
call test_inset();
select *  from  emp_test

8、創(chuàng)建一個存儲過程戈锻，以員工號為參數(shù)歼跟，返回該員工所在的部門的平均工資。

create or replace procedure test8(i_worker_no emp_test.worker_no%TYPE) as
avg_income int;
begin
  select  avg(income)  into avg_income from emp_test where worker_no=i_worker_no;
  dbms_output.put_line(i_worker_no||'平均工資'||avg_income);
end;

//調(diào)用存儲過程
call test8('40號部門')

八格遭、語法及案例使用數(shù)據(jù)

數(shù)據(jù)

--建表：
create table emp_test(
worker_no  varchar2(50),---員工工號
income  int,---員工工資
department varchar2(50)--部門
)哈街；

---插入數(shù)據(jù)
insert into emp_test
select '200010',5000,'10號部門'  from dual union all
select '200011',5000,'20號部門'  from dual union all
select '200012',5000,'30號部門'  from dual union all
select '200013',5000,'40號部門'  from dual union all
select '200014',5000,'20號部門'  from dual union all
select '200015',5000,'40號部門'  from dual

后期會新增案例，及游標使用.拒迅。
一直以為自己懂了會了骚秦，還是要寫文章總結(jié)才能掌握更深刻，也在寫文章中明確找到了自己的不足璧微。
堅持寫文章作箍，加油！