基于Mysql⽰例数据库的存储过程练习基于Mysql⽰例数据库的存储过程练习
写在前⾯
此处是数据库的脚本（建表语句，包括数据），执⾏⽅法这⾥就不介绍了！
⼀、第⼀个⼊门⽰例，创建存储过程，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 参数⽰例
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
第四节多个返回参数的时候
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 声明
-- 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 >50000THEN
SET p_customerLevel ='PLATINUM';
ELSEIF(creditlim <=50000AND creditlim >=10000)THEN
SET p_customerLevel ='GOLD';
ELSEIF creditlim <10000THEN
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
--   
--  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
mysql存储过程使用--    ...
-
-    ELSE commands
--  END CASE;
CASE⽰例　⼆

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。