1.建Function程序
CREATE OR REPLACE FUNCTION sort_string(p_string IN VARCHAR2) RETURN VARCHAR2 IS
TYPE typ_rec IS RECORD(
letter VARCHAR2(256), --记录字母
seq PLS_INTEGER --记录字母出现次数
);
TYPE tab_rec IS TABLE OF typ_rec INDEX BY BINARY_INTEGER;
tabr tab_rec;
l_result VARCHAR2(32767);
BEGIN
FOR i IN 1 .. length(p_string) LOOP
tabr(ascii(substr(p_string, i, 1))).letter := substr(p_string,
i,
1);
tabr(ascii(substr(p_string, i, 1))).seq := nvl(tabr(ascii(substr(p_string, i, 1))).seq,
0) + 1;
END LOOP;
FOR i IN tabr.first .. tabr.last LOOP
IF tabr.exists(i) THEN
l_result := l_result || rpad(tabr(i).letter,
tabr(i).seq,
tabr(i).letter);
END IF;
END LOOP;
RETURN l_result;
END;
2.用法
例子1:SQL> SELECT sort_string ('43258888') FROM DUAL;
SORT_STRING('43258888')
--------------------------------------------------------------------------------
23458888
例子2:
SQL> SELECT sort_string ('klaktkky') FROM DUAL;
SORT_STRING('KLAKTKKY')
--------------------------------------------------------------------------------
akkkklty
文本地址:http://blog.csdn.net/sunansheng/article/details/46331485