树状菜单展示

CREATE OR REPLACE FUNCTION menuDisplay(p_fillchar in varchar2, --填充字符
                                       p_fillnum  in varchar2, --填充字符数
                                       p_table    in varchar2, --表名
                                       p_path     in varchar2 --排序路径(列名)
                                       ) RETURN SYS_REFCURSOR AS
  st_cursor SYS_REFCURSOR;
  v_sql     varchar2(4000);
BEGIN
  v_sql := 'select id, join_char(:p_fillchar, (grade - 1) * :p_fillnum) || grade || ''--'' || name name' ||
           ' from (select id,name,level grade,sys_connect_by_path(' ||
           p_path || ', ''-'') path from ' || p_table ||
           ' connect by prior id = parent_id start with id = 1) order by path';
  --dbms_output.put_line(v_sql);
  OPEN st_cursor FOR v_sql
    USING p_fillchar, p_fillnum;
  RETURN st_cursor;
END;