-- 创建视图
CREATE VIEW v_employee_dept AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.id;

-- 使用视图（和普通表一样查询）
SELECT * FROM v_employee_dept WHERE salary > 10000;

-- 查看视图定义
SHOW CREATE VIEW v_employee_dept;

-- 删除视图
DROP VIEW v_employee_dept;

-- 1. 带聚合的视图
CREATE VIEW v_dept_salary_summary AS
SELECT d.dept_name,
       COUNT(e.id) AS emp_count,
       ROUND(AVG(e.salary), 2) AS avg_salary,
       MAX(e.salary) AS max_salary
FROM department d
LEFT JOIN employee e ON d.id = e.dept_id
GROUP BY d.id, d.dept_name;

-- 2. 视图上再建视图（不推荐多层嵌套）
CREATE VIEW v_high_salary_dept AS
SELECT * FROM v_dept_salary_summary
WHERE avg_salary > 12000;

-- 3. 可更新视图（满足条件的视图可以更新底层数据）
CREATE VIEW v_employee_basic AS
SELECT id, name, dept_id, salary
FROM employee
WHERE dept_id IS NOT NULL;

-- 可通过视图更新数据
UPDATE v_employee_basic SET salary = 16000 WHERE id = 1;
-- 实际更新的是 employee 表

-- 带 CHECK OPTION 的视图（防止更新后脱离视图范围）
CREATE VIEW v_tech_employee AS
SELECT id, name, dept_id, salary
FROM employee
WHERE dept_id = 1
WITH CHECK OPTION;

-- 下面这条会报错，因为更新后 dept_id=2 不在视图范围内
UPDATE v_tech_employee SET dept_id = 2 WHERE id = 1;

-- 场景：对外部系统只暴露用户的基本信息，隐藏敏感字段
CREATE VIEW v_user_public AS
SELECT id, username, nickname, avatar_url
FROM user
WHERE is_deleted = 0;

-- 内部系统视图（增加更多字段但隐藏密码）
CREATE VIEW v_user_internal AS
SELECT id, username, nickname, email, phone, status, create_time
FROM user
WHERE is_deleted = 0;

-- 修改分隔符（因为过程体内有 ;）
DELIMITER $$

-- 创建无参存储过程
CREATE PROCEDURE sp_get_all_employees()
BEGIN
    SELECT * FROM employee;
END$$

-- 改回分隔符
DELIMITER ;

-- 调用存储过程
CALL sp_get_all_employees();

-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_get_all_employees;

-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_get_all_employees;

DELIMITER $$

-- IN 参数（输入）
CREATE PROCEDURE sp_get_employee_by_dept(
    IN dept_id_param INT
)
BEGIN
    SELECT e.name, e.salary, d.dept_name
    FROM employee e
    JOIN department d ON e.dept_id = d.id
    WHERE e.dept_id = dept_id_param;
END$$

-- OUT 参数（输出）
CREATE PROCEDURE sp_get_dept_stats(
    IN dept_id_param INT,
    OUT emp_count INT,
    OUT avg_salary DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), AVG(salary)
    INTO emp_count, avg_salary
    FROM employee
    WHERE dept_id = dept_id_param;
END$$

-- INOUT 参数
CREATE PROCEDURE sp_increase_salary(
    INOUT salary_param DECIMAL(10,2),
    IN percentage DECIMAL(5,2)
)
BEGIN
    SET salary_param = salary_param * (1 + percentage / 100);
END$$

DELIMITER ;

-- 调用 OUT 参数的过程
CALL sp_get_dept_stats(1, @count, @avg);
SELECT @count AS emp_count, @avg AS avg_salary;

-- 调用 INOUT 参数的过程
SET @sal = 10000;
CALL sp_increase_salary(@sal, 10);
SELECT @sal;  -- 11000

DELIMITER $$

CREATE PROCEDURE sp_batch_insert_employee(
    IN total_count INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE dept_id_val INT;
    DECLARE salary_val DECIMAL(10,2);

    -- 开启事务
    START TRANSACTION;

    WHILE i <= total_count DO
        -- 随机分配部门（1~3）
        SET dept_id_val = FLOOR(1 + RAND() * 3);
        SET salary_val = ROUND(5000 + RAND() * 20000, 2);

        INSERT INTO employee(name, dept_id, salary)
        VALUES (CONCAT('测试用户', i), dept_id_val, salary_val);

        SET i = i + 1;

        -- 每1000条提交一次
        IF i % 1000 = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;

    COMMIT;
END$$

DELIMITER ;

-- 插入 10000 条测试数据
CALL sp_batch_insert_employee(10000);

DELIMITER $$

-- 标量函数：返回单个值
CREATE FUNCTION fn_get_dept_name(dept_id INT)
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE dept_name VARCHAR(50);

    SELECT d.dept_name INTO dept_name
    FROM department d
    WHERE d.id = dept_id;

    RETURN dept_name;
END$$

DELIMITER ;

-- 使用函数
SELECT id, name, fn_get_dept_name(dept_id) AS dept_name FROM employee;

DELIMITER $$

-- 1. 计算年龄
CREATE FUNCTION fn_calc_age(birth_date DATE)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END$$

-- 2. 脱敏手机号（138****1234）
CREATE FUNCTION fn_mask_phone(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    RETURN CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4));
END$$

-- 3. 计算订单折扣金额
CREATE FUNCTION fn_calc_discount(
    total_amount DECIMAL(12,2),
    discount_rate DECIMAL(5,2)
)
RETURNS DECIMAL(12,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE result DECIMAL(12,2);
    SET result = ROUND(total_amount * discount_rate, 2);
    RETURN result;
END$$

DELIMITER ;

-- 使用示例
SELECT
    fn_mask_phone('13812345678') AS masked_phone;  -- 138****5678

SELECT
    fn_calc_discount(1000, 0.85) AS discounted_price;  -- 850.00

DELIMITER $$

CREATE PROCEDURE sp_order_report(
    IN start_date DATE,
    IN end_date DATE,
    IN min_amount DECIMAL(12,2),
    OUT total_orders INT,
    OUT total_revenue DECIMAL(12,2)
)
BEGIN
    -- 临时表存储中间结果
    CREATE TEMPORARY TABLE tmp_report AS
    SELECT
        DATE(o.order_date) AS order_day,
        o.status,
        COUNT(*) AS order_count,
        SUM(o.amount) AS daily_revenue
    FROM orders o
    WHERE DATE(o.order_date) BETWEEN start_date AND end_date
      AND o.amount >= min_amount
    GROUP BY DATE(o.order_date), o.status
    ORDER BY order_day;

    -- 统计总数
    SELECT COUNT(*), SUM(daily_revenue)
    INTO total_orders, total_revenue
    FROM tmp_report;

    -- 返回明细结果集
    SELECT * FROM tmp_report;

    -- 清理临时表
    DROP TEMPORARY TABLE tmp_report;
END$$

DELIMITER ;

-- 调用报表
CALL sp_order_report('2024-01-01', '2024-03-31', 100, @total, @revenue);
SELECT @total AS total_orders, @revenue AS total_revenue;