數(shù)據(jù)庫存儲(chǔ)過程

MySQL 5.0 版本開始支持存儲(chǔ)過程。

存儲(chǔ)過程（Stored Procedure）是一種在數(shù)據(jù)庫中存儲(chǔ)復(fù)雜程序，以便外部程序調(diào)用的一種數(shù)據(jù)庫對(duì)象，它可以封裝成SQL語句集，以便完成一些較為復(fù)雜的業(yè)務(wù)邏輯，并且可以想 Java 等高級(jí)編程語言一樣輸入?yún)?shù)。

存儲(chǔ)過程是為了完成特定功能的SQL語句集，創(chuàng)建時(shí)會(huì)預(yù)先編譯，并保存在數(shù)據(jù)庫中，用戶和后續(xù)的 調(diào)用都不需要再次編譯了，用戶可通過指定存儲(chǔ)過程的名字并給定參數(shù)(需要時(shí))來調(diào)用執(zhí)行。

存儲(chǔ)過程思想上很簡(jiǎn)單，就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用。

問題來了？

為什么wo們不直接使用 SQL來查詢業(yè)務(wù)數(shù)據(jù)，Java 來處理業(yè)務(wù)邏輯，這樣子做不行嗎？為什么要費(fèi)盡心思去特意學(xué)SQL的存儲(chǔ)過程來處理業(yè)務(wù)邏輯？

因?yàn)槲覀冊(cè)趯?shí)際的生產(chǎn)當(dāng)中，我們可以通過修改存儲(chǔ)過程的方式修改業(yè)務(wù)邏輯，而不用啟動(dòng)服務(wù)器。另一方面，存儲(chǔ)過程是期間京每一條SQL語句都進(jìn)行編譯，并保存在數(shù)據(jù)庫當(dāng)中，那么我們通過Java程序調(diào)用SQL語句時(shí)可以不同一條條的進(jìn)行編譯，而是直接調(diào)用存儲(chǔ)過程，這樣的效率更高。

• 優(yōu)點(diǎn)

  • 存儲(chǔ)過程可封裝，并隱藏復(fù)雜的商業(yè)邏輯。
  • 存儲(chǔ)過程可以回傳值，并可以接受參數(shù)。
  • 存儲(chǔ)過程無法使用 SELECT 指令來運(yùn)行，因?yàn)樗亲映绦?#xff0c;與查看表，數(shù)據(jù)表或用戶定義函數(shù)不同。
  • 存儲(chǔ)過程可以用在數(shù)據(jù)檢驗(yàn)，強(qiáng)制實(shí)行商業(yè)邏輯等。

• 缺點(diǎn)

  • 存儲(chǔ)過程，往往定制化于特定的數(shù)據(jù)庫上，因?yàn)橹С值木幊陶Z言不同。當(dāng)切換到其他廠商的數(shù)據(jù)庫系統(tǒng)時(shí)，需要重寫原有的存儲(chǔ)過程。
  • 存儲(chǔ)過程的性能調(diào)校與撰寫，受限于各種數(shù)據(jù)庫系統(tǒng)。

1. 語法結(jié)構(gòu)

CREATE[DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body-- proc_parameter參數(shù)部分(先寫參數(shù)名，再寫參數(shù)類型):      [ IN | OUT | INOUT ] param_name type  -- type 是 MySQL 支持的所有類型-- routine_body(程序體)部分 ，可以書寫合法的SQL語句：  BEGIN ...... END$$

• 聲明語句結(jié)束符，可以自定義:

  DELIMITER $$
  

• 聲明存儲(chǔ)過程:

  CREATE PROCEDURE hello_procedurer(IN 參數(shù)名 參數(shù)類型)   
  

• 存儲(chǔ)過程開始和結(jié)束符號(hào):

  BEGIN .... END   
  

• 演示示例：

• -- 聲明語句結(jié)束符，可以自定義:
  delimiter $$
  -- 創(chuàng)建存儲(chǔ)過程，名為  hello_procedure， 參數(shù)為空， 執(zhí)行輸出 'hello world'
  create procedure hello_procedure ()
  beginselect 'hello world';
  end $$
  -- 聲明語句結(jié)束符
  delimiter ;
  -- 調(diào)用春初過程
  call hello_procedure();
  -- 刪除存儲(chǔ)過程
  drop procedure hello_procedure;

2. 變量結(jié)構(gòu)

MySQL變量一共分為兩大類：用戶自定義變量和系統(tǒng)變量。如下：

• 用戶自定義變量（重點(diǎn)）
  • 局部變量
  • 會(huì)話變量
• 系統(tǒng)變量（理解）
  • 會(huì)話變量
  • 全局變量

1. 局部變量

局部變量就是用戶自定義，在begin … end 代碼塊中定義的，也在代碼塊中有效。

語法：declare  變量名  變量類型  [default 默認(rèn)值]
舉例：  declare name varchar(32) '張三'

-- set 賦值
delimiter $$drop procedure if exists var01$$create procedure var01()
begin-- 聲明變量declare username varchar(32) default '張三';-- 為變量賦值set username = '李四';select username;
end $$delimiter ;call var01();

2. 會(huì)話變量

會(huì)話變量即為服務(wù)器為每個(gè)客戶端連接維護(hù)的變量。在客戶端連接時(shí)，使用相應(yīng)全局變量的當(dāng)前值對(duì)客戶端的回話變量進(jìn)行初始化。設(shè)置會(huì)話變量不需要特殊權(quán)限，但客戶端只能更改自己的會(huì)話變量。其作用域與生命周期均限于當(dāng)前客戶端連接。

delimiter $$
drop procedure if exists var02$$-- 創(chuàng)建存儲(chǔ)過程
create procedure var02()
beginset @username='張三';
end $$
delimiter ;-- 調(diào)用存儲(chǔ)過程
call var02();
-- 查詢會(huì)話變量的值
select @username;

@username 會(huì)話變量可以在 begin … end 代碼塊中定義，也可以在外部定義，定義后同一變量名在內(nèi)存中只有一份，且當(dāng)前會(huì)話有效。

3. 入?yún)⒆兞亢统鰠⒆兞?/h5>

• IN 輸入?yún)?shù)

  表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程時(shí)指定，在存儲(chǔ)過程中修改該參數(shù)的值不能被返回，為默認(rèn)值

  僅需要將數(shù)據(jù)傳入存儲(chǔ)過程，并不需要返回計(jì)算后的該值。

  只能當(dāng)做傳入?yún)?shù)

• OUT 輸出參數(shù)

  該值可在存儲(chǔ)過程內(nèi)部被改變，并可返回

  不接受外部傳入的數(shù)據(jù)，僅返回計(jì)算之后的值。

  只能當(dāng)做轉(zhuǎn)出參數(shù)

• INOUT 輸入輸出參數(shù)

  調(diào)用時(shí)指定，并且可被改變和返回

  需要數(shù)據(jù)傳入存儲(chǔ)過程經(jīng)過調(diào)用計(jì)算后，再傳出返回值

  可當(dāng)做傳入轉(zhuǎn)出參數(shù)

---

詳細(xì)演示

1. 聲明語句結(jié)束符，可以自定義:

   delimiter $$

2. 創(chuàng)建一個(gè)會(huì)話變量，用于測(cè)試out對(duì)會(huì)話變量的影響

   set @username=‘wangwu’;

   

3. 如果存在該存儲(chǔ)過程，則刪除

   drop procedure if exists var03$$

4. 創(chuàng)建一個(gè)存儲(chǔ)過程，命名為var03。 該存儲(chǔ)過程是傳入兩個(gè)變量，第一個(gè)為輸入in變量，第二個(gè)為是輸出out變量。當(dāng)輸入用戶id 時(shí)，返回用戶名。

   create procedure var03(in id int,out username varchar(32))
   beginselect id,username;-- select 'procedure into username' into username;
   end $$
   

   

5. 聲明語句結(jié)束符為 ；

   delimiter ;

6. 查詢會(huì)話變量用戶名

   select @username;

   

   在還未調(diào)用存儲(chǔ)過程時(shí)，我們發(fā)現(xiàn)此時(shí)的會(huì)話變量@username的值依然為 wangwu

7. 調(diào)用存儲(chǔ)過程，查看會(huì)話變量值是否正確

   call var03(35,@username);

   

   😤我們會(huì)發(fā)現(xiàn)，此時(shí)查詢到的username會(huì)為空。？？？？？？？？ 這是為什么呢？😤

   這里就是 in 和 out 參數(shù)的區(qū)別了。 也有區(qū)別與Java等高級(jí)語言中的參數(shù)。

   如果是以Java的思想理解調(diào)用存儲(chǔ)過程的話，我們可能會(huì)認(rèn)為說 @username變量的值為wangwu 傳入到 存儲(chǔ)過程函數(shù)中，所以我們也理所應(yīng)當(dāng)?shù)恼J(rèn)為 username的值應(yīng)該為wangwu。

   所以這就是錯(cuò)誤的地方了。 out函數(shù)它只負(fù)責(zé)變量的接收，不負(fù)責(zé)傳入值。 也就是說 調(diào)用call var03(35,@username); 的時(shí)候，@username只是將它的內(nèi)存地址傳進(jìn)去，告訴存儲(chǔ)過程說，等等你把out變量username的值給我（我是@username）。然后在存儲(chǔ)過程中一直都沒有給username進(jìn)行賦值的操作，所以此時(shí)的username的值為null，最終就將null賦值給了 @username。

8. 查詢會(huì)話變量用戶名

   select @username;

   

   看完了步驟7，也就大概了解了為什么@username經(jīng)過存儲(chǔ)過程后就變?yōu)榭樟恕?/p>

9. 刪除之前的存儲(chǔ)過程，打開存儲(chǔ)過程中的注釋，再次運(yùn)行查看結(jié)果。

-- 測(cè)試in入?yún)?out出參set @username = 'wangwu';delimiter $$
drop procedure if exists var03$$create procedure var03(in id int, out username varchar(32))
beginselect id, username;-- select 'procedure into username' into username;-- set username='procedure into username'
end $$delimiter ;select @username;call var03(35, @username);
select @username;

3. 流程控制

在存儲(chǔ)過程和自定義函數(shù)中可以使用流程控制語句來控制程序的流程。MySQL 中流程控制語句有：IF 語句、CASE 語句、LOOP 語句、LEAVE 語句、ITERATE 語句、REPEAT 語句和 WHILE 語句等。

下面將詳細(xì)講解這些流程控制語句。

1. 判斷（IF 語句）

IF 語句用來進(jìn)行條件判斷，根據(jù)是否滿足條件（可包含多個(gè)條件），來執(zhí)行不同的語句，是流程控制中最常用的判斷語句。其語法的基本形式如下：

IF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list]...[ELSE statement_list]
END IF
------------------------------------------------------------------------------------------
IF  判斷條件  THEN 執(zhí)行語句[ELSELF  判斷條件  THEN  執(zhí)行語句][ELSE  執(zhí)行語句]
END IF

其中，search_condition 參數(shù)表示條件判斷語句，如果返回值為 TRUE ，相應(yīng)的 SQL 語句列表（statement_list）被執(zhí)行；如果返回值為 FALSE，則 ELSE 子句的語句列表被執(zhí)行。statement_list 可以包括一個(gè)或多個(gè)語句。

注意：MySQL 中的 IF( ) 函數(shù)不同于這里的 IF 語句。

🐱?🏍劍來

set @username = '張三風(fēng)';
delimiter $$
drop procedure if exists if01$$create procedure if01()
beginIF @username = '張三' THENselect concat('我是',@username);ELSEIF  @username = '張三風(fēng)' THENselect '張三是我徒弟';ELSEselect '我是栗四,不認(rèn)識(shí)張三';END IF;
end $$delimiter ;xx
call if01;

該例子 主要演示的是 根據(jù) username的值顯示不一樣的結(jié)果。結(jié)果很容易理解。

2. 判斷（CASE語句）

CASE 語句也是用來進(jìn)行條件判斷的，它提供了多個(gè)條件進(jìn)行選擇，可以實(shí)現(xiàn)比 IF 語句更復(fù)雜的條件判斷。CASE 語句的基本形式如下：

CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list]...[ELSE statement_list]
END CASE
------------------------------------------------------------------
CASE  參數(shù)變量WHEN  參數(shù)變量1  THEN  執(zhí)行語句][WHEN  參數(shù)變量2  THEN  執(zhí)行語句][ELSE  執(zhí)行語句]
END  CASE 

其中：

• case_value 參數(shù)表示條件判斷的變量，決定了哪一個(gè) WHEN 子句會(huì)被執(zhí)行；

• when_value 參數(shù)表示變量的取值，如果某個(gè) when_value 表達(dá)式與 case_value 變量的值相同，則執(zhí)行對(duì)應(yīng)的 THEN 關(guān)鍵字后的 statement_list 中的語句；

• statement_list 參數(shù)表示 when_value 值沒有與 case_value 相同值時(shí)的執(zhí)行語句。

• CASE 語句都要使用 END CASE 結(jié)束。

• 這個(gè)就有點(diǎn)類似與 Java 中的 switch 語句的使用。 與之區(qū)別就是執(zhí)行時(shí)，只要進(jìn)入其中的一個(gè)判斷語句之后，就不會(huì)在執(zhí)行其他的，也不需要使用過break語句進(jìn)行跳出。

3. 循環(huán)（loop）

語法：

[begin_label:] LOOP statement_list ;
END LOOP [begin_label];
------------------------------------------------------------------
循環(huán)名: loop-- 循環(huán)體執(zhí)行語句;
end loop 循環(huán)名;

反復(fù)執(zhí)行循環(huán)體中的語句，直到循環(huán)結(jié)束。如果沒有l(wèi)eave等關(guān)鍵字，此時(shí)的loop為死循環(huán)。

🐱?🏍劍來

-- -----------關(guān)于loop的示例-------------------
-- 需求： 打印1-10，輸出結(jié)果為 1，2，3，4......9,10
-- 如果存在loop_test，則先刪除
drop procedure if exists loop_test;
delimiter $$
-- 創(chuàng)建存儲(chǔ)過程loop_test
create procedure loop_test()
begindeclare c_index int default 1;declare result_str varchar(256) default '1';cnt: loop-- 循環(huán)10次，則跳出循環(huán)if c_index >= 10 thenleave cnt;end if;-- 自增set c_index = c_index + 1;-- 拼接select concat(result_str, ',', c_index) into result_str;end loop cnt;-- 查詢結(jié)果select result_str;
end $$delimiter ;
call loop_test();

• leave 就相當(dāng)于Java中的break語句，用于跳出當(dāng)前循環(huán)

上述例子，類比Java代碼如下：

    public void loop_test() {String result_str = "1";int c_index = 1;while (true) {if (c_index >= 10) {break;}c_index = c_index + 1;result_str = result_str + "," + c_index;}System.out.println(result_str);}

4. 循環(huán)（while）