一罐可乐

1
-- 存储过程实例：计算商品单价中位数、方差与标准差
2
DROP PROCEDURE IF EXISTS sp_price_stats;
3
DELIMITER $$
4
CREATE PROCEDURE sp_price_stats()
5
BEGIN
6
  DECLARE $n INT;
7
  DECLARE $m, $s, $x DOUBLE;
8

9
  -- 获取总数、平方和与平均值
10
  SELECT COUNT(*), SUM(Unitprice * Unitprice), AVG(Unitprice)
11
    INTO $n, $s, $x
12
  FROM Products;
13

14
  -- 计算中位数（区分奇偶数）
15
  IF (MOD($n, 2) = 0) THEN
16
    SELECT SUM(Unitprice) / 2 INTO $m
17
    FROM (
18
      SELECT Unitprice, ROW_NUMBER() OVER (ORDER BY Unitprice) as rowno
19
      FROM Products
20
    ) as p
21
    WHERE rowno = $n / 2 OR rowno = $n / 2 + 1;
22
  ELSE
23
    SELECT Unitprice INTO $m
24
    FROM (
25
      SELECT Unitprice, ROW_NUMBER() OVER (ORDER BY Unitprice) as rowno
26
      FROM Products
27
    ) as p
28
    WHERE rowno = ($n + 1) / 2;
29
  END IF;
30

31
  -- 输出统计值
32
  SELECT
33
    $m as '中位数',
34
    CAST(($s - $n * $x * $x) / $n as DECIMAL(16,8)) as '方差',
35
    CONVERT(SQRT(($s - $n * $x * $x) / $n), DECIMAL(16,8)) as '标准差';
36
END $$
37
DELIMITER ;

1
-- 触发器实例：当折扣率超出客户类型的限制上限时，使用 SIGNAL 强制抛出异常回滚操作
2
DROP TRIGGER IF EXISTS tr_check_discount;
3
DELIMITER $$
4
CREATE TRIGGER tr_check_discount BEFORE INSERT ON myOrderitems
5
FOR EACH ROW
6
BEGIN
7
  DECLARE $limit DECIMAL(6,2);
8

9
  -- 根据订单号查询对应客户类别的折扣上限
10
  SELECT Discount_limit INTO $limit
11
  FROM Orders
12
  JOIN Customers USING (CustomerID)
13
  JOIN CustomerTypes USING (TypeID)
14
  WHERE OrderID = new.OrderID;
15

16
  -- 校验折扣是否越界
17
  IF (new.Discount IS NOT NULL) AND (new.Discount < 0 OR new.Discount > $limit) THEN
18
    SIGNAL SQLSTATE '45000'
19
    SET MESSAGE_TEXT = '插入失败：销售折扣率超出该客户类别的上限约束！';
20
  END IF;
21
END $$
22
DELIMITER ;

1
-- 游标循环标准模板（使用 WHILE 循环）
2
DROP PROCEDURE IF EXISTS sp_cursor_demo;
3
DELIMITER $$
4
CREATE PROCEDURE sp_cursor_demo()
5
BEGIN
6
  DECLARE $done INT DEFAULT 0;
7
  DECLARE $pid INT;
8
  DECLARE $price DECIMAL(10,2);
9

10
  -- 1. 声明游标
11
  DECLARE cur_prod CURSOR FOR SELECT ProductID, Unitprice FROM Products;
12
  -- 2. 声明 NOT FOUND 异常句柄，游标读完后自动将 $done 置为 1
13
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done = 1;
14

15
  OPEN cur_prod;
16

17
  -- 3. 首次提取数据
18
  FETCH cur_prod INTO $pid, $price;
19

20
  -- 4. 循环遍历
21
  WHILE $done = 0 DO
22
    -- 执行业务逻辑处理
23
    -- ...
24

25
    -- 下移游标指针
26
    FETCH cur_prod INTO $pid, $price;
27
  END WHILE;
28

29
  CLOSE cur_prod;
30
END $$
31
DELIMITER ;