这个查询函数那里不对

[yjc2020]

这个查询函数那里不对
这个查询函数那里不对
在Oracl里的函数是这样写的：
create or replace function ordercode(departmentcode in department.departmentcode%TYPE) return varchar2
is
scode varchar2(10000);
ccode department.departmentcode%TYPE;
begin
scode := '';
ccode := departmentcode;
loop
scode := ccode||scode;
select scode into ccode from department where departmentcode=ccode;
end loop;
return scode;
exception
when NO_DATA_FOUND then return scode;
end;
这样查询
select departmentcode from department order by ordercode(0);

现在不管怎么改都输出
1001
1002
1001001
1003
1003001
1002001
1003002

我希望这样输出
1001
1001001
1002
1002001
1003
1003001
1003002

请问有什么错误

[baojianjun]

不知道你表中數據到底怎樣，我猜想你的查詢的輸出過程是這樣的：
1.1001，1002
2.1001001，1003，1003001
3.1002001，1003002
你可以這樣寫：
select departmentcode
from (
select departmentcode
from department
order by ordercode(0)
)
order by departmentcode ;

[aweihappy]

你那个函数根本没有用；
把查询语句改成:
select departmentcode from department order by departmentcode
就可以得到你想要的结果。

[cxwsoftware]

帮你顶!!!!

[beckhambobo]

哪就简单,只要把当前数据类型修改为字符型:
SQL> create table aa (num number,str varchar2(10));

Table created

SQL> insert into aa values(1001,'1001');

1 row inserted

SQL> insert into aa values(1001001,'1001001');

1 row inserted

SQL> insert into aa values(1002,'1002');

1 row inserted

SQL> select num from aa order by num;

NUM
----------
1001
1002
1001001

SQL> select str from aa order by str;

STR
----------
1001
1001001
1002

当数值型不能达到你要求,当字符型就能达到你的要求