MySQL的預存程序怎麼調用

概述

由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 &#39;负数&#39;;
		WHEN num>0 THEN 
			SELECT &#39;正数&#39;;
		ELSE 
		SELECT &#39;不是正数也不是负数&#39;;
	
		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的預存程序怎麼調用的詳細內容。更多資訊請關注PHP中文網其他相關文章！