一个添加语句的存储过程,需要判断参数不能为空,userName的名称不允许为张王李赵开头。
DELIMITER //
CREATE PROCEDURE AddBillionaire(
IN p_birthday DATE,
IN p_userName VARCHAR(100),
IN p_countMoney DECIMAL(15, 2),
IN p_age INT,
IN p_sex VARCHAR(10),
IN p_address VARCHAR(200),
IN p_introduce TEXT
)
BEGIN
-- 判断参数不能为空
IF p_birthday IS NULL OR p_userName IS NULL OR p_countMoney IS NULL OR p_age IS NULL OR p_sex IS NULL OR p_address IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '参数不能为空';
END IF;
-- 判断userName的名称不允许为张王李赵开头
IF LEFT(p_userName, 1) = '张' OR LEFT(p_userName, 1) = '王' OR LEFT(p_userName, 1) = '李' OR LEFT(p_userName, 1) = '赵' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName的名称不允许为张王李赵开头';
END IF;
-- 参数检查通过后,执行插入操作
INSERT INTO 亿万富翁表 (birthday, userName, countMoney, age, sex, address, introduce)
VALUES (p_birthday, p_userName, p_countMoney, p_age, p_sex, p_address, p_introduce);
END //
DELIMITER ;
CALL AddBillionaire('1985-05-15', '刘晓明', 1234567890, 38, '男', '北京市朝阳区', '互联网科技新锐');
通过case,when语句来查询数据,数值为男时显示【雄性】,数值为女时则显示【女孩】
SELECT
id,
birthday,
userName,
countMoney,
age,
sex,
address,
introduce,
CASE
WHEN sex = '男' THEN '【雄性】'
WHEN sex = '女' THEN '【女孩】'
ELSE '未知' -- 可选,用于处理除'男'和'女'之外的其他情况
END AS sex_description
FROM
亿万富翁表;