CREATE OR REPLACE TYPE CUX_STR_SPLIT_TYPE IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE PACKAGE cux_char_handle_util_pkg IS TYPE char_var_type IS RECORD( char_value VARCHAR2(200)); TYPE char_var_tbl IS TABLE OF char_var_type INDEX BY BINARY_INTEGER; g_char_var_tbl char_var_tbl; FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN cux_str_split_type PIPELINED; PROCEDURE prepare_char_value(p_str_var VARCHAR2, p_delimiter IN VARCHAR2); FUNCTION make_char_str(p_str VARCHAR2, p_delimiter IN VARCHAR2) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY cux_char_handle_util_pkg IS --拆分字符串 FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN cux_str_split_type PIPELINED AS v_length NUMBER := length(p_string); v_start NUMBER := 1; v_index NUMBER; BEGIN WHILE (v_start <= v_length) LOOP v_index := instr(p_string, p_delimiter, v_start); IF v_index = 0 THEN PIPE ROW(substr(p_string, v_start)); v_start := v_length + 1; ELSE PIPE ROW(substr(p_string, v_start, v_index - v_start)); v_start := v_index + 1; END IF; END LOOP; RETURN; END; /****************************************************** \ \ 将字符串处理为全局数组变量g_char_var_tbl, \ \ \ ********************************************************/ PROCEDURE prepare_char_value(p_str_var VARCHAR2, p_delimiter IN VARCHAR2) IS CURSOR cur_cloumn(p_str VARCHAR2) IS SELECT column_value FROM TABLE(splitstr(p_str, p_delimiter)); i NUMBER := 1; l_str_var VARCHAR2(240); BEGIN l_str_var := p_str_var; g_char_var_tbl.delete; OPEN cur_cloumn(l_str_var); LOOP FETCH cur_cloumn INTO g_char_var_tbl(i).char_value; EXIT WHEN cur_cloumn%NOTFOUND; i := i + 1; END LOOP; CLOSE cur_cloumn; END; /****************************************************** \在某些情况下,例如2.12.15.1, 2.2.1, 2.2.1.5, 2.20.1.1 \需要按每一段内的数字大小进行排序 \ \ 处理思路如下,将字符串按指定字符拆分到一个数组里面,并把每段左边补0补齐至2位(按需调整) \ 将最后拼接的字符串向右补0,补齐至12位(按需调整,我本次的字符串不会超过6段) \
\ 对于类型为字符串或者汉字的仍然通用(由于汉字占三个字符,所以lpad的长度需要满足3*最大中文段长度),最后的排序结果为默认的oracle ansii排序,需要自己对中文再排序
\ \ 最后在外层对返回的字符串进行排序即可 \ ********************************************************/ FUNCTION make_char_str(p_str VARCHAR2, p_delimiter IN VARCHAR2) RETURN VARCHAR2 IS v_str VARCHAR2(10); p_result_str VARCHAR2(50) := NULL; BEGIN --拆分字符串并拼接成数组,数组为全局变量g_char_var_tbl prepare_char_value(p_str, p_delimiter); FOR i IN 1 .. g_char_var_tbl.count LOOP --每段向左添加0补齐 v_str := lpad(g_char_var_tbl(i).char_value, 2, '0'); --拼接 p_result_str := p_result_str || v_str; END LOOP; --将最后结果向右添加0补齐至12位 p_result_str := rpad(p_result_str, 12, '0'); RETURN p_result_str; END; END;
示例
SELECT cux_char_handle_util_pkg.make_char_str(csa.account_code, '.'), csa.head_id, csa.* FROM cux_cst_accounts csa WHERE csa.head_id = 28 AND csa.account_code <> '根节点' ORDER BY csa.account_code
加入
SELECT cux_char_handle_util_pkg.make_char_str(csa.account_code, '.'), csa.head_id, csa.* FROM cux_cst_accounts csa WHERE csa.head_id = 28 AND csa.account_code <> '根节点' ORDER BY cux_char_handle_util_pkg.make_char_str(csa.account_code, '.');