建表

建表（部分）

--用户表
create table xyu_user(
userno integer not null,
userid varchar2(16) default '' not null,
passwd varchar2(30) default '' not null,
name varchar2(20) default '' not null,
creditcard varchar2(18) default '' not null,
tel varchar2(20) default '' not null,
effective number(1,0) default 0 not null,
createdate date,
orgid varchar2(10) default 'aaaaaaaaaa',
constraint user_pk primary key(userno),
constraint user_uk unique (userid)
);

create sequence userno increment by 1 start with 1;

--角色表
create table xyu_role(
roleno integer not null,
name varchar2(20) default '' not null,
info varchar2(100) default '' not null,
constraint role_pk primary key(roleno)
);

create sequence roleno increment by 1 start with 1;

--权限表
create table xyu_item(
itemno integer not null,
name varchar2(100) default '' not null,
info varchar2(100) default '' not null,
constraint item_pk primary key(itemno)
);

create sequence itemno increment by 1 start with 1;

--用户角色表
create table xyu_userrole(
urno integer not null,
userno integer not null,
roleno integer not null,
constraint ur_pk primary key(urno),
constraint ur_fk1 foreign key(userno) references xyu_user(userno),
constraint ur_fk2 foreign key(roleno) references xyu_role(roleno)
);

create sequence urno increment by 1 start with 1;

--用户权限表
create table xyu_useritem(
uino integer not null,
userno integer not null,
itemno integer not null,
constraint ui_pk primary key(uino),
constraint uir_fk1 foreign key(userno) references xyu_user(userno),
constraint ui_fk2 foreign key(itemno) references xyu_item(itemno)
);

create sequence uino increment by 1 start with 1;

--角色权限表
create table xyu_roleitem(
rino integer not null,
roleno integer not null,
itemno integer not null,
constraint ri_pk primary key(rino),
constraint rr_fk1 foreign key(roleno) references xyu_role(roleno),
constraint ri_fk2 foreign key(itemno) references xyu_item(itemno)
);

create sequence rino increment by 1 start with 1;