防办数据结构SQL语句

1、*表空间CODE用户CUSER/*/CREATE TABLE CUSER.purview(pid number(16,0) not null,loginid Varchar(10),password Varchar(16),property Varchar(1),storeid Varchar(6),11super varchar(1),username Varchar(10),rid Varchar(6),constraint pk_purview PRIMARY KEY ( pid ) TABLESPACE CODE ;CREATE TABLE CUSER.REGION(rid varch

2、ar(6) not null,sname varchar(50),allame varchar(50),serial number(4,0),constraint pk_REGION PRIMARY KEY ( rid )TABLESPACE CODE ;CREATE TABLE CUSER.mstorage (Sid varchar(6) not null,owner varchar(2),constraint ckc_owner_store check (owner in (01,02,03,04),serial number(4,0),name varchar(48),simple va

3、rchar(14),rid varchar(6),constraint pk_mstorage PRIMARY KEY(sid)TABLESPACE CODE;CREATE TABLE CUSER.storeinfo(sid varchar(6) not null,area number(16, 4),picture varchar(16),dutyman varchar(16),tel varchar(12),postal varchar(6),email varchar(30),facsimile varchar(12),summarize varchar(36),note varchar

4、(255),PICNAME varchar(100),constraint pk_storeinfo PRIMARY KEY ( sid ) TABLESPACE CODE;CREATE TABLE CUSER.msort(midvarchar(4) not null,namevarchar(48),sidvarchar(4),measure varchar(12),serial number(4,0),convvarchar(12),convalue float,supnamevarchar(4),termnumber(3,0),constraint pk_msort PRIMARY KEY

5、 ( mid ) )TABLESPACE CODE;CREATE TABLE CUSER.specs(sid varchar(12) not null,name varchar(16),mid varchar(12),serial number(4,0),constraint pk_specs PRIMARY KEY ( sid ) ) TABLESPACE CODE;CREATE TABLE CUSER.module(midvarchar(10) not null,namevarchar(30),flag varchar(5),constraint pk_module PRIMARY KEY

6、 ( mid ) TABLESPACE CODE;CREATE TABLE CUSER.oitem(PID NUMBER(16) not null,MID VARCHAR2(10) not null,pbrowse varchar(1) default 0,psearch varchar(1) default 0,padd varchar(1) default 0,pmod varchar(1) default 0,pdel varchar(1) default 0,paud varchar(1) default 0,pprint varchar(1) default 0, pfile var

7、char(1) default 0, constraint pk_oitem PRIMARY KEY ( pid, mid)TABLESPACE CODE;CREATE TABLE CUSER.oitemsort(osidvarchar(16) not null,osortvarchar(30),constraint pk_oitemsort PRIMARY KEY ( osid ) ) TABLESPACE CODE;CREATE TABLE CUSER.DEPARTMENT (D_ID1 NUMBER(16) NOT NULL,D_ID2 NUMBER(16) NOT NULL,O_ID

8、VARCHAR2(24) NOT NULL,NAME VARCHAR2(24),TEL VARCHAR2(16),ZIP VARCHAR2(6),ADDR VARCHAR2(18),WA VARCHAR2(12),FAX VARCHAR2(12),EMAIL VARCHAR2(30),MEM VARCHAR2(255),CONSTRAINT PK_DEPARTMENT PRIMARY KEY(D_ID1, D_ID2) ) TABLESPACE CODE;CREATE TABLE CUSER.office (O_ID int not null,name varchar(48) null,add

9、r varchar(48) null,zip varchar(6) null,RID varchar(6) null,tel varchar(48) null,wav varchar(16) null,fax varchar(16) null,email varchar(48) null,mem varchar(255) null,constraint pk_office PRIMARY KEY (O_ID) )TABLESPACE CODE;CREATE TABLE CUSER.person(num int not null,O_ID int not null,D_ID1 int ,D_ID

10、2 int ,name varchar(24) ,job varchar(12) ,addr varchar(48) ,mobile varchar(16) ,call varchar(16) ,wav varchar(16) ,zip varchar(6) ,offtel varchar(16) ,famtel varchar(16) ,fax varchar(16) null,email varchar(16) ,mem varchar(255) ,constraint pk_person PRIMARY KEY (num ) )TABLESPACE CODE;CREATE TABLE C

11、USER.criteriatype ( type int not null,typename varchar(14) not null, constraint pk_criteriatype PRIMARY KEY (type ) )TABLESPACE CODE;CREATE TABLE CUSER.criteria (id int not null,name varchar(48) null,type int null,date1 date null,date2 date null,content varchar(4000) null,note varchar(100) null,cons

12、traint pk_criteria PRIMARY KEY ( id ) )TABLESPACE CODE;CREATE TABLE CUSER.onduty(odidvarchar(16) not null,begintimedate,endtimedate,personidnumber(8,0),notevarchar(255),constraint pk_onduty PRIMARY KEY ( odid ) )TABLESPACE CODE;CREATE TABLE CUSER.dynamic(did varchar(16) not null,captionvarchar(16),c

13、ontentvarchar(255),ondate date,userrid varchar(6),constraint pk_dynamic PRIMARY KEY (did )TABLESPACE CODE;CREATE TABLE CUSER.moutlaysort( midvarchar(16) not null,namevarchar(30),constraint pk_moutlaysort PRIMARY KEY ( mid ) )TABLESPACE CODE;create table cuser.treez (zcode varchar(4) not null,zstr va

14、rchar(20) null,constraint pk_treez PRIMARY KEY ( zcode ) )TABLESPACE CODE;create table cuser.treek (kcode varchar (4) not null,kstr varchar(20) null,constraint pk_treek PRIMARY KEY ( kcode ) )TABLESPACE CODE;create table cuser.article (fcode varchar(10) not null,name varchar(150) null,zcode varchar(

15、4) null,kcode varchar(4) null,flag varchar(1) null,fbdate date null,memo varchar(80) null,constraint pk_article PRIMARY KEY ( fcode ) TABLESPACE CODE;，*物资：表空间 MATERIAL用户 MUSER /*/CREATE TABLE MUSER.material(MNOnumber (18, 0) not null,SIDvarchar(6),MIDvarchar(4),M1varchar(4),M2varchar(14),M3date,M4va

16、rchar(2),M5varchar(2),M6int,M7varchar(8),M8varchar(40),M9date,M10number(9, 2),M11number(11, 2),M12number(11, 2),M14varchar(16),M15varchar(255),M16date,M17varchar(12),ORIDvarchar(6),IRIDvarchar(6),M161date,M171varchar(12),M31varchar(255),M51varchar(255),M18varchar(1),M19number,M20number(18,0),iridvar

17、char(6),constraint pk_material PRIMARY KEY (MNO)TABLESPACE MATERIAL;CREATE TABLE MUSER.MDETAIL (MNO NUMBER(18) NOT NULL,MID NUMBER(10) NOT NULL,AMOUNT NUMBER(10),TYPE VARCHAR2(1),CONSTRAINT PK_DETAIL PRIMARY KEY(MNO, MID)TABLESPACE MATERIAL;CREATE TABLE MUSER.danger(did number(18, 0) ,rid varchar(6)

18、,sid varchar(6) ,detail varchar(255),declarer varchar(8) ,dcdate date ,auditing varchar(8) ,addate date ,adsign varchar(1) ,suggestion varchar(255) ,occurdate date,note varchar(255),fileno varchar(16),CONSTRAINT PK_danger PRIMARY KEY(did)TABLESPACE MATERIAL;CREATE TABLE MUSER.feedback (fid number (1

19、8, 0),rid varchar(6),sid varchar(6),docno varchar(16),mid varchar(4),num Number,reporter varchar(12),rpdate date,flag varchar(1),note varchar(255),CONSTRAINT PK_feedback PRIMARY KEY(fid)TABLESPACE MATERIAL;CREATE TABLE MUSER.moutlay(midnumber (18, 0) not null,filenum varchar(16),unitid varchar(6),md

20、ate date,itemidnumber (18, 0),useid varchar(16),osort varchar(1),fee number(12, 2),flag varchar(1),remark varchar(255),userrid varchar(6),constraint pk_moutlay PRIMARY KEY (mid)TABLESPACE MATERIAL;CREATE TABLE MUSER.stock(sidnumber (18, 0) not null,applytimedate,applydepartment varchar(6),stockidvar

21、char(1),msortidvarchar(4),amountint,measure varchar(8), pricenumber(9, 2),totalpricenumber(11,2),stockervarchar(12),stocktime date, auditingvarchar(12),auditingtimedate,auditingsignvarchar(1),auditingmind varchar(255), note varchar(255), mspecsvarchar(12),userrid varchar(6), constraint pk_stock PRIM

22、ARY KEY (sid)TABLESPACE MATERIAL;CREATE TABLE MUSER.repertory(ridnumber (18, 0) not null,areaidvarchar(6),meterialid varchar(6),amount int,dutyman varchar(12),signaturetime date,distributetime date,auditing varchar(12),auditingtime date,auditingsign varchar(1),auditingmind varchar(255),notevarchar(2

23、55),userrid varchar(6), constraint pk_repertory PRIMARY KEY (rid) )TABLESPACE MATERIAL;CREATE TABLE MUSER.factory( fid varchar(16) not null, name varchar(24), addressvarchar(24),mainproduct varchar(24), enrolfund number(16, 4), believevarchar(24),directorvarchar(8),linkmainvarchar(8),tel varchar(12)

24、, fax varchar(12), email varchar(24), userrid varchar(6), constraint pk_factory PRIMARY KEY ( fid ) ) TABLESPACE MATERIAL;CREATE TABLE MUSER.product( pidvarchar(16) not null,namevarchar(4),specsid varchar(12), pricenumber(9, 2),performance varchar(30), factoryname varchar(24), picture varchar(16), u

25、serrid varchar(6),constraint pk_product PRIMARY KEY ( pid )*/)TABLESPACE MATERIAL;*经费：表空间 OUTLAY用户OUSER /*/CREATE TABLE OUSER.fxjf_files(f_id numeric (18,0) not null ,dscd varchar2(8) not null,dt date ,f_no varchar2 (40) not null,f_nm varchar2 (100) null,f_sort numeric (1,0) not null,rpun1 varchar2

26、(40) ,rpun2 varchar2 (40) ,sgis1 varchar2 (10) ,sgis2 varchar2 (10) ,rcun varchar2 (255),text0 varchar(4000) ,rm varchar2 (40) ,rm1 varchar2 (40) ,rm2 varchar2 (40) ,p_no number(18,0),image0 blob ,constraint pk_fxjf_files PRIMARY KEY (f_id)TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_01 (reg_cd varchar

27、2 (8) not null,reg_nm varchar2 (20),sub_cd varchar2 (8) not null,sub_nm varchar2 (20),constraint pk_fxjf_01 PRIMARY KEY ( reg_cd,sub_cd )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_11 (f_no numeric (18,0) not null ,dt date not null,c1 numeric(9, 2),c2 numeric(9, 2) ,c3 numeric(9, 2),c4 numeric(9, 2) n

28、ull,c5 numeric(9, 2) null,c6 numeric(9, 2) null,c7 numeric(9, 2) null,c8 numeric(9, 2) null,c9 date null,sg numeric(1, 0) null,constraint pk_fxjf_11 PRIMARY KEY( f_no)TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_12 (i_no int not null,f_no numeric (18,0) not null,dt date not null,p_dscd varchar2 (8) not

29、 null,p_dt date not null,p_f_no varchar2 (40) not null,p_i_no int not null,l_dscd varchar2 (8) null,uf_no varchar2 (40) null,u_date date null,u_item varchar2 (100) null,u_bankroll numeric(9, 2) null,l_item varchar2 (100) null,i_type numeric(1, 0) null,bankroll numeric(9, 2) null,bankrolltype numeric

30、(1, 0) null,arrived_bankroll numeric(9, 2) null,earthwork numeric(9, 2) null,stonework numeric(9, 2) null,concretework numeric(9, 2) null,completed numeric(1, 0) null,isphoto numeric(1, 0) null,constraint pk_fxjf_12 PRIMARY KEY ( f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_13 (f_no numeric

31、(18,0) not null,dt date not null,i_no int null,c1 numeric(9, 2) null,c2 numeric(9, 2) null,c3 varchar2 (128) null,c4 numeric(9, 2) null,c5 numeric(9, 2) null,c6 varchar2 (128) null,c7 numeric(9, 2) null, c8 numeric(9, 2) null, c9 varchar2 (128) null,c10 numeric(9, 2) null,c11 numeric(9, 2) null,c12

32、varchar2 (128) null,c13 numeric(9, 2) null,c14 numeric(9, 2) null,c15 varchar2 (128) null,sg numeric(1, 0) null,constraint pk_fxjf_13 PRIMARY KEY ( f_no)TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_14 (i_no smallint not null,f_no numeric (18,0) not null,dt date not null,c1 varchar2 (40) null,c2 varchar

33、2 (40) null,c3 varchar2 (40) null,c4 varchar2 (40) null,c5 varchar2 (40) null,c6 varchar2 (40) null,c7 varchar2 (40) null,c8 numeric(6, 0) null,c9 varchar2 (128) null,c10 varchar2 (128) null,c11 numeric(6, 2) null,c12 numeric(6, 2) null,c13 numeric(6, 2) null,c14 numeric(9, 2) null,c15 numeric(9, 2)

34、 null,c16 varchar2 (128) null,sg numeric(1,0) null,constraint pk_fxjf_14 PRIMARY KEY ( f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_15 (i_no smallint not null,f_no numeric (18,0) not null ,dt date not null,c1 varchar2 (40) null,c2 varchar2 (40) null,c3 varchar2 (40) null,c4 varchar2 (40) nul

35、l,c5 varchar2 (40) null,c6 varchar2 (10) null,c7 varchar2 (40) null,c8 varchar2 (40) null,c9 varchar2 (40) null,c10 varchar2 (128) null,c11 varchar2 (128) null,c12 numeric(6, 2) null,c13 numeric(6, 2) null,c14 numeric(6, 2) null,c15 numeric(9, 2) null,c16 numeric(9, 2) null,c17 varchar2 (128) null,s

36、g numeric(1, 0) null,constraint pk_fxjf_15 PRIMARY KEY ( f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.仅jf_16 (i_no int not null,f_no numeric (18,0) not null,dt date not null,c1 varchar2 (40) null,c2 varchar2 (40) null,c3 varchar2 (40) null,c4 varchar2 (40) null,c5 varchar2 (40) null,c6 varchar2 (

37、40) null,c7 varchar2 (80) null,c8 varchar2 (60) null,c9 varchar2 (60) null,c10 varchar2 (60) null,c11 varchar2 (128) null,c12 numeric(6, 2) null,c13 numeric(6, 2) null,c14 numeric(6, 2) null,c15 numeric(9, 2) null,c16 numeric(9, 2) null,c17 varchar2 (128) null,sg numeric(1, 0) null,constraint pk_fxj

38、f_16 PRIMARY KEY ( f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_17 (f_no numeric (18,0) not null,dt date not null,i_no smallint null, c1 varchar2 (128) null, c2 numeric(9, 2) null,c3 numeric(9, 2) null,c4 varchar2 (128) null,c5 numeric(9, 2) null,c6 numeric(9, 2) null,c7 varchar2 (128) null,

39、c8 numeric(9, 2) null,c9 numeric(9, 2) null,c10 varchar2 (128) null,c11 numeric(9, 2) null,c12 numeric(9, 2) null,c13 varchar2 (128) null,c14 numeric(9, 2) null,c15 numeric(9, 2) null,c16 varchar2 (128) null,c17 numeric(9, 2) null,c18 numeric(9, 2) null,c19 varchar2 (128) null,c20 numeric(9, 2) null

40、,c21 numeric(9, 2) null,c22 varchar2 (128) null,c23 numeric(9, 2) null,c24 numeric(9, 2) null,sg numeric(1, 0) null,constraint pk_fxjf_17 PRIMARY KEY ( f_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_03 (i_no int not null,f_no numeric (18,0) not null ,dt date null, p_nm varchar2 (255) null,cl decima

41、l(10, 3) null,c2 decimal(10, 3) null,p_sort int null,rm varchar2 (40) null,constraint pk_fxjf_03 PRIMARY KEY (f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_23 (f_no numeric (18,0) not null ,dt date not null,i_no int null,c1 int null,c2 numeric(7, 2) null,c3 numeric(12, 2) null,c4 numeric(7, 2

42、) null,c5 numeric(9, 2) null,c6 clob null,sg numeric (1,0) null,constraint pk_fxjf_23 PRIMARY KEY ( f_no) TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_20 (i_no int not null,f_no numeric (18,0) not null,dt date not null,l_dscd varchar2 (8) null,uf_no varchar2 (40) null,u_date date null,u_item varchar2 (

43、100) null, u_bankroll numeric(9, 2) null,l_item varchar2 (100) null,i_type numeric (1,0) null,bankroll numeric(9, 2) null,bankrolltype numeric (1,0) null,sg numeric (1,0) null,constraint pk_fxjf_20 PRIMARY KEY (f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_19 (p_no int not null,i_no int not n

44、ull ,dt date null,sub_cd varchar2 (8) not null,p_nm varchar2 (255) null,c1 decimal(10, 3) null,c2 decimal(10, 3) null,c3 decimal(10, 3) null,c4 decimal(10, 3) null,c5 decimal(10, 3) null,c6 decimal(10, 3) null,c7 decimal(10, 3) null,c8 varchar2 (100) null,p_sort numeric(1, 0) null,rm varchar2 (40) n

45、ull,rm1 varchar2 (40) null,rm2 varchar(40) null,rm3 varchar2 (40) null,constraint pk_fxjf_19 PRIMARY KEY ( p_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.仅jf_20_1(i_no int not null,f_no numeric (18,0) not null,dt date not null,l_dscd varchar2 (8) null,uf_no varchar2 (40) null,u_date date null,u_ite

46、m varchar2 (100) null,u_bankroll numeric(9, 2) null,l_item varchar2 (100) null,i_type numeric (1,0) null,bankroll numeric(9, 2) null,bankrolltype numeric (1,0) null,sg numeric (1,0) null,constraint pk_fxjf_20_1 PRIMARY KEY ( f_no,i_no )TABLESPACE OUTLAY;CREATE TABLE OUSER.fxjf_04 (f_no numeric (18,0) not null,dt date null,c1 decimal(10, 3) null,c2 decimal(10, 3) null,c3 decimal(10, 0) null,c4 decimal(10, 0) null,c5 decimal(10, 3) null,c6 decimal(10, 3) null,c7 decimal(10, 3) null,c8 decimal(10, 0) null,c9 dec