txl_table.sql

此程序为纯jsp实现通讯录功能

-- Create table
create table TXL_ORG
(
  ORG_ID       NUMBER(8) not null,
  ORG_NAME     VARCHAR2(120) not null,
  UPPER_ORG_ID NUMBER(8) not null,
  ORG_NO       NUMBER(8),
  ORG_LEVEL    NUMBER(1) not null
)

-- Create sequence 
create sequence TXL_ORG_SEQ
minvalue 1
maxvalue 9999999999999
start with 401
increment by 1
cache 20;


-- Create table
create table TXL_PERSON
(
  ID        NUMBER(13),
  NAME      VARCHAR2(320),
  CODE      VARCHAR2(320),
  ORG_ID    VARCHAR2(13),
  POST      VARCHAR2(320),
  ROOMID    VARCHAR2(320),
  CITYID    VARCHAR2(320),
  PHONE     VARCHAR2(320),
  EXTENSION VARCHAR2(320),
  MOBILE    VARCHAR2(320),
  TDNO      VARCHAR2(320),
  EMAIL     VARCHAR2(320),
  FAX       VARCHAR2(320),
  ZIP       VARCHAR2(320),
  ADDRESS   VARCHAR2(1000),
  REMARK    VARCHAR2(1000)
)

-- Create sequence 
create sequence TXL_PERSON_SEQ
minvalue 1
maxvalue 9999999999999
start with 2961
increment by 1
cache 20;

-- Create view
create or replace view txl_view as
select t1.org_id company_ID,
       t1.org_name company_name,
       null unit_ID,
       null unit_name,
       null depart_id,
       null depart_name,
       t2.*
  from txl_org t1, txl_person t2
 where t1.org_level = '1'
   and t1.org_id = t2.org_id
   union all
select t2.org_id 公司ID,
       t2.org_name 公司名,
       t1.org_id 部门ID,
       t1.org_name 部门名,
       null 处室ID,
       null 处室名,
       t3.*
  from txl_org t1, txl_org t2, txl_person t3
 where t1.org_id = t3.org_id
   and t1.org_level = '2'
   and t1.upper_org_id = t2.org_id
   union all
select t3.org_id 公司ID,
       t3.org_name 公司名,
       t2.org_id 部门ID,
       t2.org_name 部门名,
       t1.org_id 处室ID,
       t1.org_name 处室名,
       t4.* from txl_org t1,txl_org t2,txl_org t3,txl_person t4
where t1.org_id = t4.org_id and t1.upper_org_id = t2.org_id
and t2.upper_org_id = t3.org_id