基于Mysql示例資料庫的存儲過程練習

寫在前面
一、第一個入門示例，建立存儲過程，getAllProducts
二、第二節，存儲過程變量
三、存儲過程參數，IN，OUT，INOUT
第四節多個傳回參數的時候
第五節 MYSQL 的 if 聲明
第六節 MySQL CASE語句
第七節如何在 IF 和 CASE 語句之間選擇
第八節 MySQL 中循環
8.1 WHILE循環
8.2 REPEAT循環
第九節 MySQL Cursor(光标)(隻需了解階段，不做重點)
第十節如何查出MySQL資料庫中的存儲過程
第十一節 MYSQL中存儲過程中的錯誤處理(了解即可，不做重點)
第十二節如何使用SIGNAL和RESIGNAL語句來提高存儲過程中的錯誤條件(了解即可，不做重點)
第十三節 MySQL存儲函數(一般内置的函數已經可以滿足業務需求，這裡可自定義函數，并在SQL中調用)

寫在前面

此處是資料庫的腳本（建表語句，包括資料），執行方法這裡就不介紹了！classicmodels.sql

一、第一個入門示例，建立存儲過程，getAllProducts

USE classicmodels;-- 選擇資料庫
SHOW TABLES;
delimiter //
CREATE PROCEDURE getAllProducts ()
BEGIN
  SELECT * FROM products ;
END //
delimiter ;
-- 調用存儲過程 getAllProducts
CALL getAllproducts();
 -- 這裡會正确傳回findAll

二、第二節，存儲過程變量

-- 聲明變量（在存儲過程内部）
-- DECLARE 變量名 變量類型 (size) DEFAULT default_value;

– 聲明如下

– DECLARE a INT DEFAULT 1;

– DECLARE x,y INT DEFAULT 0;

– 可同時聲明多個變量

– DECLARE total_count INT;

– SET total_count = 10;

三、存儲過程參數，IN，OUT，INOUT

– IN/OUT/INOUT 名字類型,IN/OUT/INOUT 名字類型,IN/OUT/INOUT 名字類型

– 以逗号隔開，可傳多個

– 執行個體

delimiter //
CREATE PROCEDURE getOfficeByCountry (IN countryName VARCHAR(255))
BEGIN
  SELECT
    *
  FROM
    offices
  WHERE
    country = countryName ;
END //
delimiter ;
-- 調用存儲過程
call getOfficeByCountry('usa');
call getOfficeByCountry('france');

– OUT 參數示例

delimiter //
CREATE PROCEDURE CountOrderByStatus (
  IN orderStatus VARCHAR (255),
  OUT total INT
)
BEGIN
  SELECT
    count(orderStatus) INTO total
  FROM
    orders
  WHERE
    STATUS = orderStatus ;
END //
delimiter ;
-- 調用存儲過程
call CountOrderByStatus('Shipped',@total);
SELECT @total;

call CountOrderByStatus('Shipped',@aa);
SELECT @aa;

– INOUT 參數示例

delimiter //
CREATE PROCEDURE set_counter (
  INOUT count INT (4),
  IN inc INT (4)
)
BEGIN
  SET count = count + inc ;
END //
delimiter ;

-- 這個怎麼運作，調用
SET @counter = 1;
CALL set_counter (@counter, 1);
SELECT @counter ;-- 傳回 2

call set_counter(@counter,6);
SELECT @counter ;-- 傳回 8

第四節多個傳回參數的時候

delimiter //
CREATE PROCEDURE get_order_by_cust(
 IN cust_no INT,
 OUT shipped INT,
 OUT canceled INT,
 OUT resolved INT,
 OUT disputed INT)
BEGIN
 -- shipped
 SELECT count(*) INTO shipped
        FROM orders WHERE
            customerNumber = cust_no
                AND status = 'Shipped';
 -- canceled
 SELECT
            count(*) INTO canceled
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Canceled';
 
 -- resolved
 SELECT
            count(*) INTO resolved
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Resolved';
 
 -- disputed
 SELECT
            count(*) INTO disputed
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Disputed';
 
END //
DELIMITER ;

-- 調用存儲過程,輸出時按存儲過程的輸出順序，輸出對應字段
CALL get_order_by_cust (141 ,@a ,@b ,@c ,@d);
SELECT @a total_shiped ,@b total_canceled ,@c ,@d total_disputed ;

第五節 MYSQL 的 if 聲明

-- 句式 1
  -- IF expression THEN
  --  statements;
  -- END
  -- IF;

-- 句式 2
-- IF expression THEN
--  statements;
-- ELSE
--  - statements;
-- END IF;

-- 句式 3
-- IF expression THEN
--  statements;
-- ELSEIF
-- ELSEIF - expression THEN
-- ELSEIF - statements;
-- ..
-- ELSE
-- ELSE
--  - statements;
-- END IF;

– MySQL IF語句示例

delimiter //
CREATE PROCEDURE GetCustomerLevel (
  IN p_customerNumber INT (11),
  OUT p_customerLevel VARCHAR (10)
)
BEGIN
  DECLARE creditlim DOUBLE ; 
   SELECT creditlimit INTO creditlim
    FROM customers WHERE customerNumber = p_customerNumber;
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM' ;
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD' ;
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER' ;
    END IF ;
END //-- 老是會報錯，即使複制過來也是，因為 末尾的 // 

delimiter ;

CALL GetCustomerLevel (144 ,@customerLevel) ;
SELECT @customerLevel ;

第六節 MySQL CASE語句

-- 聲明定義
  --  CASE case_expression
  --  WHEN when_expression_1 THEN
  --      commands
  --  WHEN when_expression_2 THEN
  --      commands...
  --  ELSE
  --      commands
  --  END CASE;

– 簡單 CASE 示例一

DELIMITER //
CREATE PROCEDURE GetCustomerShipping (
   IN p_customerNumber INT (11),
   OUT p_shiping VARCHAR (50)
)
BEGIN
   DECLARE customerCountry VARCHAR (50) ; 
   SELECT country INTO customerCountry FROM customers
      WHERE customerNumber = p_customerNumber ; 
      CASE customerCountry
      WHEN 'USA' THEN
            SET p_shiping = '2-day Shipping' ;
      WHEN 'Canada' THEN
            SET p_shiping = '3-day Shipping' ;
      ELSE
            SET p_shiping = '5-day Shipping' ;
      END CASE ;
END //
delimiter ;

調用存儲過程

CALL GetCustomerShipping(144,@p_shiping);
SELECT @p_shiping;

– SQL測試腳本，可一鍵運作，輸出結果

SET @customerNo = 112;
 
SELECT country into @country
FROM customers
WHERE customernumber = @customerNo;
 
CALL GetCustomerShipping(@customerNo,@shipping);
 
SELECT @customerNo AS Customer,
       @country    AS Country,
       @shipping   AS Shipping; -- USA  2-day Shipping  112

-- 搜尋 CASE 的 statement 
--  CASE
--     WHEN condition_1 THEN commands
--     WHEN condition_2 THEN commands
--     ...
--     ELSE commands
--  END CASE;

CASE示例　二

DELIMITER //
CREATE PROCEDURE GetCustomerLevel_2 (
  IN p_customerNumber INT (11),
  OUT p_customerLevel VARCHAR (10)
)
BEGIN
  DECLARE
    creditlim DOUBLE ; SELECT
      creditlimit INTO creditlim
    FROM
      customers
    WHERE
      customerNumber = p_customerNumber ; CASE
    WHEN creditlim > 50000 THEN
    SET p_customerLevel = 'PLATINUM' ;
    WHEN (
      creditlim <= 50000
      AND creditlim >= 10000
    ) THEN
    SET p_customerLevel = 'GOLD' ;
    WHEN creditlim < 10000 THEN

    SET p_customerLevel = 'SILVER' ;
    END CASE ; 
END //
delimiter ;

– 測試腳本 2

CALL GetCustomerLevel_2(144,@p_customerLevel);
SELECT @p_customerLevel as customerLevel; -- PLATINUM

第七節如何在 IF 和 CASE 語句之間選擇

– 首先考慮以下幾點

– 一個簡單的CASE語句是不是更可讀IF語句，當你對一個範圍内唯一值的比較單一的表達。此外，簡單CASE語句比IF語句更有效。

– 當您基于多個值檢查複雜表達式時，該IF語句更容易了解。

– 如果選擇使用該CASE語句，則必須確定至少有一個CASE條件比對。否則，您需要定義錯誤處理程式以捕獲錯誤。回想一下，您不必使用該IF語句執行此操作。

– 在大多數組織中，總會有一些稱為開發指南的文檔，它為開發人員提供了程式設計風格的命名約定和指南。您應該參考本文檔并遵循開發實踐。

– 在某些情況下，之間的混合IF，并CASE讓您的存儲過程更具可讀性和效率。

第八節 MySQL 中循環

三種： WHILE，REPEAT 和 LOOP。

8.1 WHILE循環

– WHILE expression DO

– statements

– END WHILE

– 示例

DELIMITER //
-- DROP PROCEDURE IF EXISTS test_mysql_while_loop //
CREATE PROCEDURE test_mysql_while_loop ()
BEGIN
  DECLARE x INT ; 
  DECLARE str VARCHAR (255) ;
    SET x = 1 ;
    SET str = '' ;
    WHILE x <= 5 DO

    SET str = CONCAT(str, x, ',') ;
    SET x = x + 1 ;
    END WHILE ; 
    SELECT str ;
 END //
DELIMITER ;

調用函數

call test_mysql_while_loop();

8.2 REPEAT循環

– REPEAT

– statements;

– UNTIL expression – 注意UNTIL 表達式中沒有分号 “;”

– END REPEAT

示例 repeated

DELIMITER //
DROP PROCEDURE IF EXISTS mysql_test_repeat_loop //
    CREATE PROCEDURE mysql_test_repeat_loop ()
    BEGIN
      DECLARE x INT ;
      DECLARE str VARCHAR (255) ;
        SET x = 1 ;
        SET str = '' ;
        REPEAT
            SET str = CONCAT(str, x, ',') ;
            SET x = x + 1 ; 
        UNTIL x > 5
        END REPEAT; 
        SELECT str ; 
      END //
    DELIMITER ;

– 調用函數

CALL mysql_test_repeat_loop();

-- 8.3 LOOP循環
-- LOOP，LEAVE和ITERATE語句

– 示例

delimiter //
CREATE PROCEDURE test_mysql_loop ()
BEGIN
  DECLARE
    x INT ; DECLARE
      str VARCHAR (255) ;
    SET x = 1 ;
    SET str = '' ; loop_label :
    LOOP
        IF x > 10 THEN
            LEAVE loop_label ;
        END IF ;
            SET x = x + 1 ;
        IF (x MOD 2) THEN
            ITERATE loop_label ;
        ELSE
            SET str = CONCAT(str, x, ',') ;
    END IF ;
    END LOOP;
    SELECT str ;
END //
delimiter ;

-- 調用存儲過程
  call test_mysql_loop();  -- 2,4,6,8,10,

第九節 MySQL Cursor(光标)(隻需了解階段，不做重點)

– MySQL遊标是隻讀的，不可滾動且不敏感的。

– 隻讀：您無法通過遊标更新基礎表中的資料。

– 不可滾動：您隻能按SELECT語句确定的順序擷取行。您無法以相反的順序擷取行。

– 此外，您不能跳過行或跳轉到結果集中的特定行。

– 未定型：有兩種光标：未定型遊标和不敏感遊标。敏感光标指向實際資料，而不敏感光标使用資料的臨時副本。

– 敏感性遊标比不敏感遊标執行得更快，因為它不必建立臨時資料副本。但是，對來自其他連接配接的資料所做的

– 任何更改都将影響敏感光标正在使用的資料，是以，如果不更新敏感光标正在使用的資料，則更安全。MySQL遊标是敏感的。

第十節如何查出MySQL資料庫中的存儲過程

-- 10.1 展示特征 
  SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];  
  --  示例
    SHOW PROCEDURE STATUS;
    SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
    SHOW PROCEDURE STATUS WHERE name LIKE '%product%';
-- 10.2 顯示存儲過程的源代碼
  SHOW CREATE PROCEDURE stored_procedure_name;
  SHOW CREATE PROCEDURE getAllProducts;

第十一節 MYSQL中存儲過程中的錯誤處理(了解即可，不做重點)

-- 11.1 聲明處理程式
  DECLARE action HANDLER FOR condition_value statement;
  -- 如果條件的值與之比對  condition_value，MySQL将執行statement并繼續或退出目前代碼塊action。

  -- 所述action接受下列值之一：

  -- CONTINUE：繼續執行封閉代碼塊（BEGIN... END）。
  -- EXIT ：聲明處理程式的封閉代碼塊的執行終止。

-- 11.2 MySQL錯誤處理示例

第十二節如何使用SIGNAL和RESIGNAL語句來提高存儲過程中的錯誤條件(了解即可，不做重點)

第十三節 MySQL存儲函數(一般内置的函數已經可以滿足業務需求，這裡可自定義函數，并在SQL中調用)

-- 聲明 statement
  --  CREATE FUNCTION function_name(param1,param2,…)
  --     RETURNS datatype
  --    [NOT] DETERMINISTIC
  --  statements

示例

DELIMITER //
CREATE FUNCTION CustomerLevel (p_creditLimit DOUBLE) RETURNS VARCHAR (10) DETERMINISTIC
BEGIN
  DECLARE lvl VARCHAR (10) ;
  IF p_creditLimit > 50000 THEN
      SET lvl = 'PLATINUM' ;
  ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
      SET lvl = 'GOLD' ;
  ELSEIF p_creditLimit < 10000 THEN
      SET lvl = 'SILVER' ;
  END IF ;
  RETURN (lvl) ;
END //
delimiter ;

SELECT
      customerName,
      CustomerLevel (creditLimit)
   FROM
      customers
   ORDER BY
      customerName ;

基于Mysql示例資料庫的存儲過程練習