概述

由MySQL5.0 版本開(kāi)始支持存儲(chǔ)過(guò)程。

如果在實(shí)現(xiàn)用戶的某些需求時(shí)，需要編寫(xiě)一組復(fù)雜的SQL語(yǔ)句才能實(shí)現(xiàn)的時(shí)候，那么我們就可以將這組復(fù)雜的SQL語(yǔ)句集提前編寫(xiě)在數(shù)據(jù)庫(kù)中，由JDBC調(diào)用來(lái)執(zhí)行這組SQL語(yǔ)句。把編寫(xiě)在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句集稱為存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程：（PROCEDURE）是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合。調(diào)用存儲(chǔ)過(guò)程可以簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是很有好處的。

就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用。

存儲(chǔ)過(guò)程就類(lèi)似于Java中的方法，需要先定義，使用時(shí)需要調(diào)用。存儲(chǔ)過(guò)程可以定義參數(shù)，參數(shù)分為IN、OUT、INOUT類(lèi)型三種類(lèi)型。

1. IN類(lèi)型的參數(shù)表示接受調(diào)用者傳入的數(shù)據(jù)；
2. OUT類(lèi)型的參數(shù)表示向調(diào)用者返回?cái)?shù)據(jù)；
3. INOUT類(lèi)型的參數(shù)即可以接受調(diào)用者傳入的參數(shù)，也可以向調(diào)用者返回?cái)?shù)據(jù)。

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

1. 存儲(chǔ)過(guò)程是通過(guò)處理封裝在容易使用的單元中，簡(jiǎn)化了復(fù)雜的操作。
2. 簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名、或業(yè)務(wù)邏輯有了變化。只需要更改存儲(chǔ)過(guò)程的代碼。使用它的人不用更改自己的代碼。
3. 通常存儲(chǔ)過(guò)程都是有助于提高應(yīng)用程序的性能。當(dāng)創(chuàng)建的存儲(chǔ)過(guò)程被編譯之后，就存儲(chǔ)在數(shù)據(jù)庫(kù)中。
4. 但是，MySQL實(shí)現(xiàn)的存儲(chǔ)過(guò)程略有所不同。
5. MySQL存儲(chǔ)過(guò)程是按需編譯。在編譯存儲(chǔ)過(guò)程之后，MySQL將其放入緩存中。
6. MySQL為每個(gè)連接維護(hù)自己的存儲(chǔ)過(guò)程高速緩存。如果應(yīng)用程序在單個(gè)連接中多次使用存儲(chǔ)過(guò)程，則使用編譯版本，否則存儲(chǔ)過(guò)程的工作方式類(lèi)似于查詢。
7. 存儲(chǔ)過(guò)程有助于減少應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器之間的流量。
8. 因?yàn)?/span>
9. 應(yīng)運(yùn)
10. 程序不必發(fā)送多個(gè)冗長(zhǎng)的SQL語(yǔ)句，只用發(fā)送存儲(chǔ)過(guò)程中的名稱和參數(shù)即可。
11. 存儲(chǔ)過(guò)程度任何應(yīng)用程序都是可重用的和透明的。存儲(chǔ)過(guò)程將數(shù)據(jù)庫(kù)接口暴露給所有的應(yīng)用程序，以方便開(kāi)發(fā)人員不必開(kāi)發(fā)存儲(chǔ)過(guò)程中已支持的功能。
12. 存儲(chǔ)的程序是安全的。數(shù)據(jù)庫(kù)管理員是可以向訪問(wèn)數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的應(yīng)用程序授予適當(dāng)?shù)臋?quán)限，而不是向基礎(chǔ)數(shù)據(jù)庫(kù)表提供任何權(quán)限。

缺點(diǎn)

1. 如果使用大量的存儲(chǔ)過(guò)程，那么使用這些存儲(chǔ)過(guò)程的每個(gè)連接的內(nèi)存使用量將大大增加。
2. 此外，如果在存儲(chǔ)過(guò)程中過(guò)度使用大量的邏輯操作，那么CPU的使用率也在增加，因?yàn)镸ySQL數(shù)據(jù)庫(kù)最初的設(shè)計(jì)就側(cè)重于高效的查詢，而不是邏輯運(yùn)算。
3. 存儲(chǔ)過(guò)程的構(gòu)造使得開(kāi)發(fā)具有了復(fù)雜的業(yè)務(wù)邏輯的存儲(chǔ)過(guò)程變得困難。
4. 很難調(diào)試存儲(chǔ)過(guò)程。只有少數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)允許調(diào)試存儲(chǔ)過(guò)程。不幸的是，MySQL不提供調(diào)試存儲(chǔ)過(guò)程的功能。
5. 開(kāi)發(fā)和維護(hù)存儲(chǔ)過(guò)程都不容易。
6. 開(kāi)發(fā)和維護(hù)存儲(chǔ)過(guò)程通常需要一個(gè)不是所有應(yīng)用程序開(kāi)發(fā)人員擁有的專業(yè)技能。這可能導(dǎo)致應(yīng)用程序開(kāi)發(fā)和維護(hù)階段的問(wèn)題。
7. 對(duì)數(shù)據(jù)庫(kù)依賴程度較高，移值性差。

MySQL存儲(chǔ)過(guò)程的定義

存儲(chǔ)過(guò)程的基本語(yǔ)句格式

DELIMITER $$


CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 數(shù)據(jù)庫(kù)名.存儲(chǔ)過(guò)程名([in變量名 類(lèi)型,out 參數(shù) 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
  BEGIN
    [DECLARE 變量名 類(lèi)型 [DEFAULT 值];]
    存儲(chǔ)過(guò)程的語(yǔ)句塊;
  END$$


DELIMITER ;

存儲(chǔ)過(guò)程中的參數(shù)分別是 in，out，inout三種類(lèi)型；

1. in代表輸入?yún)?shù)（默認(rèn)情況下為in參數(shù)），表示該參數(shù)的值必須由調(diào)用程序指定。
2. ou代表輸出參數(shù)，表示該參數(shù)的值經(jīng)存儲(chǔ)過(guò)程計(jì)算后，將out參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序。
3. inout代表即時(shí)輸入?yún)?shù)，又是輸出參數(shù)，表示該參數(shù)的值即可有調(diào)用程序制定，又可以將inout參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序

存儲(chǔ)過(guò)程中的語(yǔ)句必須包含在BEGIN和END之間。

DECLARE中用來(lái)聲明變量，變量默認(rèn)賦值使用的DEFAULT，語(yǔ)句塊中改變變量值，使用SET 變量=值；

存儲(chǔ)過(guò)程的使用

定義一個(gè)存儲(chǔ)過(guò)程

DELIMITER $$


CREATE
    PROCEDURE `demo`.`demo1`()
  -- 存儲(chǔ)過(guò)程體
  BEGIN
    -- DECLARE聲明 用來(lái)聲明變量的
    DECLARE de_name VARCHAR(10) DEFAULT '';
    
    SET de_name = "jim";
    
    -- 測(cè)試輸出語(yǔ)句（不同的數(shù)據(jù)庫(kù)，測(cè)試語(yǔ)句都不太一樣。
    SELECT de_name;
  END$$


DELIMITER ;

調(diào)用存儲(chǔ)過(guò)程

CALL demo1();

定義一個(gè)有參數(shù)的存儲(chǔ)過(guò)程

先定義一個(gè)student數(shù)據(jù)庫(kù)表：

現(xiàn)在要查詢這個(gè)student表中的sex為男的有多少個(gè)人。

DELIMITER $$


CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
  -- 存儲(chǔ)過(guò)程體
  BEGIN
    -- 把SQL中查詢的結(jié)果通過(guò)INTO賦給變量
    SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
    SELECT s_count;
    
  END$$
DELIMITER ;

調(diào)用這個(gè)存儲(chǔ)過(guò)程

-- @s_count表示測(cè)試出輸出的參數(shù)
CALL demo2 ('男',@s_count);

定義一個(gè)流程控制語(yǔ)句 IF ELSE

IF 語(yǔ)句包含多個(gè)條件判斷，根據(jù)結(jié)果為 TRUE、FALSE執(zhí)行語(yǔ)句，與編程語(yǔ)言中的 if、else if、else 語(yǔ)法類(lèi)似。

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
  -- 存儲(chǔ)過(guò)程體
  BEGIN
    IF `day` = 0 THEN
    SELECT '星期天';
    ELSEIF `day` = 1 THEN
    SELECT '星期一';
    ELSEIF `day` = 2 THEN
    SELECT '星期二';
    ELSE
    SELECT '無(wú)效日期';
    END IF;
    
  END$$
DELIMITER ;

調(diào)用這個(gè)存儲(chǔ)過(guò)程

CALL demo3(2);

定義一個(gè)條件控制語(yǔ)句 CASE

case是另一個(gè)條件判斷的語(yǔ)句，類(lèi)似于編程語(yǔ)言中的 choose、when語(yǔ)法。MySQL 中的 case語(yǔ)句有兩種語(yǔ)法格式。

1. 第一種

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
  BEGIN
    CASE -- 條件開(kāi)始
  
    WHEN num<0 THEN 
      SELECT '負(fù)數(shù)';
    WHEN num>0 THEN 
      SELECT '正數(shù)';
    ELSE 
    SELECT '不是正數(shù)也不是負(fù)數(shù)';
  
    END CASE; -- 條件結(jié)束
  END$$
DELIMITER;

調(diào)用這個(gè)存儲(chǔ)過(guò)程

CALL demo4(1);

2.第二種

DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
  BEGIN
    CASE num  -- 條件開(kāi)始
    WHEN 1 THEN 
      SELECT '輸入為1';
    WHEN 0 THEN 
      SELECT '輸入為0';
    ELSE 
    SELECT '不是1也不是0';
    END CASE; -- 條件結(jié)束
  END$$
DELIMITER;

調(diào)用此函數(shù)

CALL demo5(0);

定義一個(gè)循環(huán)語(yǔ)句 WHILE

DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
  BEGIN
       SET SUM = 0;
       WHILE num<10 DO -- 循環(huán)開(kāi)始
           SET num = num+1;
           SET SUM = SUM+num;
           END WHILE; -- 循環(huán)結(jié)束
  END$$
DELIMITER;

調(diào)用此函數(shù)

-- 調(diào)用函數(shù)
CALL demo6(0,@sum);


-- 查詢函數(shù)
SELECT @sum;

定義一個(gè)循環(huán)語(yǔ)句 REPEAT UNTLL

REPEATE…UNTLL 語(yǔ)句的用法和 Java中的 do…while 語(yǔ)句類(lèi)似，都是先執(zhí)行循環(huán)操作，再判斷條件，區(qū)別是REPEATE 表達(dá)式值為 false時(shí)才執(zhí)行循環(huán)操作，直到表達(dá)式值為 true停止。

-- 創(chuàng)建過(guò)程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
  BEGIN
       SET SUM = 0;
       REPEAT-- 循環(huán)開(kāi)始
    SET num = num+1;
    SET SUM = SUM+num ;
    UNTIL num>=10
    END REPEAT; -- 循環(huán)結(jié)束
  END$$
DELIMITER;

調(diào)用此函數(shù)

CALL demo7(9,@sum);


SELECT @sum;

定義一個(gè)循環(huán)語(yǔ)句 LOOP

循環(huán)語(yǔ)句，用來(lái)重復(fù)執(zhí)行某些語(yǔ)句。

執(zhí)行過(guò)程中可使用 LEAVE語(yǔ)句或者ITEREATE來(lái)跳出循環(huán)，也可以嵌套IF等判斷語(yǔ)句。

1. LEAVE 語(yǔ)句效果對(duì)于Java中的break，用來(lái)終止循環(huán)；
2. ITERATE語(yǔ)句效果相當(dāng)于Java中的continue，用來(lái)跳過(guò)此次循環(huán)。進(jìn)入下一次循環(huán)。且ITERATE之下的語(yǔ)句將不在進(jìn)行。

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
  BEGIN
       SET SUM = 0;
       demo_sum:LOOP-- 循環(huán)開(kāi)始
    SET num = num+1;
    IF num > 10 THEN
        LEAVE demo_sum; -- 結(jié)束此次循環(huán)
    ELSEIF num <= 9 THEN
        ITERATE demo_sum; -- 跳過(guò)此次循環(huán)
    END IF;
    
    SET SUM = SUM+num;
    END LOOP demo_sum; -- 循環(huán)結(jié)束
  END$$
DELIMITER;

調(diào)用此函數(shù)

CALL demo8(0,@sum);


SELECT @sum;

使用存儲(chǔ)過(guò)程插入信息

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
  BEGIN
     -- 聲明一個(gè)變量 用來(lái)決定這個(gè)名字是否已經(jīng)存在
     DECLARE s_count INT DEFAULT 0;
     -- 驗(yàn)證這么名字是否已經(jīng)存在
     SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;  
     IF s_count = 0 THEN
          INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
    SET s_result = '數(shù)據(jù)添加成功';
     ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
     END IF;
  END$$
DELIMITER;

調(diào)用此函數(shù)

CALL demo9("Jim","女",@s_result);

再次調(diào)用次函數(shù)

CALL demo9("Jim","女",@s_result)

存儲(chǔ)過(guò)程的管理

顯示存儲(chǔ)過(guò)程

SHOW PROCEDURE STATUS

顯示特定數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

顯示特定模式的存儲(chǔ)過(guò)程

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

顯示存儲(chǔ)過(guò)程的源碼

SHOW CREATE PROCEDURE 存儲(chǔ)過(guò)程名;

刪除存儲(chǔ)過(guò)程

DROP PROCEDURE 存儲(chǔ)過(guò)程名;

后端調(diào)用存儲(chǔ)過(guò)程的實(shí)現(xiàn)

在mybatis當(dāng)中，調(diào)用存儲(chǔ)過(guò)程

<parameterMap type="savemap" id=“usermap"> 
  <parameter property="name" jdbcType="VARCHAR" mode="IN"/>
  <parameter property="sex" jdbcType="CHAR" mode="IN"/>
  <parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>


<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> 
{call saveuser(?, ?, ?)} 
</insert >

調(diào)用數(shù)據(jù)庫(kù)管理

HashMap<String, Object> map = new HashMap<String, Object>(); 
  map.put("name", "Jim"); 
  map.put("sex","男");
  userDao.saveUserDemo(map); 
  map.get(“result”);//獲得輸出參數(shù)

通過(guò)這樣就可以調(diào)用數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程的結(jié)果。