可以新创建一张临时表tmp_table ,一个字段tmp_value,类型为number(4),在存储过程中,把传进来的3, 5, 4,1都insert 到临时表中,然后再将执行select * from tmp_table order by tmp_value后的结果输出出来就行了。。呵呵。
帮你写了个转换用的函数,执行这个函数就可以转换了。函数内部使用了wm_concat方法,该方法需要oracle 10g以上版本。 CREATE OR REPLACE FUNCTION f_PaiXu(OldStr VarChar2) RETURN VarChar2 AS TmpStr VarChar2(4000); NewStr VarChar2(4000); WordCount Number(4); BEGIN -- 如果参数为空,则返回空 If OldStr Is Null Then RETURN Null; End If;
-- 将排序后的数字重新拼接成字符串 SELECT WM_CONCAT(X) INTO NewStr FROM -- 将分解出来的数字排序 (SELECT A.X FROM -- 将字符串分解为数字(列转行) (SELECT to_number(regexp_substr(TmpStr,'[0-9]+',1,LEVEL)) X FROM DUAL CONNECT BY LEVEL <= WordCount ) A ORDER BY A.X); RETURN NewStr; END f_PaiXu;
1. 创建type 类型表,存放数据CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(500);2.创建函数CREATE OR REPLACE FUNCTION SEND_SPLIT(src VARCHAR2, delimiter varchar2) RETURN mytable IS psrc VARCHAR2(500); a mytable := mytable(); i NUMBER := 1; j NUMBER := 1; BEGIN psrc := RTrim(LTrim(src, delimiter), delimiter); LOOP i := InStr(psrc, delimiter, j); --Dbms_Output.put_line(i); IF i > 0 THEN a.extend; a(a.Count) := Trim(SubStr(psrc, j, i - j)); j := i + 1; --Dbms_Output.put_line(a(a.Count-1)); END IF; EXIT WHEN i = 0; END LOOP; IF j <= Length(psrc) THEN a.extend; a(a.Count) := Trim(SubStr(psrc, j, Length(psrc) + 1 - j)); END IF; RETURN a; END;3.输出测试select wm_concat(col) from ( SELECT column_value as col FROM TABLE(CAST(SEND_SPLIT('1,9,5,6', ',')AS mytable)) order by column_value asc) 这个wm_concat ,oracle10g 才有
--不需要函数 写个sql就可以了。 ----T1 代表你的字符串存储的表 ----A 是字段[TEST@myoracle] SQL>WITH T1 AS( 2 SELECT '3,5,4,1' A FROM dual UNION ALL 3 SELECT '13,5,44,17' A FROM dual UNION ALL 4 SELECT '73,54,14,51' A FROM dual UNION ALL 5 SELECT '312,534,544,61' A FROM dual 6 )SELECT WM_CONCAT(ANS) ANS 7 FROM (SELECT A, TO_NUMBER(REGEXP_SUBSTR(T1.A, '[^,]+', 1, L)) ANS 8 FROM T1, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T2 9 WHERE LENGTH(T1.A) - LENGTH(REPLACE(T1.A, ',', '')) >= L - 1 10 ORDER BY A, ANS) 11 GROUP BY A;ANS -------------------------------------------------------------------------------- 5,13,17,44 1,3,4,5 61,534,544,312 14,54,73,51
具体代码不写了,给你一个思路
1.将传入的字符串转换成数字(使用,to_number()函数)
2.然后将数字拆分成一个个独立的数字,存入数组中
3.进行排序(很多排序算法)
4.将排好序的数字从数组中取出,连接成字符串,返回。
是啊,传入的是有效数字的字符串(不确定几个数字,格式例如:3,5,4,1),然后返回1,3,4,5, 能有具体的代码吗?没有写过这样的,不知道具体该怎么实现呢?因为我是oracle一个新手啊!!!
CREATE OR REPLACE FUNCTION f_PaiXu(OldStr VarChar2) RETURN VarChar2
AS
TmpStr VarChar2(4000);
NewStr VarChar2(4000);
WordCount Number(4);
BEGIN
-- 如果参数为空,则返回空
If OldStr Is Null Then
RETURN Null;
End If;
-- 初始化待处理字符串
TmpStr := OldStr||',';
-- 计算字符串中有几个数字
WordCount := LENGTH(TmpStr) - LENGTH(REPLACE(TmpStr,','));
-- 将排序后的数字重新拼接成字符串
SELECT WM_CONCAT(X)
INTO NewStr
FROM
-- 将分解出来的数字排序
(SELECT A.X
FROM
-- 将字符串分解为数字(列转行)
(SELECT to_number(regexp_substr(TmpStr,'[0-9]+',1,LEVEL)) X
FROM DUAL
CONNECT BY LEVEL <= WordCount ) A
ORDER BY A.X);
RETURN NewStr;
END f_PaiXu;
RETURN mytable IS
psrc VARCHAR2(500);
a mytable := mytable();
i NUMBER := 1;
j NUMBER := 1;
BEGIN
psrc := RTrim(LTrim(src, delimiter), delimiter);
LOOP
i := InStr(psrc, delimiter, j);
--Dbms_Output.put_line(i);
IF i > 0 THEN
a.extend;
a(a.Count) := Trim(SubStr(psrc, j, i - j));
j := i + 1;
--Dbms_Output.put_line(a(a.Count-1));
END IF;
EXIT WHEN i = 0;
END LOOP;
IF j <= Length(psrc) THEN
a.extend;
a(a.Count) := Trim(SubStr(psrc, j, Length(psrc) + 1 - j));
END IF;
RETURN a;
END;3.输出测试select wm_concat(col) from (
SELECT column_value as col FROM TABLE(CAST(SEND_SPLIT('1,9,5,6', ',')AS mytable)) order by column_value asc)
这个wm_concat ,oracle10g 才有
--不需要函数 写个sql就可以了。
----T1 代表你的字符串存储的表
----A 是字段[TEST@myoracle] SQL>WITH T1 AS(
2 SELECT '3,5,4,1' A FROM dual UNION ALL
3 SELECT '13,5,44,17' A FROM dual UNION ALL
4 SELECT '73,54,14,51' A FROM dual UNION ALL
5 SELECT '312,534,544,61' A FROM dual
6 )SELECT WM_CONCAT(ANS) ANS
7 FROM (SELECT A, TO_NUMBER(REGEXP_SUBSTR(T1.A, '[^,]+', 1, L)) ANS
8 FROM T1, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T2
9 WHERE LENGTH(T1.A) - LENGTH(REPLACE(T1.A, ',', '')) >= L - 1
10 ORDER BY A, ANS)
11 GROUP BY A;ANS
--------------------------------------------------------------------------------
5,13,17,44
1,3,4,5
61,534,544,312
14,54,73,51