递归层次表使用

1.创建表

create table OSS_CHARATER_DICT_INFO
(
  characterid   NUMBER(8) not null,
  charactername VARCHAR2(128) not null,
  charactertype NUMBER(5) default 0 not null,
  fatherid      NUMBER(6) default 0 not null,
  extint        NUMBER(10) default 0 not null,
  extstring     VARCHAR2(128),
  createtime    DATE default SYSDATE not null,
  modifytime    DATE default SYSDATE not null,
  ordertype     NUMBER(3) default 0 not null,
  isshow        NUMBER(1) default 0 not null,
  selecttype    NUMBER(2) default 0 not null,
  selectnum     NUMBER(2) default 0 not null,
  menu_flag     NUMBER default 0 not null,
  store_type_id NUMBER default 1 not null
);

2.写入数据(直接拷贝在pl/sql里执行)

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (401, '电子商务', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 401, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (402, '招聘类', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 402, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (404, '旅行/酒店', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 404, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (405, '其他', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 405, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (406, '交友', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 406, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (407, '团购类', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 407, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (408, '网上支付习惯用户', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 408, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (409, '赶集网', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 409, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (410, '奇虎网', 0, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 410, 1, 0, 2, 0, 1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (411, '新浪', -1, 4, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 411, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (412, '网易', -1, 4, 0, null, to_date('26-11-2012 16:57:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2012 16:57:00', 'dd-mm-yyyy hh24:mi:ss'), 12, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (413, '腾讯', -1, 4, 0, null, to_date('26-11-2012 16:57:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2012 16:57:00', 'dd-mm-yyyy hh24:mi:ss'), 13, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (414, '搜狐', -1, 4, 0, null, to_date('15-05-2013 14:47:04', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-05-2013 14:47:04', 'dd-mm-yyyy hh24:mi:ss'), 414, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (415, '雅虎', -1, 4, 0, null, to_date('15-05-2013 14:47:04', 'dd-mm-yyyy hh24:mi:ss'), to_date('15-05-2013 14:47:04', 'dd-mm-yyyy hh24:mi:ss'), 415, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (416, 'gmail', -1, 4, 0, null, to_date('21-05-2013 14:55:30', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-05-2013 14:55:30', 'dd-mm-yyyy hh24:mi:ss'), 416, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (40202, '前程无忧', -1, 402, 1, null, to_date('26-11-2012 13:07:55', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2012 13:07:55', 'dd-mm-yyyy hh24:mi:ss'), 12, 1, -1, -1, 1, -1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (40100021, '广东人才网', 4, 402, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 21, 1, 1, 1, 0, 1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (40100023, '智联招聘', 4, 402, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 23, 1, 1, 1, 0, 1);

insert into OSS_CHARATER_DICT_INFO (CHARACTERID, CHARACTERNAME, CHARACTERTYPE, FATHERID, EXTINT, EXTSTRING, CREATETIME, MODIFYTIME, ORDERTYPE, ISSHOW, SELECTTYPE, SELECTNUM, MENU_FLAG, STORE_TYPE_ID)
values (40100024, '中华英才网', 4, 402, 0, null, to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-05-2012 15:07:44', 'dd-mm-yyyy hh24:mi:ss'), 24, 1, 1, 1, 0, 1);
commit;

3.创建函数(返回输入参数的上级节点)

CREATE OR REPLACE Function Get_label_Full_Path(Pi_Labebno In Number)
  Return Varchar As

  l_Full_Path Varchar2(1024);

Begin
  For l_Cur_Path In (Select Charactername
                       From (Select Level Lv, t.Charactername
                               From Oss_Charater_Dict_Info t 　　
                               start With Characterid = Pi_Labebno 　　connect By Prior Fatherid = Characterid)
                      Order By Lv Desc) Loop
    l_Full_Path:= l_Full_Path||'-->'||l_Cur_Path.Charactername;
  End Loop;
  Return ltrim(l_Full_Path,'-->');
End;

4.使用例子

select Get_label_Full_Path(t.characterid),t.* from OSS_CHARATER_DICT_INFO t
Where t.characterid = 40100021