MySQL——存儲過程詳解及執行個體分析

一、儲存過程簡介

1、什麼是存儲過程

2、存儲過程優缺點

3、存儲過程入門程式

4、在idea中如何調用儲存過程？

二、存儲過程程式設計

1、存儲過程的變量

2、存儲過程中的參數

3、選擇結構if

4、分支結構case

5、3個循環結構

6、存儲過程的異常處理

7、MySQL遊标

三、存儲過程執行個體講解

1、案例1：取消訂單

2、案例2：根據商品分類擷取商品詳情

一、儲存過程簡介

1、什麼是存儲過程

存儲過程（Stored Procedure）是一種在資料庫中存儲複雜程式，以便外部程式調用的一種資料庫對象。存儲過程是為了完成特定功能的SQL語句集，經編譯建立并儲存在資料庫中，使用者可通過指定存儲過程的名字并給定參數(需要時)來調用執行。

存儲過程思想上很簡單，就是資料庫 SQL 語言層面的代碼封裝與重用。

2、存儲過程優缺點

優點

1.存儲過程隻在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL 語句每執行一次就編譯一次,是以使用存儲過程可提高資料庫執行速度。

2.存儲過程可以重複使用,可減少資料庫開發人員的工作量(可以忽略)

3.安全性高,可設定隻有某此使用者才具有對指定存儲過程的使用權

4. 減少網絡通信量、 Java與mysql的IO互動。調用一個行數不多的存儲過程與直接調用SQL 語句的網絡通信量可能不會有很大的差别，可是如果存儲過程包含上百行SQL 語句，那麼其性能絕對比一條一條的調用SQL 語句要高得多。

缺點

1、不可移植性，每種資料庫的内部程式設計文法都不太相同，當你的系統需要相容多種資料庫時最好不要用存儲過程。

2、學習成本高,沒有java代碼debug友善

3、業務邏輯多處存在，采用存儲過程後也就意味着你的系統有一些業務邏輯不是在應用程式裡處理，這種架構會增加一些系統維護和調試成本。業務邏輯集中管理會更易于維護與調試，你很難做到業務邏輯都放在存儲過程裡，比如關于一些客戶輸入數當據的簡單校驗，會話資料的校驗，應用伺服器緩存資料的校驗。

4、運作速度：大多數進階的資料庫系統都有statement cache的，是以編譯sql的花費沒什麼影響。但是執行存儲過程要比直接執行sql花費更多（檢查權限等），是以對于很簡單的sql，存儲過程沒有什麼優勢。

5、存儲占實體記憶體，如果使用大量存儲過程，那麼使用這些存儲過程的每個連接配接的記憶體使用量将會大大增加。此外，如果您在存儲過程中過度使用大量邏輯操作，則CPU使用率也會增加，因為資料庫伺服器的設計不當于邏輯運算。

3、存儲過程入門程式

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER

對上述存儲過程的詳解：

第一個指令是 DELIMITER // ，它與存儲過程文法無關。 DELIMITER 語句将标準分隔符 - 分号( ; )更改為： // 。在這種情況下，分隔符從分号( ; )更改為雙斜杠 // 。為什麼我們必須更改分隔符？因為我們想将存儲過程作為整體傳遞給伺服器，而不是讓mysql工具一次解釋每個語句。在 END 關鍵字之後，使用分隔符 // 來訓示存儲過程的結束。最後一個指令( DELIMITER; )将分隔符更改回分号( ; )。

使用 CREATE PROCEDURE 語句建立一個新的存儲過程。在 CREATE PROCEDURE 語句之後指定存儲過程的名稱。在這個示例中，存儲過程的名稱為： GetAllProducts ，并把括号放在存儲過程的名字之後。

BEGIN 和 END 之間的部分稱為存儲過程的主體。将聲明性SQL語句放在主體中以處理業務邏輯。在這個存儲過程中，我們使用一個簡單的SELECT語句來查詢 products 表中的資料。

要調用存儲過程，可以使用以下SQL指令：

CALL GetAllProducts();

4、在idea中如何調用儲存過程？

<select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">



call find_category_id( #{cid,mode=IN,jdbcType=INTEGER} )

</select>

<select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">

call find_category_id( #{cid,mode=IN,jdbcType=INTEGER} ，

#{id,mode=OUT,jdbcType=INTEGER} )

</select>

其他指令：

檢視已有的procedure

show procedure status \G

删除存儲過程

drop procedure 名字

二、存儲過程程式設計

1、存儲過程的變量

聲明變量：要在存儲過程中聲明一個變量，可以使用

DECLARE

語句

DECLARE variable_name datatype(size) DEFAULT default_value;

首先，在 DECLARE 關鍵字後面要指定變量名。變量名必須遵循MySQL表列名稱的命名規則。

其次，指定變量的資料類型及其大小。變量可以有任何MySQL資料類型，如 INT ， VARCHAR ， DATETIME 等。

第三，當聲明一個變量時，它的初始值為 NULL 。但是可以使用 DEFAULT 關鍵字為變量配置設定預設值。

如：DECLARE test INT;

配置設定變量值：要為變量配置設定一個值，可以使用

SET

語句，或者使用

SELECT INTO

語句将查詢的結果配置設定給一個變量

DECLARE total_count INT DEFAULT 0;

SET total_count := 10;

或者：

SELECT COUNT(*) INTO total_count FROM products

變量範圍(作用域)

一個變量有自己的範圍(作用域)，它用來定義它的生命周期。如果在存儲過程中聲明一個變量，那麼當達到存儲過程的 END
語句時，它将超出範圍，是以在其它代碼塊中無法通路。

如果您在
BEGIN END 塊内聲明一個變量，那麼如果達到 END
，它将超出範圍。可以在不同的作用域中聲明具有相同名稱的兩個或多個變量，因為變量僅在自己的作用域中有效。但是，在不同範圍内聲明具有相同名稱的變量不是很好的程式設計習慣。

以
@ 符号開頭的變量是會話變量。直到會話結束前它可用和可通路。

:=與=的差別：

1.=

隻有在set和update時才是和:=一樣，指派的作用，其它都是等于的作用。鑒于此，用變量實作行号時，必須用:=

2.:=

不隻在set和update時時指派的作用，在select也是指派的作用。

如果明白了=和:=的差別，那麼也就了解了下邊的現象。

@num:[email protected]+1,:=是指派的作用，是以，先執行@num+1,然後再指派給@num，是以能正确實作行号的作用。

@[email protected]+1,此時=是等于的作用，@num不等于@num+1，是以始終傳回0，如果改為@[email protected],始終傳回1了。mysql資料庫中，用1表示真，0表示假。

2、存儲過程中的參數

在MySQL中，參數有三種模式：

IN

，

OUT

或

INOUT

。

IN - 是預設模式。(可寫可不寫) 在存儲過程中定義 IN 參數時，調用程式必須将參數傳遞給存儲過程。另外， IN 參數的值被保護。這意味着即使在存儲過程中更改了 IN 參數的值，在存儲過程結束後仍保留其原始值。換句話說，存儲過程隻使用 IN 參數的副本。

OUT - 可以在存儲過程中更改 OUT 參數的值，并将其更改後新值傳遞回調用程式。請注意，存儲過程在啟動時無法通路 OUT 參數的初始值。

INOUT - INOUT 參數是 IN 和 OUT 參數的組合。這意味着調用程式可以傳遞參數，并且存儲過程可以修改 INOUT 參數并将新值傳遞回調用程式。

#需要注意類型是寫在後面的

CREATE PROCEDURE my_test5(IN i INT, OUT message VARCHAR(20))

3、選擇結構if

因為很類似于Java，此處直接放執行個體了，詳解介紹可以參考：MySQL教程——存儲過程的if語句

delimiter $$
create procedure p4()
begin
declare age int default 18;
if age >=18 then
select concat('已經成年');
else
select concat('未成年');
end if;#(隻是結束if 在存儲過程中的if、循環語句等基本都需要結束)

end $$


參數的傳遞
#求矩形的面積
delimiter $$
create procedure p5(width int,height int)
begin
	select concat('你的面積是',width*height) as area;
	if width>height then
	select '胖';
	elseif width < height then
	select '瘦';
    else
    select '方';
   end if;
end $$

4、分支結構case

case……when ~then ~

delimiter $$
create procedure p9()
begin
declare pos int default 0;
case pos  #相當于switch（）
	when 1 then select 1;#相當于case
	when 2 then select 2;
	when 3 then select 3;
	else select 'ojbk'; #相當于default
end case;
end $$

帶參數
delimiter $$
create procedure p10(num int)
begin
case num
when 1 then select 1;
when 2 then select 2;
when 3 then select 3;
else select 'ojbk';
end case;
end $$
注意 參考java中的switch\case語句

5、3個循環結構

while ~~~do

----》執行個體1：
#求1-100之和
delimiter $
create procedure p6()
begin
	declare total int default 0;
	declare num int default 0;
while num<=100 do
		set total := total +num;
		set num = num+1;
end while;
select total;
end$

----》執行個體2：
增強： 求1~N的何 N由使用者傳入
create  procedure p7(in n int)
begin
	declare total int default 0;
	declare num  int default 0;
while num<=n do	
	set total = total +num;
set num = num+1;
end while;

select total;
end$


----》執行個體3：
增強：out
create  procedure p8(in n int,out total int)
begin
	#設定初始值 如果不設定初始值 結果為NULL
	declare num  int default 0;
	set total = 0;
while num<=n do	
	set total = total +num;
	set num = num+1;
end while;
end$

#參數一：直接輸入 參數二 定義一個變量 接受total的結果
call p8(100,@sum);

#查詢接受參數
select @sum$

repeat

文法結構
repeat
		sql1
		sql2
until（直到） 條件 

repeat和until之間就是循環體

create procedure p11()
begin
declare i int default 0;
declare total int default 0;
repeat #開始循環
	set i = i+1;
	set total = total+i;
until i>=100#當i大于等于100就執行下面的 結束循環
end repeat;
select total;
end$

loop

#loop 用法  
DELIMITER //
CREATE PROCEDURE my_test4()
BEGIN
     DECLARE i INT DEFAULT 100;
     loop_label: LOOP  #相當于java的标記 用于多重循環，loop表示開始循環 也可以不用标記 這兒一起舉例了
        INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手機',i),i*10,i*100);
       SET i=i+1;
        if i>=105 THEN  #當i大于105的時候 執行下面的
            leave loop_label; #離開标記處的循環
        END IF; #結束if 再次提示 注意
    END LOOP; #離開循環 文法
    END ;// #簡寫 delimiter

 #調用 
 CALL my_test4();

6、存儲過程的異常處理

文法：

01）當sql出現異常的時候跳出存儲過程并設定值為xxx

Declare exit handler for sqlException set …..

02）當sql出現異常的時候繼續存儲過程并設定值為xxx

Declare continue handler for sqlException set …..

-- continue繼續 exit 退出 HANDLER執行處理器 SQLEXCEPTION:sql異常

程式設計執行個體：（結合事務使用）

#存儲過程異常處理
DELIMITER//
CREATE PROCEDURE my_test7()
BEGIN
DECLARE i INT DEFAULT 0;
#發生SQL異常時，程式繼續，并設定i=-1；
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i:=-1;
START TRANSACTION;#開啟事務
UPDATE xmcc_product SET p_name='蘋果' WHERE p_id=10000;
INSERT INTO xmcc_product VALUES(10000,'主鍵沖突',10,100); 
IF i=-1 THEN
SELECT '出現異常，事務復原';
ROLLBACK; #復原
ELSE
COMMIT; #送出
END IF;
END//
DELIMITER ;
#調用 
CALL my_test7();
#檢視結果
SELECT * FROM xmcc_product

7、MySQL遊标

遊标介紹：（處理存儲過程中的結果集）

1）有資料緩沖的思想：遊标的設計是一種資料緩沖區的思想，用來存放SQL語句執行的結果。

2）先有資料基礎：遊标是在先從資料表中檢索出資料之後才能繼續靈活操作的技術。

3）類似于指針：遊标類似于指向資料結構堆棧中的指針，用來pop出所指向的資料，并且隻能每次取一個

使用文法：

1）declare 聲明；（遊标聲明必須在變量聲明之後。如果在變量聲明之前聲明遊标，MySQL将會發出一個錯誤。遊标必須始終與SELECT語句相關聯。）

   declare 遊标名 cursor for 查詢語句

2）open 打開；

open 遊标名

3）fetch 取值；

       fetch 遊标名 into val1,val2,val3……

4）close 關閉；

       close 遊标名

實用執行個體：（該執行個體非常重要，請重點了解）

DELIMITER //
CREATE PROCEDURE my_test8()
BEGIN
DECLARE result VARCHAR(100) DEFAULT '';-- 定義一個result 來存儲商品名稱的拼接
DECLARE product_name VARCHAR(10);-- 定義一個變量來接收每次遊标的商品名稱
DECLARE done INT DEFAULT FALSE; -- 定義done變量 預設值為false
-- 查詢所有的商品名稱放入遊标中 CURSOR 表示該變量為遊标類型
DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;
-- 遊标周遊結束 會出現not found 設定done的值為true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
OPEN curl;-- 打開遊标
WHILE(NOT done) DO
FETCH curl INTO product_name; -- 彈出遊标的一條資料指派給product_name
SET result:=CONCAT(result,',',product_name);-- 拼接字元串
END WHILE;
CLOSE curl;-- 關閉遊标 就像java中需要關閉resultset一樣
SELECT result;-- 列印出結果
END;//
#調用
CALL my_test8();

三、存儲過程執行個體講解

1、案例1：取消訂單

案例:當取消訂單的時候

1.修改訂單狀态為取消(1)

2.對訂單項裡面的商品增加庫存

存儲過程編寫及分析（分析請看注解）

#編寫取消訂單的存儲過程
DELIMITER $$
#輸入參數為訂單order_id 輸出參數為result 1 代表存儲過程成功 -1代表存儲過程出現異常
CREATE PROCEDURE cancel_order(IN order_id VARCHAR(30),OUT result INT)
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result:=-1;#如果出現異常設定傳回值為-1
START TRANSACTION;#開啟事務
SET result:=1;#設定初始值 這裡設定為1
UPDATE xmcc_order SET o_statu=1 WHERE o_id=order_id; #修改訂單狀态
     BEGIN 
	  DECLARE done INT DEFAULT TRUE;#定義變量done用來判斷
	  DECLARE product_id VARCHAR(30); #定義變量 來接收遊标的商品id
	  DECLARE quantity_1 INT; #定義變量來接收遊标的商品數量
	  #定義遊标 存儲根據訂單id查詢到訂單項中的商品id與數量
	  DECLARE cur CURSOR FOR SELECT p_id,quantity FROM xmcc_orderdetail WHERE o_id=order_id;
	  #當遊标循環結束 設定done的值為false
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=FALSE;
	  OPEN cur;#打開遊标
	  WHILE done DO #循環
	  FETCH cur INTO product_id,quantity_1;#将遊标的值設定到變量中
	  IF done THEN #判斷 不然會多修改一次
	  UPDATE xmcc_product SET p_stock=p_stock+quantity_1 WHERE p_id=product_id;
	  END IF;
	  END WHILE;
	  CLOSE cur;
     END ;
 IF result=1 THEN #沒有出現異常就送出
 COMMIT;
 ELSE 
 ROLLBACK;
 END IF;
 END ; $$
 #測試
CALL cancel_order('20001',@result);
#檢視結果
SELECT @result

2、案例2：根據商品分類擷取商品詳情

項目資料背景：

資料庫資料：

   1、産品分類表：xmcc_category。資料内容結構（父—子結構）：A（a1,a2,a3）B（b1,b2,b3）

   2、産品詳情表：xmcc_product 有對應的category_id

項目需求：

   根據category_id擷取到産品詳情的集合。

邏輯分析：

   如：查詢的category_id=A

   1）根據category_id=A 從表xmcc_category擷取到所有的分類id集合list1（A，a1,a2,a3）

   2）根據list1從表xmcc_product 擷取對應的産品詳情list2<xmcc_product >

存儲過程程式設計：

DELIMITER //
#傳入參數category_id 分類id
CREATE PROCEDURE find_category_id(IN category_id INT)
BEGIN
DROP TABLE IF EXISTS tmp_id;
#建立臨時表 用來存儲所有的id 臨時表在連結結束會自動删除
CREATE TEMPORARY TABLE  tmp_id(id INT);
#清空該表
TRUNCATE TABLE tmp_id;
#臨時表 用來存儲所有的商品資訊;
#根據分類查詢商品的時候，商品展示清單隻需要這幾個字段就可以了，詳情的時候才是所有字段 
DROP TABLE IF EXISTS tmp_product;
CREATE TEMPORARY TABLE  tmp_product(SELECT id,NAME,subtitle,main_image,price FROM xmcc_product WHERE 1=2);
TRUNCATE TABLE tmp_id;
#調用另外的存儲過程
CALL recursive_find_id(category_id);
  BEGIN
  #設定循環條件
   DECLARE done INT DEFAULT '0';
   #設定cid來接收遊标中彈出的id
   DECLARE cid INT DEFAULT '0';
   #遞歸過程将所有的id放在臨時表中 現在查出放在遊标ids中
   DECLARE ids CURSOR FOR SELECT id FROM tmp_id;
   #當遊标周遊結束 done=1
   DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=1;
   #打開遊标
   OPEN ids;
   #彈出遊标一個值賦給cid
   FETCH ids INTO cid;
   WHILE (done=0) DO
   #根據分類查找到商品 ，插入資料到商品臨時表中
   INSERT INTO tmp_product  SELECT id,NAME,subtitle,main_image,price FROM xmcc_product xp WHERE xp.category_id=cid and xp.status=1;
   FETCH ids INTO cid;
   END WHILE;
  END;
  #傳回查詢到的商品結果集
SELECT * FROM tmp_product;
END ;//


DELIMITER //
CREATE PROCEDURE recursive_find_id(IN category_id INT)
BEGIN 
#定義id接收每次查詢到的分類id
DECLARE cid INT;
#定義done為0TRUE繼續循環
DECLARE done INT DEFAULT TRUE;
#定義ids遊标來獲得parent_id為傳入的id的集合
DECLARE ids CURSOR FOR SELECT id FROM xmcc_category WHERE parent_id=category_id;
#遊标循環結束 出現not found  done=1循環結束
DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=FALSE;
#mysql 遞歸的時候 需要設定深度
SET @@max_sp_recursion_depth = 10;
#将id插入臨時表中
INSERT INTO tmp_id VALUES(category_id);
OPEN ids;#打開遊标
#将查詢到的遊标結果 彈出指派給id 每次彈出一個
FETCH ids INTO cid;
WHILE done DO
#遞歸調用目前存儲過程
CALL recursive_find_id(cid);
FETCH ids INTO cid;
END WHILE;
CLOSE ids;
END;//

MySQL——存儲過程詳解及執行個體分析

一、儲存過程簡介

1、什麼是存儲過程

2、存儲過程優缺點

3、存儲過程入門程式

4、在idea中如何調用儲存過程？

二、存儲過程程式設計

1、存儲過程的變量

2、存儲過程中的參數

3、選擇結構if

4、分支結構case

5、3個循環結構

6、存儲過程的異常處理

7、MySQL遊标

三、存儲過程執行個體講解

1、案例1：取消訂單

2、案例2：根據商品分類擷取商品詳情

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案