oracle path函数,自定义类似 sys_connect_by_path 功能的函数

真是简单又巧妙的办法！美中不足的是他只设置了一组全局变量，如果调用多次就会发生覆盖，比如：

SELECT hierarchy.sys_connect_by_path(level, firstname, '/')

,hierarchy.sys_connect_by_path(level, lastname, '/')

FROM ...

我来修改一下，这下子真正实用了：

create or replace package hierarchy is

type strtabletype1 is table of varchar2(4000) index by binary_integer;

type strtabletype is table of strtabletype1 index by binary_integer;

strtable strtabletype;

type numtabletype1 is table of number index by binary_integer;

type numtabletype is table of numtabletype1 index by binary_integer;

numtable numtabletype;

function sys_connect_by_path(p_level     in number,

p_value     in varchar2,

p_delimiter in varchar2 default ',',

p_index     IN NUMBER DEFAULT 1)

return varchar2;

function sys_sum_by_path(p_level in number, p_value in number,p_index     IN NUMBER DEFAULT 1)

return number;

pragma restrict_references(sys_connect_by_path, wnds);

pragma restrict_references(sys_sum_by_path, wnds);

end;

/

create or replace package body hierarchy is

ls_ret varchar2(4000);

ln_ret number;

function sys_connect_by_path(p_level     in number,

p_value     in varchar2,

p_delimiter in varchar2 default ',',

p_index     IN NUMBER DEFAULT 1)

return varchar2 is

begin

strtable(p_index)(p_level) := p_value;

ls_ret := p_value;

for i in reverse 1 .. p_level - 1 loop

ls_ret := strtable(p_index)(i) || p_delimiter || ls_ret;

end loop;

return ls_ret;

end;

function sys_sum_by_path(p_level in number, p_value in number,p_index     IN NUMBER DEFAULT 1)

return number is

begin

numtable(p_index)(p_level) := p_value;

ln_ret := p_value;

for i in reverse 1 .. p_level - 1 loop

ln_ret := numtable(p_index)(i) + ln_ret;

end loop;

return ln_ret;

end;

end;

/

SELECT hierarchy.sys_connect_by_path(level, firstname, '/')   ---- 不带额外参数则为第一组

,hierarchy.sys_connect_by_path(level, lastname, '/',2)  ---- 带额外参数=2则为第二组

FROM ...

等会我就用它来做做那个公交的题目。