-- 建立测试表
CREATETABLE t1 (a VARCHAR2 (500));
-- 生成测试数据
INSERTINTO t1 (a)
VALUES ('1.11');
INSERTINTO t1 (a)
VALUES ('1.9');
INSERTINTO t1 (a)
VALUES ('1.21');
INSERTINTO t1 (a)
VALUES ('2.1.0.1');
INSERTINTO t1 (a)
VALUES ('2.2.1');
INSERTINTO t1 (a)
VALUES ('2.11.9.1');
INSERTINTO t1 (a)
VALUES ('2.1.9.1');
INSERTINTO t1 (a)
VALUES ('1.1.1');
INSERTINTO t1 (a)
VALUES ('123.99');
INSERTINTO t1 (a)
VALUES ('1.9.0');
INSERTINTO t1 (a)
VALUES ('1.9.0.0');
COMMIT;
-- 建立存储过程,返回排序后的结果集
CREATEORREPLACEPROCEDURE p_order (ret OUT SYS_REFCURSOR)
IS
s_select VARCHAR2 (32767)
:= 'select rtrim(a,''.'') from (select a, to_number(substr(a,1,instr(a,''.'',1,1) -1 )) c1 ';
l_max_length INT := 0;
s_order VARCHAR2 (2000) := ' order by c1 nulls first ';
j INT := 0;
BEGIN
SELECTMAX (LENGTH (TRANSLATE (a, '.' || a, '.')))
INTO l_max_length
FROM t1;
FOR i IN 1 .. l_max_length
LOOP
j := i + 1;
s_select :=
s_select
|| ', to_number(substr(a,instr(a,''.'',1'
|| i
|| ') +1, instr(a,''.'',1,'
|| j
|| ') - instr(a,''.'',1'
|| i
|| ') -1)) c'
|| j;
s_order := s_order || ', c' || j || ' nulls first';
END LOOP;
s_select := s_select || ' from (select a||''.'' a from t1))' || s_order;
-- dbms_output.put_line(s_select);
OPEN ret FOR s_select;
END;
/
-- 测试
VARIABLE x REFCURSOR;
EXEC p_order(:x);
PRINT x;
--转自