-- 基本语法
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 用户订单视图
CREATE VIEW user_orders_view AS
SELECT u.id, u.username, o.order_no, o.total_amount
FROM users u INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 复杂统计视图
CREATE VIEW monthly_sales_view AS
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       COUNT(*) AS order_count, SUM(total_amount) AS total_sales
FROM orders GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- 查看视图
SHOW CREATE VIEW user_orders_view;

-- 更新视图
CREATE OR REPLACE VIEW user_orders_view AS
SELECT u.id, u.username, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;

-- 删除视图
DROP VIEW IF EXISTS user_orders_view;

-- 可更新视图（简单视图可 INSERT/UPDATE/DELETE）
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 1;

INSERT INTO active_users (username, email) VALUES ('test', 'test@example.com');
UPDATE active_users SET email = 'new@example.com' WHERE username = 'test';
DELETE FROM active_users WHERE username = 'test';

DELIMITER //
CREATE PROCEDURE procedure_name(IN param_name param_type)
BEGIN
    -- SQL 语句
END //
DELIMITER ;

-- 调用
CALL procedure_name(value);

DELIMITER //

-- IN 输入参数
CREATE PROCEDURE insert_user(IN p_username VARCHAR(50), IN p_email VARCHAR(100))
BEGIN
    INSERT INTO users (username, email) VALUES (p_username, p_email);
    SELECT LAST_INSERT_ID();
END //

-- OUT 输出参数
CREATE PROCEDURE get_count(OUT cnt INT)
BEGIN
    SELECT COUNT(*) INTO cnt FROM users;
END //

-- 调用 OUT 参数
CALL get_count(@cnt);
SELECT @cnt;

-- INOUT 参数
CREATE PROCEDURE increment(INOUT num INT)
BEGIN
    SET num = num + 1;
END //

SET @x = 10;
CALL increment(@x);
SELECT @x;  -- 11

DELIMITER ;

DELIMITER //

CREATE PROCEDURE grade_demo(IN score INT, OUT grade CHAR(1))
BEGIN
    DECLARE max_price DECIMAL(10,2) DEFAULT 0;
    
    IF score >= 90 THEN SET grade = 'A';
    ELSEIF score >= 80 THEN SET grade = 'B';
    ELSEIF score >= 70 THEN SET grade = 'C';
    ELSE SET grade = 'D';
    END IF;
END //

-- CASE 语句
CREATE PROCEDURE status_name(IN status INT, OUT name VARCHAR(20))
BEGIN
    CASE status
        WHEN 0 THEN SET name = '禁用';
        WHEN 1 THEN SET name = '启用';
        ELSE SET name = '未知';
    END CASE;
END //

DELIMITER ;

DELIMITER //

-- WHILE 循环
CREATE PROCEDURE insert_n_records(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        INSERT INTO test_table (name) VALUES (CONCAT('record_', i));
        SET i = i + 1;
    END WHILE;
END //

-- REPEAT 循环
CREATE PROCEDURE repeat_demo(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        INSERT INTO test_table (name) VALUES (CONCAT('record_', i));
        SET i = i + 1;
    UNTIL i > n END REPEAT;
END //

-- LOOP + LEAVE
CREATE PROCEDURE loop_demo(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: LOOP
        IF i > n THEN LEAVE loop_label; END IF;
        INSERT INTO test_table (name) VALUES (CONCAT('record_', i));
        SET i = i + 1;
    END LOOP loop_label;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_amount DECIMAL(10,2);
    
    DECLARE cur CURSOR FOR SELECT id, total_amount FROM orders WHERE status = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_amount;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE orders SET status = 1 WHERE id = v_id;
    END LOOP;
    CLOSE cur;
END //

DELIMITER ;

SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE procedure_name;
DROP PROCEDURE IF EXISTS procedure_name;

DELIMITER //

-- 返回状态名称
CREATE FUNCTION get_status(status INT)
RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
    RETURN CASE status WHEN 0 THEN '禁用' WHEN 1 THEN '启用' ELSE '未知' END;
END //

-- 计算含税总额
CREATE FUNCTION calc_total(subtotal DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    RETURN subtotal * 1.06;
END //

-- 统计订单数
CREATE FUNCTION order_count(user_id INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = user_id;
    RETURN cnt;
END //

DELIMITER ;

-- 调用函数
SELECT username, get_status(status) FROM users;
SELECT calc_total(100.00);  -- 106.00

SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION function_name;
DROP FUNCTION IF EXISTS function_name;

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- 每天清理过期数据
CREATE EVENT cleanup_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //

-- 禁用事件
ALTER EVENT cleanup_event DISABLE;

-- 查看事件
SHOW EVENTS;
DROP EVENT IF EXISTS cleanup_event;

✅ 视图：简化查询，提供数据访问控制
✅ 存储过程：预编译SQL，支持复杂逻辑
✅ 函数：可返回值的SQL表达式
✅ 事件调度器：定时执行任务