oracle章节排序

oracle 章节排序没有找到合适的方法，写了一个转换函数，方便排序，具体过程如下：

测试代码：

CREATE TABLE TEST (WBSCODE VARCHAR2(120));

INSERT INTO TEST (WBSCODE) VALUES ('1');
INSERT INTO TEST (WBSCODE) VALUES ('1.1');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.1');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.10');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.11');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.12');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.13');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.14');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.15');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.2');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.3');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.4');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.5');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.6');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.7');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.8');
INSERT INTO TEST (WBSCODE) VALUES ('1.1.9');

创建自定义函数。

CREATE OR REPLACE FUNCTION FORSTR(STR1 VARCHAR2, FORMAT VARCHAR2 := 'FM00')
  RETURN VARCHAR2 AS
  RESULT VARCHAR2(600);
  STR    VARCHAR2(300) := STR1;
BEGIN
  WHILE 0 <= INSTR(STR, '.') LOOP
    IF INSTR(STR, '.') = 0 THEN
      RESULT := RESULT || TO_CHAR(STR, FORMAT);
      EXIT;
    ELSE
      RESULT := RESULT ||
                TO_CHAR(TO_NUMBER(SUBSTR(STR, 0, INSTR(STR, '.'))), FORMAT) || '.';
    END IF;
    STR := SUBSTR(STR, INSTR(STR, '.') + 1, LENGTH(STR));
  END LOOP;
  RETURN RESULT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20000, '层次分隔符不匹配,请使用''.''符号!');
END FORSTR;

测试：

select WBSCODE  FROM TEST order by  FORSTR(WBSCODE);

WBSCODE
1
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.1.5
1.1.6
1.1.7
1.1.8
1.1.9
1.1.10
1.1.11
1.1.12
1.1.13
1.1.14
1.1.15