CREATE OR REPLACE FUNCTION fun_color_switch(color IN VARCHAR2)
/*
* 将十进制color(字符串型) 转换成 (R,G,B) 格式 ; 如果color是NULL,则返回NULL
* eg : 1234567 -> 18,214,135
*/
RETURN VARCHAR2 IS
RESULT VARCHAR2(255);
v_length NUMBER;
v_left_length NUMBER;
v_format_str VARCHAR2(15);
v_count NUMBER;
v_rec VARCHAR2(11);
v_index_1 VARCHAR2(5);
v_index_2 VARCHAR2(5);
v_index_3 VARCHAR2(5);
BEGIN
IF color IS NOT NULL THEN
v_length := length(color);
v_count := 1;
v_format_str := 'x';
LOOP
v_count := v_count + 1;
EXIT WHEN v_count > v_length;
v_format_str := v_format_str || 'x';
END LOOP;
v_rec := trim(to_char(to_number(color), v_format_str));
v_count := 1;
v_left_length := 6 - length(v_rec);
LOOP
v_count := v_count + 1;
EXIT WHEN v_count > v_left_length + 1;
v_rec := '0'||v_rec;
END LOOP;
v_index_1 := to_number(substr(v_rec,1,2),'xx');
v_index_2 := to_number(substr(v_rec,3,2),'xx');
v_index_3 := to_number(substr(v_rec,5,2),'xx');
RESULT := v_index_1||','||v_index_2||','||v_index_3;
ELSIF color IS NULL THEN
RESULT := NULL;
END IF;
RETURN(RESULT);
END fun_color_switch;
Oracle函数 - 根据特定字符切割字符串
最新推荐文章于 2024-01-31 09:05:26 发布