开发中会将改动较大的内容维护到数据字典中便于后期修改,但在SQL增删改操作时就会遇到如何将字典中对应的值取出来。这时候就需要用到函数来操作。
一、在mysql库中新建一个函数,名称:f_get_dict_name
CREATE DEFINER=`root`@`%` FUNCTION `f_get_dict_name`(in_type VARCHAR(40), in_code VARCHAR(40)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE out_name VARCHAR(255) DEFAULT '';
SELECT `value` INTO out_name FROM sys_dict WHERE type=in_type AND `code`=in_code limit 1;
IF out_name='' OR out_name IS NULL THEN
SET out_name = in_code;
END IF;
RETURN out_name;
END
二、在SQL语句中的使用
SELECT
a.id,
a.waybill_Number,
a.start_goods_address,
a.end_goods_address,
b.license_Plate_Number,
a.error_status,
f_get_dict_name ( 'abnormal', a.error_status ) AS error_show
FROM
t_waybillorder a
LEFT JOIN t_vehiclemanagement b ON a.car_id = b.id
f_get_dict_name (字典表中名字, 要查询的名字) AS error_show ,返回字典表中对应的value值