--建表 create table code_t_division(codeid varchar2(50), codename varchar2(100)); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610', '中国'); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610001', '重庆'); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610001001', '永川'); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610002', '成都'); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610002001', '德阳'); insert into CODE_T_DIVISION (CODEID, CODENAME) values ('610002002', '绵阳'); commit; --创建函数 create or replace function getAllName(i_codeid varchar2) return varchar2 is result varchar2(4000); begin SELECT wmsys.wm_concat(t.codename) into result FROM code_t_division t START WITH t.codeid = i_codeid CONNECT BY t.codeid = PRIOR substr(t.codeid, 1, length(t.codeid) - 3) ORDER BY t.codeid; return result; exception when others then result := ''; return result; end; / --测试 select getAllName(codeid) from code_t_division;
--建表
create table code_t_division(codeid varchar2(50), codename varchar2(100));
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610', '中国');
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610001', '重庆');
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610001001', '永川');
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610002', '成都');
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610002001', '德阳');
insert into CODE_T_DIVISION (CODEID, CODENAME)
values ('610002002', '绵阳');
commit;
--创建函数
create or replace function getAllName(i_codeid varchar2) return varchar2 is
result varchar2(4000);
begin
SELECT wmsys.wm_concat(t.codename)
into result
FROM code_t_division t
START WITH t.codeid = i_codeid
CONNECT BY t.codeid = PRIOR substr(t.codeid, 1, length(t.codeid) - 3)
ORDER BY t.codeid;
return result;
exception
when others then
result := '';
return result;
end;
/
--测试
select getAllName(codeid) from code_t_division;
应该是用一个语句实现更难,
其实ORACLE学习应该多多实践一下