基于Mysql示例数据库的存储过程练习

喜欢ヅ旅行 2021-12-19 23:13 197阅读 0赞

### 基于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 ;

– SQL 测试

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

存储过程的学习就到这里！！

[classicmodels.sql]: https://download.csdn.net/download/qq_42105629/11266038