【数据库测试】存储过程的几个实例

存储过程是一组为了完成特定功能的SQL语句集合，它可以被存储在数据库中，并在需要时调用执行。以下是几个典型的存储过程实例：

1. 员工工资计算存储过程

DELIMITER //

CREATE PROCEDURE CalculateSalary(IN emp_id INT)
BEGIN
    DECLARE base_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);

    SELECT salary INTO base_salary
    FROM employees
    WHERE id = emp_id;

    SET bonus = base_salary * 0.1; -- 假设奖金是基本工资的10%

    SELECT base_salary + bonus AS total_salary;
END //

DELIMITER ;

这个存储过程接收一个员工ID作为输入参数，计算员工的基本工资和奖金，并返回总工资。

2. 客户订单汇总存储过程

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
    SELECT o.order_id, o.order_date, SUM(p.quantity * p.price) AS total_amount
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    WHERE o.customer_id = customer_id
    GROUP BY o.order_id;
END //

DELIMITER ;

该存储过程用于获取特定客户的所有订单及其总金额。它通过连接orders、order_details和products表来计算每个订单的总金额。

3. 用户注册存储过程

DELIMITER //

CREATE PROCEDURE RegisterUser(IN username VARCHAR(50), IN password VARCHAR(50), IN email VARCHAR(100))
BEGIN
    DECLARE user_id INT;

    INSERT INTO users (username, password, email) VALUES (username, PASSWORD(password), email);
    SET user_id = LAST_INSERT_ID();

    SELECT user_id AS new_user_id;
END //

DELIMITER ;

这个存储过程用于新用户的注册。它将用户名、密码（经过加密）和电子邮件地址插入到users表中，并返回新生成的用户ID。

4. 库存更新存储过程

DELIMITER //

CREATE PROCEDURE UpdateInventory(IN product_id INT, IN quantity INT)
BEGIN
    DECLARE current_stock INT;

    SELECT stock INTO current_stock
    FROM inventory
    WHERE product_id = product_id;

    UPDATE inventory
    SET stock = stock + quantity
    WHERE product_id = product_id
    AND stock >= 0;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found or stock is negative';
    END IF;
END //

DELIMITER ;

此存储过程用于更新产品库存。它首先检查当前库存量，然后增加指定的数量。如果产品不存在或增加后库存量为负，则抛出一个错误。

5. 月度销售报告存储过程

DELIMITER //

CREATE PROCEDURE MonthlySalesReport(IN month INT, IN year INT)
BEGIN
    SELECT product_id, SUM(quantity) AS total_sold, SUM(quantity * price) AS total_revenue
    FROM sales
    WHERE YEAR(date) = year AND MONTH(date) = month
    GROUP BY product_id
    ORDER BY total_revenue DESC;
END //

DELIMITER ;

该存储过程生成指定月份的销售报告，显示每种产品的总销售量和总收入，并按收入降序排列。

请注意，上述存储过程的实现可能需要根据实际使用的数据库系统（如MySQL、PostgreSQL、Oracle等）进行调整，因为不同的数据库系统在语法和功能上可能存在差异。此外，在实际应用中，还需要考虑错误处理、事务管理和安全性等因素。