MySQL中存儲過程的詳細詳解

Posted on by

概述

由MySQL5.0 版本開始支持存儲過程。

如果在實現用戶的某些需求時，需要編寫一組復雜的SQL語句才能實現的時候，那麼我們就可以將這組復雜的SQL語句集提前編寫在數據庫中，由JDBC調用來執行這組SQL語句。把編寫在數據庫中的SQL語句集稱為存儲過程。

存儲過程：（PROCEDURE）是事先經過編譯並存儲在數據庫中的一段SQL語句的集合。調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對於提高數據處理的效率是很有好處的。

就是數據庫 SQL 語言層面的代碼封裝與重用。

存儲過程就類似於Java中的方法，需要先定義，使用時需要調用。存儲過程可以定義參數，參數分為IN、OUT、INOUT類型三種類型。

  • IN類型的參數表示接受調用者傳入的數據；
  • OUT類型的參數表示向調用者返回數據；
  • INOUT類型的參數即可以接受調用者傳入的參數，也可以向調用者返回數據。

優點

  1. 存儲過程是通過處理封裝在容易使用的單元中，簡化瞭復雜的操作。
  2. 簡化對變動的管理。如果表名、列名、或業務邏輯有瞭變化。隻需要更改存儲過程的代碼。使用它的人不用更改自己的代碼。
  3. 通常存儲過程都是有助於提高應用程序的性能。當創建的存儲過程被編譯之後，就存儲在數據庫中。
    但是，MySQL實現的存儲過程略有所不同。
    MySQL存儲過程是按需編譯。在編譯存儲過程之後，MySQL將其放入緩存中。
    MySQL為每個連接維護自己的存儲過程高速緩存。如果應用程序在單個連接中多次使用存儲過程，則使用編譯版本，否則存儲過程的工作方式類似於查詢。
  4. 存儲過程有助於減少應用程序和數據庫服務器之間的流量。
    因為應運程序不必發送多個冗長的SQL語句，隻用發送存儲過程中的名稱和參數即可。
  5. 存儲過程度任何應用程序都是可重用的和透明的。存儲過程將數據庫接口暴露給所有的應用程序，以方便開發人員不必開發存儲過程中已支持的功能。
  6. 存儲的程序是安全的。數據庫管理員是可以向訪問數據庫中存儲過程的應用程序授予適當的權限，而不是向基礎數據庫表提供任何權限。

缺點

  1. 如果使用大量的存儲過程，那麼使用這些存儲過程的每個連接的內存使用量將大大增加。
    此外，如果在存儲過程中過度使用大量的邏輯操作，那麼CPU的使用率也在增加，因為MySQL數據庫最初的設計就側重於高效的查詢，而不是邏輯運算。
  2. 存儲過程的構造使得開發具有瞭復雜的業務邏輯的存儲過程變得困難。
  3. 很難調試存儲過程。隻有少數數據庫管理系統允許調試存儲過程。不幸的是，MySQL不提供調試存儲過程的功能。
  4. 開發和維護存儲過程都不容易。
    開發和維護存儲過程通常需要一個不是所有應用程序開發人員擁有的專業技能。這可能導致應用程序開發和維護階段的問題。
  5. 對數據庫依賴程度較高，移值性差。

MySQL存儲過程的定義

存儲過程的基本語句格式

DELIMITER $$

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

DELIMITER ;

● 存儲過程中的參數分別是 in，out，inout三種類型；

  1. in代表輸入參數（默認情況下為in參數），表示該參數的值必須由調用程序指定。
  2. ou代表輸出參數，表示該參數的值經存儲過程計算後，將out參數的計算結果返回給調用程序。
  3. inout代表即時輸入參數，又是輸出參數，表示該參數的值即可有調用程序制定，又可以將inout參數的計算結果返回給調用程序。

● 存儲過程中的語句必須包含在BEGIN和END之間。

● DECLARE中用來聲明變量，變量默認賦值使用的DEFAULT，語句塊中改變變量值，使用SET 變量=值；

存儲過程的使用

定義一個存儲過程

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo1`()
	-- 存儲過程體
	BEGIN
		-- DECLARE聲明 用來聲明變量的
		DECLARE de_name VARCHAR(10) DEFAULT '';
		
		SET de_name = "jim";
		
		-- 測試輸出語句（不同的數據庫，測試語句都不太一樣。
		SELECT de_name;
	END$$

DELIMITER ;

調用存儲過程

CALL demo1();

定義一個有參數的存儲過程

先定義一個student數據庫表：

現在要查詢這個student表中的sex為男的有多少個人。

DELIMITER $$

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

調用這個存儲過程

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

定義一個流程控制語句 IF ELSE

IF 語句包含多個條件判斷，根據結果為 TRUE、FALSE執行語句，與編程語言中的 if、else if、else 語法類似。

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

調用這個存儲過程

CALL demo3(2);

定義一個條件控制語句 CASE

case是另一個條件判斷的語句，類似於編程語言中的 choose、when語法。MySQL 中的 case語句有兩種語法格式。

第一種

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

調用這個存儲過程

CALL demo4(1);

2.第二種

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

調用此函數

CALL demo5(0);

定義一個循環語句 WHILE

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

調用此函數

-- 調用函數
CALL demo6(0,@sum);

-- 查詢函數
SELECT @sum;

定義一個循環語句 REPEAT UNTLL

REPEATE…UNTLL 語句的用法和 Java中的 do…while 語句類似，都是先執行循環操作，再判斷條件，區別是REPEATE 表達式值為 false時才執行循環操作，直到表達式值為 true停止。

-- 創建過程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循環開始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循環結束
	END$$
DELIMITER;

調用此函數

CALL demo7(9,@sum);

SELECT @sum;

定義一個循環語句 LOOP

循環語句，用來重復執行某些語句。

執行過程中可使用 LEAVE語句或者ITEREATE來跳出循環，也可以嵌套IF等判斷語句。

LEAVE 語句效果對於Java中的break，用來終止循環；

ITERATE語句效果相當於Java中的continue，用來跳過此次循環。進入下一次循環。且ITERATE之下的語句將不在進行。

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

調用此函數

CALL demo8(0,@sum);

SELECT @sum;

使用存儲過程插入信息

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 聲明一個變量 用來決定這個名字是否已經存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 驗證這麼名字是否已經存在
	   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 = '數據添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END$$
DELIMITER;

調用此函數

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

再次調用次函數

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

存儲過程的管理

顯示存儲過程

SHOW PROCEDURE STATUS

顯示特定數據庫的存儲過程

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

顯示特定模式的存儲過程

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

顯示存儲過程的源碼

SHOW CREATE PROCEDURE 存儲過程名;

刪除存儲過程

DROP PROCEDURE 存儲過程名;

後端調用存儲過程的實現

在mybatis當中，調用存儲過程

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

調用數據庫管理

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

通過這樣就可以調用數據庫中的存儲過程的結果。

總結

到此這篇關於MySQL中存儲過程的文章就介紹到這瞭,更多相關MySQL存儲過程內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：