oracle.sql

该程序能够准确的记录互联网用户上网所用的流量

insert into NASYEAR values(1002,'192.168.0.200',add_months(sysdate,3),1432.5,32233,140);
insert into NASYEAR values(1003,'192.168.0.200',add_months(sysdate,4),3432.5,2833,70);
insert into NASYEAR values(1004,'192.168.0.200',add_months(sysdate,5),9432.5,32333,120);
insert into NASYEAR values(1005,'192.168.0.200',add_months(sysdate,6),9432.5,62333,800);
insert into NASYEAR values(1006,'192.168.0.200',add_months(sysdate,7),3232.5,32333,500);
insert into NASYEAR values(1007,'192.168.0.200',add_months(sysdate,8),9432.5,1333,440);
insert into NASYEAR values(1008,'192.168.0.200',add_months(sysdate,9),8432.5,52333,300);
insert into NASYEAR values(1009,'192.168.0.200',add_months(sysdate,10),9432.5,42333,800);
insert into NASYEAR values(1010,'192.168.0.200',add_months(sysdate,11),5432.5,32333,230);
insert into NASYEAR values(1011,'192.168.0.200',add_months(sysdate,12),7432.5,22333,560);


create table USERDAY
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);
comment on column USERDAY.USERNAME is '业务帐号';
comment on column USERDAY.NASIPADDRESS is 'nas ip 地址';
comment on column USERDAY.ONDATE is '时间 ';
comment on column USERDAY.TIME_DURATION is '时长总和';
comment on column USERDAY.FLUX is '流量总和';

create table USERMONTH
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);


comment on column USERMONTH.USERNAME is '业务帐号';
comment on column USERMONTH.NASIPADDRESS is 'nas ip 地址';
comment on column USERMONTH.ONDATE is '时间 ';
comment on column USERMONTH.TIME_DURATION is '时长总和';
comment on column USERMONTH.FLUX is '流量总和';
insert into usermonth values(100,'briup','192.168.0.200',sysdate,23213,213);
insert into usermonth values(101,'briup','192.168.0.200',sysdate+1,5413,123);
insert into usermonth values(102,'briup','192.168.0.200',sysdate+2,123,2413);
insert into usermonth values(103,'briup','192.168.0.200',sysdate+3,673,1423);

create table USERYEAR
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);

 
comment on column USERYEAR.USERNAME is '业务帐号';
comment on column USERYEAR.NASIPADDRESS is 'nasip地址';
comment on column USERYEAR.ONDATE is '时间';
comment on column USERYEAR.TIME_DURATION is '总时长';
comment on column USERYEAR.FLUX is '总流量';
insert into useryear values(100,'briup','192.168.0.200',add_months(sysdate,-1),23213,213);
insert into useryear values(101,'briup','192.168.0.200',add_months(sysdate,-1),54213,423);
insert into useryear values(102,'briup','192.168.0.200',add_months(sysdate,-1),15413,523);
insert into useryear values(103,'briup','192.168.0.200',add_months(sysdate,-1),35413,223);



create table BUSINESSMONTH
(
  ID           NUMBER  primary key,
  PRODUCT_NAME VARCHAR2(30),
  ONDATE       DATE,
  SUMFEE       NUMBER(20,2)
);

comment on column BUSINESSMONTH.ID is 'id号码';
comment on column BUSINESSMONTH.PRODUCT_NAME is '产品名称';
comment on column BUSINESSMONTH.ONDATE is '时间';
comment on column BUSINESSMONTH.SUMFEE is '营收之和';
insert into businessmonth values(1000,'包月制',sysdate,20003.50);
insert into businessmonth values(1001,'小时制',sysdate,30003.50);
insert into businessmonth values(1002,'幸福之家',sysdate,40003.50);
insert into businessmonth values(1003,'抄股专家',sysdate,60003.50);
insert into businessmonth values(1004,'企业高速公路',sysdate,90003.50);

create table BUSINESSYEAR
(
  ID           NUMBER primary key,
  PRODUCT_NAME VARCHAR2(30),
  ONDATE       DATE,
  SUMFEE       NUMBER(20,2)
);


comment on column BUSINESSYEAR.ID is 'id号码';
comment on column BUSINESSYEAR.PRODUCT_NAME is '产品名称';
comment on column BUSINESSYEAR.ONDATE is '时间';
comment on column BUSINESSYEAR.SUMFEE is '营收之和';
insert into businessyear values(1000,'包月制',sysdate,2044003.50);
insert into businessyear values(1001,'小时制',sysdate,3005503.50);
insert into businessyear values(1002,'幸福之家',sysdate,4066003.50);
insert into businessyear values(1003,'抄股专家',sysdate,6000773.50);
insert into businessyear values(1004,'企业高速公路',sysdate,9088003.50);


create table RADACCT
(
  RADACCTID          INTEGER primary key,
  ACCTSESSIONID      VARCHAR2(32) not null,
  ACCTUNIQUEID       VARCHAR2(32),
  USERNAME           VARCHAR2(32) not null,
  REALM              VARCHAR2(30),
  NASIPADDRESS       VARCHAR2(15) not null,
  NASPORTID          NUMBER(12),
  NASPORTTYPE        VARCHAR2(32),
  ACCTSTARTTIME      DATE,
  ACCTSTOPTIME       DATE,
  ACCTSESSIONTIME    NUMBER(12),
  ACCTAUTHENTIC      VARCHAR2(32),
  CONNECTINFO_START  VARCHAR2(32),
  CONNECTINFO_STOP   VARCHAR2(32),
  ACCTINPUTOCTETS    NUMBER(12),
  ACCTOUTPUTOCTETS   NUMBER(12),
  CALLEDSTATIONID    VARCHAR2(50),
  CALLINGSTATIONID   VARCHAR2(50),
  ACCTTERMINATECAUSE VARCHAR2(32),
  SERVICETYPE        VARCHAR2(32),
  FRAMEDPROTOCOL     VARCHAR2(32),
  FRAMEDIPADDRESS    VARCHAR2(15),
  ACCTSTARTDELAY     NUMBER(12),
  ACCTSTOPDELAY      NUMBER(12)
);
create unique index RADACCT_IDX1 on RADACCT (ACCTSESSIONID, USERNAME, ACCTSTARTTIME, ACCTSTOPTIME, NASIPADDRESS, FRAMEDIPADDRESS);
comment on column RADACCT.RADACCTID is 'id号码';
comment on column RADACCT.ACCTSESSIONID is '用户登陆的SessionID';
comment on column RADACCT.ACCTUNIQUEID is '记帐唯一ID';
comment on column RADACCT.USERNAME is '用户名';
comment on column RADACCT.REALM is '域';
comment on column RADACCT.NASIPADDRESS is 'NAS 的IP 地址';
comment on column RADACCT.NASPORTID is 'NAS端口号';
comment on column RADACCT.NASPORTTYPE is '设备的端口类型';
comment on column RADACCT.ACCTSTARTTIME is '拨号开始时间';
comment on column RADACCT.ACCTSTOPTIME is '拨号结束时间';
comment on column RADACCT.ACCTSESSIONTIME is '用户使用服务的时长';
comment on column RADACCT.ACCTAUTHENTIC is '计费认证方式';
comment on column RADACCT.CONNECTINFO_START is '连接开始';
comment on column RADACCT.CONNECTINFO_STOP is '连接结束';
comment on column RADACCT.ACCTINPUTOCTETS is '下载字节数量    单位: byte';
comment on column RADACCT.ACCTOUTPUTOCTETS is '上传字节数量     单位: byte';
comment on column RADACCT.CALLEDSTATIONID is '曾经登陆用户的网卡MAC地址';
comment on column RADACCT.CALLINGSTATIONID is '当前在线用户网卡MAC地址';
comment on column RADACCT.ACCTTERMINATECAUSE is 'ADSL连接结束原因 ';
comment on column RADACCT.SERVICETYPE is '服务类型';
comment on column RADACCT.FRAMEDPROTOCOL is '用户接入协议';
comment on column RADACCT.FRAMEDIPADDRESS is '用户IP地址';
comment on column RADACCT.ACCTSTARTDELAY is '接入延迟             单位:second';
comment on column RADACCT.ACCTSTOPDELAY is '断开延迟             单位:second';

create table RADCHECK
(
  ID        INTEGER primary key,
  USERNAME  VARCHAR2(30) not null,
  ATTRIBUTE VARCHAR2(30),
  OP        VARCHAR2(2) not null,
  VALUE     VARCHAR2(40),
  USERSID    NUMBER references USERS(ID),
  PRODUCTSID NUMBER references PRODUCTS(ID)
);
insert into radcheck values(11,'briup1','User-Password',':=','briup1',null,null);
insert into radcheck values(12,'briup','User-Password',':=','briup',null,null);

create table RADREPLY
(
  ID        INTEGER primary key,
  USERNAME  VARCHAR2(30) not null,
  ATTRIBUTE VARCHAR2(30),
  OP        VARCHAR2(2) not null,
  VALUE     VARCHAR2(40)
);


create table HV
(
	id NUMBER primary key,
	highvalue number
);

create sequence ADMINS_SEQ start with 1000;
create sequence USERS_SEQ start with 1000;
create sequence ROLES_SEQ start with 1000;
create sequence PRODUCTS_SEQ start with 1000;
create sequence RADCHECK_SEQ start with 1000;
create sequence RADREPLY_SEQ start with 1000;
CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;

drop sequence USERDAY_SEQ;
drop sequence USERMONTH_SEQ;
drop sequence USERYEAR_SEQ;
drop sequence NASDAY_SEQ;
drop sequence NASMONTH_SEQ;
drop sequence NASYEAR_SEQ;
create sequence USERDAY_SEQ start with 1000;
create sequence USERMONTH_SEQ start with 1000;
create sequence USERYEAR_SEQ start with 1000;
create sequence NASDAY_SEQ start with 1000;
create sequence NASMONTH_SEQ start with 1000;
create sequence NASYEAR_SEQ start with 1000;