建表語句
序列
create sequence seq_jx
increment by 1
start with
1
nomaxvalue
nominvalue
cache 10;
字典型別表
create table sys_dict_type(
id number(20) primary key,
name varchar2(100) ,
type varchar2(100) ,
group_code varchar2(100) ,
status char(1)
);
comment on able sys_dict_type is '系統字典型別表';
comment on olumn sys_dict_type.name is '字典名稱';
comment on olumn sys_dict_type.type is '字典型別編碼';
comment on olumn sys_dict_type.group_code is '字典分組 (system:系統字典)';
comment on olumn sys_dict_type.status is '狀態 (0:正常 1:停用)';
insert into sys_dict_type values(seq_jx.nextval,'性別','gender','system','0');
insert into sys_dict_type values(seq_jx.nextval,'模組','module','system','0');
字典值表
create table sys_dict_data(
id number(20) primary key,
type varchar2(100) ,
group_code varchar2(100) ,
label varchar2(100) ,
value varchar2(100) ,
default_value char(1),
status char(1)
);
comment on able sys_dict_data is '系統字典值表';
comment on olumn sys_dict_data.type is '系統字典型別表';
comment on olumn sys_dict_data.group_code is '字典型別編碼';
comment on olumn sys_dict_data.label is '字典標籤';
comment on olumn sys_dict_data.value is '字典鍵值';
comment on olumn sys_dict_data.default_value is '是否預設值 (Y:是 N:否)';
comment on olumn sys_dict_data.status is '狀態 (0:正常 1:停用)';
insert into sys_dict_data values(seq_jx.nextval,'gender','system','男','male','Y','0');
insert into sys_dict_data values(seq_jx.nextval,'gender','system','女','female','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','system','系統','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','auth','授權與認證','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','order','訂單','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','product','商品','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','member','會員','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','pay','支付','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','stock','庫存','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','cart','購物車','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','cms','內容管理','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','report','報表','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','promotion','促銷','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','seller','商家','N','0');
insert into sys_dict_data values(seq_jx.nextval,'module','system','search','搜尋','N','0');
分頁查詢
參考: Oracle中進行分頁查詢的三種方法
select * from (select rownum no, a.* from sys_dict_data a) where no between 1 and 5;
select * from sys_dict_data where rownum between 1 and 5;
select * from sys_dict_data where rownum < 7 minus select * from sys_dict_data where rownum < 1;
七種 sql join
新增資料
插入兩條資料
insert into sys_dict_type values(seq_jx.nextval,'left','left','left','0');
insert into sys_dict_data values(seq_jx.nextval,'right','right','right','right','N','0');
現在兩張表的資料如下
左表
SQL> select * from sys_dict_type;
ID NAME TYPE GROUP_CODE STATUS
---------- --------------- --------------- --------------- ---------------
1 性別 gender system 0
21 模組 module system 0
35 left left left 0
SQL>
右表
SQL> select * from sys_dict_data;
ID TYPE GROUP_CODE LABEL VALUE DEFAULT_VALUE STATUS
---------- --------------- --------------- --------------- --------------- --------------- ---------------
11 gender system 男 male Y 0
12 gender system 女 female N 0
22 module system system 系統 N 0
23 module system auth 授權與認證 N 0
24 module system order 訂單 N 0
25 module system product 商品 N 0
26 module system member 會員 N 0
27 module system pay 支付 N 0
28 module system stock 庫存 N 0
29 module system cart 購物車 N 0
30 module system cms 內容管理 N 0
31 module system report 報表 N 0
32 module system promotion 促銷 N 0
33 module system seller 商家 N 0
34 module system search 搜尋 N 0
41 right right right right N 0
已選擇16行。
SQL>
第一種
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
left join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第二種
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
left join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where t2.value is not null;
第三種
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
right join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第四種
select
distinct t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
right join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where t1.type is not null;
第五種
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
inner join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
第六種
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
full outer join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code
where
t1.group_code is null
or t2.group_code is null;
第七種
select
distinct
t1.name,
t1.type,
t1.group_code,
t2.label,
t2.value,
t2.default_value
from
sys_dict_type t1
full outer join sys_dict_data t2
on
t1.type = t2.type
and t1.group_code = t2.group_code;
