基于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示例数据库的存储过程练习