创建带输入参数的存储过程 P_cust_credit_level,根据输入参数更新客户信用等级表,并将更新前的记录插入到客户信用等级历史表。mysql
时间: 2024-09-09 18:04:18 浏览: 51
创建一个名为`P_cust_credit_level`的存储过程,用于根据输入参数更新MySQL数据库中的客户信用等级并保存历史记录,可以按照以下步骤编写:
```sql
DELIMITER //
CREATE PROCEDURE P_cust_credit_level(
IN customer_id INT,
IN new_credit_level VARCHAR(50),
OUT prev_level VARCHAR(50) // 输出参数,用于返回更新前的信用等级
)
BEGIN
-- 首先,查询更新前的信用等级
SET @prev_level = (SELECT credit_level FROM customer_credits WHERE id = customer_id);
-- 如果找到了当前客户的信用等级,开始更新
IF @prev_level IS NOT NULL THEN
UPDATE customer_credits
SET credit_level = new_credit_level
WHERE id = customer_id;
-- 插入新的信用等级记录到历史表
INSERT INTO customer_credit_history (
customer_id, credit_level, update_date
) VALUES (
customer_id, new_credit_level, NOW()
);
-- 返回更新后的信用等级
SELECT信用_level INTO prev_level FROM customer_credits WHERE id = customer_id;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer with the given ID not found.';
END IF;
END //
DELIMITER ;
```
在这个存储过程中,你需要两个表:`customer_credits`(包含客户ID和信用等级字段)以及`customer_credit_history`(用于存储历史记录,通常包括客户ID、信用等级和更新日期)。执行此存储过程时,传入`customer_id`和`new_credit_level`作为参数,存储过程会自动处理信用等级的更新和历史记录的添加。