请教一下,oracle如何自定义一个函数来实现排序的功能
比如我在数据库中有一列值如下:
01,02,03,07,09,11,13,14,12,10,08,06,05,04
11,12,13,17,19,21,23,25,31,30,29,28,27,26,24,22,20,18,16,15,14
。
我想实现在查询出这列时直接在数据库完成此列的排序再显示
比如我在数据库中有一列值如下:
01,02,03,07,09,11,13,14,12,10,08,06,05,04
11,12,13,17,19,21,23,25,31,30,29,28,27,26,24,22,20,18,16,15,14
。
我想实现在查询出这列时直接在数据库完成此列的排序再显示
select dbms_lob.substr(regexp_substr(youcolname, '[^,]+', 1, x.n)) as t
from dual a, (select rownum n from dual connect by rownum < 100) x
) order by t asc
还有可能描述的不好,让大家有所误会,那些值是已经在数据库中了,我做的是把每行的此列值在取出时候排个序
加个order by 列名 不就可以了?
难道是我没理解你的意思?
ID content
1 01,02,03,07,09,11,13,14,12,10,08,06,05,04
2 09,11,13,14,12,10
3 09,11,13,14,12,10
。。
我要做的是:
select id,自定义排序函数(content) as content from A我要的就是实现content外面的那个自定义排序函数,应该讲的很清楚了吧^_^
------测试下。。,。。
SQL>
SQL> with a as
2 (
3 select '01,02,03,07,09,11,13,14,12,10,08,06,05,04' name from dual
4 )
5 select wm_concat(t) from (
6 select * from (
7 select dbms_lob.substr(regexp_substr(name, '[^,]+', 1, rownum)) as t
8 from a connect by rownum <length(name)-length(replace(name,',',''))+1
9 ) order by t);WM_CONCAT(T)
-----------------------------------------------------------------------------01,02,03,05,06,07,08,09,10,11,12,13,14SQL>
(
select '01,02,03,07,09,11,13,14,12,10,08,06,05,04' name from dual
union all
select '11,12,13,17,19,21,23,25,31,30,29,28,27,26,24,22,20,18,16,15,14' from dual
)
select wm_concat(t) from (
select * from (
select dbms_lob.substr(regexp_substr(name, '[^,]+', 1, rownum)) as t
from a connect by rownum <length(name)-length(replace(name,',',''))+1
) order by t);
create or replace function fun_order_col(v_str varchar2)
return varchar2
as
sqlstr varchar2(4000);
v_sqlstr varchar2(4000);
begin
v_sqlstr:=','||v_str||',';
sqlstr:='select wm_concat(newid)
from (select newid from
(select
substr('''||v_sqlstr||''',instr('''||v_sqlstr||''','','',1,rownum)+1,
instr('''||v_sqlstr||''','','',1,rownum+1)-
instr('''||v_sqlstr||''','','',1,rownum)-1) newid
from dual
connect by rownum <= length('''||v_sqlstr||''')
- length(replace('''||v_sqlstr||''', '','', ''''))-1)
order by newid)';
execute immediate sqlstr into v_sqlstr;
return v_sqlstr;
end; with tb as(
select 1 ID, '01,02,03,07,09,11,13,14,12,10,08,06,05,04' content from dual union all
select 2, '09,11,13,14,12,10' from dual union all
select 3, '09,03,12,10' from dual)
--以上为提供数据的语句
select id,fun_order_col(content) content from tb
ID CONTENT
---------- --------------------------------------------------
1 01,02,03,04,05,06,07,08,09,10,11,12,13,14
2 09,10,11,12,13,14
3 03,09,10,12
create or replace function fun_order_col(v_str varchar2)
return varchar2
as
sqlstr varchar2(4000);
v_sqlstr varchar2(4000);
begin
sqlstr:='select wm_concat(t) from (
elect * from (
elect dbms_lob.substr(regexp_substr('''||v_str||''', ''[^,]+'', 1, rownum)) as t
rom dual connect by rownum <
ength('''||v_str||''')-length(replace('''||v_str||''','','',''''))+1
order by t)';
execute immediate sqlstr into v_sqlstr;
return v_sqlstr;
end;
EXECUTE IMMEDIATE sqlstr INTO v_sqlstr;
你可以用dbms_output.put_line()打印出来看看