Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5145694
  • 博文数量: 1004
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13042
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1004)

文章存档

2020年(1)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2016-07-08 08:55:02

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

阅读(2155) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~