sql必知必会学习记录-19课使用存储过程（mysql）

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE mailinglistcount(OUT ListCount INTEGER)
BEGIN 
    SELECT count(*) INTO ListCount
    FROM customers
    WHERE NOT cust_email IS NULL;
END;
DELIMITER;
-- 调用存储过程
SET @ListCount = 0;
CALL mailinglistcount(@ListCount);
SELECT @ListCount;

-- 例2 在Orders表中插入一个新订单
DELIMITER //
DROP PROCEDURE if exists neworder;
CREATE PROCEDURE neworder(IN id CHAR(10))
BEGIN
   -- 为订单号声明一个变量
    DECLARE order_number INTEGER;
    -- 获取当前最大订单号
    SELECT max(order_num) INTO order_number
    FROM orders;
    -- 决定下一个订单号
    SET order_number = order_number+1; 
    -- 插入新订单
    INSERT INTO orders(order_num, order_date, cust_id)
    VALUES(order_number, CURDATE(), id);
END //
-- 调用这个存储过程
SET FOREIGN_KEY_CHECKS = 0;     # 临时取消外键约束
CALL neworder("1000000112");
SET FOREIGN_KEY_CHECKS = 1;     # 开启外键约束