存储过程

CREATE PROCEDURE 基础

使用 IN、OUT 和 INOUT 参数定义可重用的 SQL 例程。

DELIMITER $$

CREATE PROCEDURE get_user_orders(IN p_user_id INT, OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM orders
  WHERE user_id = p_user_id;
END$$

DELIMITER ;

-- Call the procedure
CALL get_user_orders(42, @cnt);
SELECT @cnt;

-- Procedure with multiple statements
DELIMITER $$
CREATE PROCEDURE transfer_funds(
  IN from_id INT,
  IN to_id   INT,
  IN amount  DECIMAL(10,2)
)
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END$$
DELIMITER ;

变量与流程控制

DELIMITER $$
CREATE PROCEDURE classify_order(IN p_total DECIMAL(10,2))
BEGIN
  DECLARE v_class VARCHAR(20);

  IF p_total >= 500 THEN
    SET v_class = 'Large';
  ELSEIF p_total >= 100 THEN
    SET v_class = 'Medium';
  ELSE
    SET v_class = 'Small';
  END IF;

  SELECT v_class AS order_class;
END$$
DELIMITER ;

-- CASE statement
DELIMITER $$
CREATE PROCEDURE day_name_proc(IN p_day INT)
BEGIN
  DECLARE v_name VARCHAR(10);
  CASE p_day
    WHEN 1 THEN SET v_name = 'Monday';
    WHEN 2 THEN SET v_name = 'Tuesday';
    ELSE       SET v_name = 'Other';
  END CASE;
  SELECT v_name;
END$$
DELIMITER ;

-- WHILE loop
DELIMITER $$
CREATE PROCEDURE count_up(IN p_limit INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= p_limit DO
    INSERT INTO log_tbl (val) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

游标

游标用于在存储过程内逐行遍历结果集。

DELIMITER $$
CREATE PROCEDURE process_pending_orders()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_order_id INT;
  DECLARE v_user_id  INT;

  DECLARE cur CURSOR FOR
    SELECT id, user_id FROM orders WHERE status = 'pending';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_order_id, v_user_id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- process each row
    UPDATE orders SET status = 'processing' WHERE id = v_order_id;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

错误处理

DELIMITER $$
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;  -- re-raise the error to the caller
  END;

  DECLARE EXIT HANDLER FOR 1062  -- duplicate entry
  BEGIN
    SELECT 'Email already exists' AS error_msg;
  END;

  START TRANSACTION;
  INSERT INTO users (email) VALUES (p_email);
  COMMIT;
END$$
DELIMITER ;

-- SIGNAL: raise a custom error
DELIMITER $$
CREATE PROCEDURE validate_age(IN p_age INT)
BEGIN
  IF p_age < 0 OR p_age > 150 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Age must be between 0 and 150';
  END IF;
END$$
DELIMITER ;

触发器

触发器在 INSERT、UPDATE 或 DELETE 事件时自动触发。

-- BEFORE INSERT: set defaults
DELIMITER $$
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.slug = LOWER(REPLACE(NEW.name, ' ', '-'));
END$$
DELIMITER ;

-- AFTER UPDATE: audit log
DELIMITER $$
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary != NEW.salary THEN
    INSERT INTO salary_audit (emp_id, old_sal, new_sal, changed_at)
    VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
  END IF;
END$$
DELIMITER ;

-- Show and drop triggers
SHOW TRIGGERS LIKE 'users'\G
DROP TRIGGER IF EXISTS trg_users_before_insert;