基于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 ;
存储过程的学习就到这里！！
京公网安备 11010502036488号