-

设备巡检操作系统 本程序是专门为某电厂开发的设备巡检系统

------------------------------------------------------
-- Export file for user SBDJ                        --
-- Created by Administrator on 2005-03-11, 21:39:21 --
------------------------------------------------------

spool 05-3-11.log

prompt
prompt Creating table T_BDATA
prompt ======================
prompt
create table T_BDATA
(
  PERNO    VARCHAR2(4) not null,
  CHECKNUM NUMBER not null,
  MSRNO    NUMBER not null,
  MSRVALUE VARCHAR2(20),
  UDATE    DATE,
  CDATE    DATE,
  CBZ      VARCHAR2(50)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table T_CHECKPT
prompt ========================
prompt
create table T_CHECKPT
(
  EUPETNNO VARCHAR2(4),
  NPARTNO  VARCHAR2(4),
  MSRNNO   VARCHAR2(4),
  MSRNO    NUMBER not null,
  MSRITEM  VARCHAR2(20),
  DWV      NUMBER,
  UWV      NUMBER,
  IB       VARCHAR2(15),
  PCDATE   DATE,
  PCLINE   NUMBER,
  MSRNAME  VARCHAR2(30)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_CHECKPT
  add constraint MSRNO primary key (MSRNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );

prompt
prompt Creating table T_DEPT
prompt =====================
prompt
create table T_DEPT
(
  DEPTNO   VARCHAR2(2) not null,
  DEPTNAME VARCHAR2(10)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_DEPT
  add constraint DEPTNO primary key (DEPTNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );

prompt
prompt Creating table T_EMPLOYEE
prompt =========================
prompt
create table T_EMPLOYEE
(
  NO         VARCHAR2(4) not null,
  USERNAME   VARCHAR2(20),
  PASSWORD   VARCHAR2(20),
  PURVIEW    NUMBER,
  DEPTNO     VARCHAR2(2),
  PREFESSION VARCHAR2(20),
  REMARK     VARCHAR2(50),
  PLANNO     VARCHAR2(2),
  PURVIEW2   VARCHAR2(10)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_EMPLOYEE
  add constraint NO primary key (NO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );
insert into t_Employee (no,Username,Password,Purview,Purview2) values ('0001','Admin','123',1,'高级用户');
prompt
prompt Creating table T_EUPET
prompt ======================
prompt
create table T_EUPET
(
  EUPETNNO  VARCHAR2(4) not null,
  EUPETNO   VARCHAR2(20),
  EUPETNAME VARCHAR2(30)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_EUPET
  add constraint EUPETNNO primary key (EUPETNNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );

prompt
prompt Creating table T_NCPT
prompt =====================
prompt
create table T_NCPT
(
  MSRNO    NUMBER not null,
  PERNO    VARCHAR2(4) not null,
  CHECKNUM NUMBER not null,
  UDATE    DATE,
  MEMO     VARCHAR2(100)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table T_NCPT
  add constraint KNCPT primary key (MSRNO, CHECKNUM, PERNO)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents 4096
    pctincrease 0
  );

prompt
prompt Creating table T_PART
prompt =====================
prompt
create table T_PART
(
  NPARTNO  VARCHAR2(4) not null,
  PARTNAME VARCHAR2(50) not null,
  EUPETNNO VARCHAR2(4) not null,
  IB       VARCHAR2(20),
  PARTNO   VARCHAR2(20),
  IBMEMO   VARCHAR2(20)
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
comment on column T_PART.NPARTNO
  is '部件内码';
comment on column T_PART.PARTNAME
  is '部件名';
comment on column T_PART.EUPETNNO
  is '机组';

prompt
prompt Creating table T_PERPLAN
prompt ========================
prompt
create table T_PERPLAN
(
  MSRNO      NUMBER not null,
  PLANNO     VARCHAR2(2) not null,
  PERDATE    DATE,
  SPECIALTY  VARCHAR2(2) not null,
  PLANSORTID NUMBER
)
tablespace SBDJ_DATA
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating view ABC
prompt =================
prompt
create or replace view abc as
select T_employee.No 人员编号,T_employee.Username 用户名称,T_employee.Purview 用户权限,T_employee.Purview2 用户权限2,T_employee.Deptno 部门编号,T_dept.Deptname 部门名称,T_employee.prefession 专业 ,T_employee.remark 备注
    from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/

prompt
prompt Creating view VIEW_EMPLOYEE
prompt ===========================
prompt
create or replace view view_employee as
select T_employee.*,
T_dept.Deptname
    from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/

prompt
prompt Creating view VIEW_SBDJ_PART1
prompt =============================
prompt
create or replace view view_sbdj_part1 as
select
T_part.Partno,T_part.Partname,T_part.Ib,T_part.Ibmemo,
tc.npartno,tc.eupetnno,tc.msrnno,tc.msrno,tc.msritem,tc.dwv,tc.uwv,tc.pcdate,tc.pcline,tc.msrname
    from T_part,T_checkpt tc
   where tc.npartno=T_part.npartno and tc.Eupetnno=T_part.Eupetnno
/

prompt
prompt Creating view VIEW_SBDJ
prompt =======================
prompt
create or replace view view_sbdj as
select t_Eupet.Eupetno,t_Eupet.Eupetname,
    view_sbdj_part1.*
    from t_Eupet,view_sbdj_part1
   where t_Eupet.Eupetnno=view_sbdj_part1.Eupetnno
/

prompt
prompt Creating view VIEW_SBDJ_PLANDATE
prompt ================================
prompt
create or replace view view_sbdj_plandate as
select e.no,e.username,e.deptno,e.prefession,e.remark,
e.planno,p.msrno,p.perdate,p.plansortid
 from t_Employee e,t_Perplan p where e.deptno=p.specialty and e.planno=p.planno
/

prompt
prompt Creating view VIEW_NCPT
prompt =======================
prompt
create or replace view view_ncpt as
select c.eupetno,c.eupetname,c.partno,c.partname,a.username,
    c.msrno,c.msrname,c.msritem,b.perno,b.checknum,b.udate,b.memo from view_sbdj_plandate a,
    T_ncpt b,view_sbdj c where a.msrno=b.msrno
    and c.msrno=b.msrno and b.perno=a.no
/

prompt
prompt Creating view VIEW_SBDJVALUE
prompt ============================
prompt
create or replace view view_sbdjvalue as
select a.*,b.perno,
         b.msrvalue,b.udate,b.cdate,
         b.cbz
    from view_sbdj a,T_bdata b
   where a.msrno=b.msrno
/

prompt
prompt Creating view VIEW_NEWSBDJVALUE_PER
prompt ===================================
prompt
create or replace view view_newsbdjvalue_per as
select  a.*,
"EUPETNO","EUPETNAME","PARTNO","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from view_employee a,view_sbdjvalue b where a.no=b.perno
--"人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注"
/

prompt
prompt Creating view VIEW_PERPLAN
prompt ==========================
prompt
create or replace view view_perplan as
select t_employee.no,t_employee.planno,T_perplan.Msrno
from T_perplan,t_Employee where T_perplan.Planno=t_Employee.Planno
and T_perplan.Specialty=t_Employee.Deptno
/

prompt
prompt Creating view VIEW_PERCURPLAN
prompt =============================
prompt
create or replace view view_percurplan as
select view_perplan.no,view_perplan.planno,view_sbdj.* from view_perplan,view_sbdj
   where view_sbdj.msrno=view_perplan.Msrno
/

prompt
prompt Creating view VIEW_SBDJVALUE_PER
prompt ================================
prompt
create or replace view view_sbdjvalue_per as
select "人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注",
"EUPETNO","EUPETNAME","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from abc a,view_sbdjvalue b where a.人员编号=b.perno
/

prompt
prompt Creating view VIEW_SBDJVALUE_PERADDPLAN
prompt =======================================
prompt
create or replace view view_sbdjvalue_peraddplan as
select b.username,b.remark,b.perdate,a.*
 from view_sbdjvalue_per a,view_sbdj_plandate b where a.msrno=b.msrno and a.人员编号=b.no
/


spool off