项目正式——数据访问接口模块

-- 删除外键约束
alter table T_INTERCFG
   drop constraint FK_INTERCFG_DATATYPE;

-- 删除表
drop table T_INTERCFG cascade constraints;

-- 创建序列
create sequence SEQ_INTERCFG increment by 1 minvalue 1 nocycle;

-- 创建表
create table T_INTERCFG 
(
   intername          varchar2(30)         not null,
   typeid             varchar2(30),
   intercname         varchar2(100),
   selectsql          varchar2(1000)       not null,
   colstr             varchar2(300)        not null,
   bindin             varchar2(300),
   orderby            number(5),
   memo               varchar2(300),
   rsts               number(15)           default 1 not null,
   upttime            date                 default sysdate not null,
   recid              number(15)           not null,
   constraint PK_INTERCFG primary key (intername),
   constraint INTERCFG_KEYID unique (typeid)
);

-- 添加表注释
comment on table T_INTERCFG is
'本表存放了全部接口的配置参数。';

-- 添加列注释
comment on column T_INTERCFG.intername is
'接口代码，英文名。';

comment on column T_INTERCFG.typeid is
'数据种类。';

comment on column T_INTERCFG.intercname is
'接口名称，中文名。';

comment on column T_INTERCFG.selectsql is
'接口SQL。';

comment on column T_INTERCFG.colstr is
'输出列名，列名之间用逗号分隔。';

comment on column T_INTERCFG.bindin is
'接口参数，参数之间用逗号分隔。';

comment on column T_INTERCFG.orderby is
'显示顺序。';

comment on column T_INTERCFG.memo is
'备注。';

comment on column T_INTERCFG.rsts is
'记录状态，1-启用；2-禁用。';

comment on column T_INTERCFG.upttime is
'更新时间。';

comment on column T_INTERCFG.recid is
'记录编号，从与本表同名的序列生成器中获取。';

-- 添加外键约束
alter table T_INTERCFG
   add constraint FK_INTERCFG_DATATYPE foreign key (typeid)
      references T_DATATYPE (typeid);

-- 删除表
drop table T_USERINFO cascade constraints;

-- 创建序列
create sequence SEQ_USERINFO increment by 1 minvalue 1 nocycle;

-- 创建表
create table T_USERINFO 
(
   username           varchar2(30)         not null,
   passwd             varchar2(30)         not null,
   appname            varchar2(50)         not null,
   ip                 varchar2(50),
   contacts           varchar2(50),
   tel                varchar2(50),
   email              varchar2(50),
   memo               varchar2(300),
   rsts               number(1)            default 1 not null,
   upttime            date                 default sysdate not null,
   recid              number(15)           not null,
   constraint PK_USERINFO primary key (username),
   constraint USERINFO_KEYID unique (appname)
);

-- 添加表注释
comment on table T_USERINFO is
'本表存放了客户端的身份认证信息。';

-- 添加列注释
comment on column T_USERINFO.username is
'用户名。';

comment on column T_USERINFO.passwd is
'密码。';

comment on column T_USERINFO.appname is
'应用名称。';

comment on column T_USERINFO.ip is
'绑定ip，多个ip之间用逗号分隔。';

comment on column T_USERINFO.contacts is
'联系人。';

comment on column T_USERINFO.tel is
'联系电话。';

comment on column T_USERINFO.email is
'联系邮箱。';

comment on column T_USERINFO.memo is
'备注。';

comment on column T_USERINFO.rsts is
'记录状态，1-启用；2-禁用。';

comment on column T_USERINFO.upttime is
'更新时间。';

comment on column T_USERINFO.recid is
'记录编号，从与本表同名的序列生成器中获取。';