pc-new.sql

unix 下用pro*c tuxedo 开发的东西

/* SQL Product = ORACLE 8 */
/* Environment Indexes = CLUSTERED unique indexes */
/* Environment Drop Tables = Generate DROP */
/* Environment Drop Indices = Generate DROP */
/* Environment Drop Constraints = Generate DROP */
/* Environment Drop Procedures = Generate DROP */
/* Environment Drop Sequences = Generate DROP */
/* Environment Drop Synonyms = Generate DROP */
/* Environment Drop Views = Generate DROP */
CREATE DATABASE ProvinceModel
   	NOARCHIVELOG;
DROP INDEX T_CZRZ_X1;
DROP INDEX T_FSBKJHML_X2;
DROP INDEX T_FSBKJHMLNR_X3;
DROP INDEX T_FSBSFXML_X4;
DROP INDEX T_FSBSFXMLNR_X5;
DROP INDEX T_FSGH_X6;
DROP INDEX T_FSPDYSSJ_X7;
DROP INDEX T_FSPDYSSM_X8;
DROP INDEX T_FSTJBB_X9;
DROP INDEX T_FSYXSJ_X10;
DROP INDEX T_FSZDYSSJ_X11;
DROP INDEX T_FSZDYSSM_X12;
DROP INDEX T_FSZFJB_X13;
DROP INDEX T_FSZFJBNR_X14;
DROP INDEX T_HZ_X15;
DROP INDEX T_HZ_X16;
DROP INDEX T_JSBKHZML_X17;
DROP INDEX T_JSBKHZMLNR_X18;
DROP INDEX T_JSGH_X19;
DROP INDEX T_JSPDYSSJ_X20;
DROP INDEX T_JSPDYSSM_X21;
DROP INDEX T_JSPDYSSM_X22;
DROP INDEX T_JSRZ_X23;
DROP INDEX T_JSYXSJ_X24;
DROP INDEX T_JSZDYSSJ_X25;
DROP INDEX T_JSZDYSSM_X26;
DROP INDEX T_JSZDYSSM_X27;
DROP INDEX T_JSZFJB_X28;
DROP INDEX T_JSZFJBNR_X29;
DROP INDEX T_SNMLFB_X30;
DROP INDEX T_SNMLFB_X31;
DROP INDEX T_SNMLFB_X32;
DROP INDEX T_ZCFSSJ_X33;
DROP INDEX T_ZCJSSJ_X34;
DROP INDEX T_ZFJ_X35;
DROP INDEX T_CZRZ_PK;
ALTER TABLE T_CZRZ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_CZRZ CASCADE CONSTRAINTS;
DROP INDEX T_CZYB_PK;
ALTER TABLE T_CZYB DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_CZYB CASCADE CONSTRAINTS;
DROP INDEX T_FBKJ_PK;
ALTER TABLE T_FBKJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FBKJ CASCADE CONSTRAINTS;
DROP INDEX T_FSBKJHML_PK;
ALTER TABLE T_FSBKJHML DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSBKJHML CASCADE CONSTRAINTS;
DROP INDEX T_FSBKJHMLNR_PK;
ALTER TABLE T_FSBKJHMLNR DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSBKJHMLNR CASCADE CONSTRAINTS;
DROP INDEX T_FSBSFXML_PK;
ALTER TABLE T_FSBSFXML DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSBSFXML CASCADE CONSTRAINTS;
DROP INDEX T_FSBSFXMLNR_PK;
ALTER TABLE T_FSBSFXMLNR DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSBSFXMLNR CASCADE CONSTRAINTS;
DROP INDEX T_FSGH_PK;
ALTER TABLE T_FSGH DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSGH CASCADE CONSTRAINTS;
DROP INDEX T_FSPDYSSJ_PK;
ALTER TABLE T_FSPDYSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSPDYSSJ CASCADE CONSTRAINTS;
DROP INDEX T_FSPDYSSM_PK;
ALTER TABLE T_FSPDYSSM DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSPDYSSM CASCADE CONSTRAINTS;
DROP INDEX T_FSRZ_PK;
ALTER TABLE T_FSRZ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSRZ CASCADE CONSTRAINTS;
DROP INDEX T_FSTJBB_PK;
ALTER TABLE T_FSTJBB DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSTJBB CASCADE CONSTRAINTS;
DROP INDEX T_FSYXSJ_PK;
ALTER TABLE T_FSYXSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSYXSJ CASCADE CONSTRAINTS;
DROP INDEX T_FSZDYSSJ_PK;
ALTER TABLE T_FSZDYSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSZDYSSJ CASCADE CONSTRAINTS;
DROP INDEX T_FSZDYSSM_PK;
ALTER TABLE T_FSZDYSSM DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSZDYSSM CASCADE CONSTRAINTS;
DROP INDEX T_FSZFJB_PK;
ALTER TABLE T_FSZFJB DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSZFJB CASCADE CONSTRAINTS;
DROP INDEX T_FSZFJBNR_PK;
ALTER TABLE T_FSZFJBNR DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_FSZFJBNR CASCADE CONSTRAINTS;
DROP INDEX T_HZ_PK;
ALTER TABLE T_HZ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_HZ CASCADE CONSTRAINTS;
DROP INDEX T_JSBKHZML_PK;
ALTER TABLE T_JSBKHZML DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSBKHZML CASCADE CONSTRAINTS;
DROP INDEX T_JSBKHZMLNR_PK;
ALTER TABLE T_JSBKHZMLNR DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSBKHZMLNR CASCADE CONSTRAINTS;
DROP INDEX T_JSGH_PK;
ALTER TABLE T_JSGH DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSGH CASCADE CONSTRAINTS;
DROP INDEX T_JSPDYSSJ_PK;
ALTER TABLE T_JSPDYSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSPDYSSJ CASCADE CONSTRAINTS;
DROP INDEX T_JSPDYSSM_PK;
ALTER TABLE T_JSPDYSSM DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSPDYSSM CASCADE CONSTRAINTS;
DROP INDEX T_JSRZ_PK;
ALTER TABLE T_JSRZ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSRZ CASCADE CONSTRAINTS;
DROP INDEX T_JSYXSJ_PK;
ALTER TABLE T_JSYXSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSYXSJ CASCADE CONSTRAINTS;
DROP INDEX T_JSZDYSSJ_PK;
ALTER TABLE T_JSZDYSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSZDYSSJ CASCADE CONSTRAINTS;
DROP INDEX T_JSZDYSSM_PK;
ALTER TABLE T_JSZDYSSM DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSZDYSSM CASCADE CONSTRAINTS;
DROP INDEX T_JSZFJB_PK;
ALTER TABLE T_JSZFJB DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSZFJB CASCADE CONSTRAINTS;
DROP INDEX T_JSZFJBNR_PK;
ALTER TABLE T_JSZFJBNR DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_JSZFJBNR CASCADE CONSTRAINTS;
DROP INDEX T_SJBH_PK;
ALTER TABLE T_SJBH DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_SJBH CASCADE CONSTRAINTS;
DROP INDEX T_SNML_PK;
ALTER TABLE T_SNML DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_SNML CASCADE CONSTRAINTS;
DROP INDEX T_SNMLFB_PK;
ALTER TABLE T_SNMLFB DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_SNMLFB CASCADE CONSTRAINTS;
DROP INDEX T_ZCFSSJ_PK;
ALTER TABLE T_ZCFSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_ZCFSSJ CASCADE CONSTRAINTS;
DROP INDEX T_ZCJSSJ_PK;
ALTER TABLE T_ZCJSSJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_ZCJSSJ CASCADE CONSTRAINTS;
DROP INDEX T_ZFJ_PK;
ALTER TABLE T_ZFJ DROP
   PRIMARY KEY CASCADE;
DROP TABLE T_ZFJ CASCADE CONSTRAINTS;
DROP TABLE T_BSBH CASCADE CONSTRAINTS;

 drop view v_fszdyssm;
 drop view v_fsbkjhml;
 drop view v_fsyxsj;
 drop view v_fsbsfxml;
 drop view v_fstjbb;
 drop view v_fsgh;
 drop view v_fszfjb;
 drop view v_fspdyssm;
 drop view v_fsghcx;
 drop view v_jsghcx;
 drop view v_checkjsfspd;
 drop view v_checkjsfszd;
 drop table t_zcghnr;
 drop table t_tempysflag;
 drop table t_fbkj_wjjs;

DROP SEQUENCE S_FSXH;

CREATE SEQUENCE S_FSXH
	INCREMENT BY 1
	START WITH 1
	MAXVALUE 9999999999
	MINVALUE 1
	CYCLE
	CACHE 3;

DROP SEQUENCE yswjxh;
DROP SEQUENCE S_FBKJ_WJBH;

CREATE SEQUENCE yswjxh
	INCREMENT BY 1
	START WITH 1
	MAXVALUE 999
	MINVALUE 1
	CYCLE
	CACHE 3;

CREATE SEQUENCE S_FBKJ_WJBH
	INCREMENT BY 1
	START WITH 1
	MAXVALUE 999
	MINVALUE 1
	CYCLE
	CACHE 3;



CREATE TABLE T_BSBH(
	     BSBH					   VARCHAR2(2) NOT NULL,
	     BSMC					   VARCHAR2(8) NOT NULL);
CREATE TABLE T_CZRZ(
          CZQSSJ                                  DATE NOT NULL,
          CZYH                                    NUMBER(2) NOT NULL,
          CZZZSJ                                  DATE NOT NULL,
	  CZYM					  VARCHAR2(20),
          QX                                      CHARACTER(1));
CREATE UNIQUE INDEX T_CZRZ_PK ON T_CZRZ(CZQSSJ);
ALTER TABLE T_CZRZ ADD (CONSTRAINT T_CZRZ_PK
    PRIMARY KEY (CZQSSJ));
CREATE TABLE T_CZYB(
          CZYH                                    NUMBER(2) NOT NULL,
          CZYM                                    VARCHAR2(20) NOT NULL,
          KL                                      VARCHAR2(16) NOT NULL,
          QX                                      CHARACTER(1) NOT NULL);
CREATE UNIQUE INDEX T_CZYB_PK ON T_CZYB(CZYH);
ALTER TABLE T_CZYB ADD (CONSTRAINT T_CZYB_PK
    PRIMARY KEY (CZYH));
CREATE TABLE T_FBKJ(
          FBKJBH                                  VARCHAR2(2) NOT NULL,
          FBKJMC                                  VARCHAR2(16) ,
          JSFS                                    VARCHAR2(1) NOT NULL);
CREATE UNIQUE INDEX T_FBKJ_PK ON T_FBKJ(FBKJBH);
ALTER TABLE T_FBKJ ADD (CONSTRAINT T_FBKJ_PK
    PRIMARY KEY (FBKJBH));
CREATE TABLE T_FSBKJHML(
          NF                                      NUMBER(4) NOT NULL,
          FSXH                                    NUMBER(10) NOT NULL);
CREATE UNIQUE INDEX T_FSBKJHML_PK ON T_FSBKJHML(FSXH);
ALTER TABLE T_FSBKJHML ADD (CONSTRAINT T_FSBKJHML_PK
    PRIMARY KEY (FSXH));
CREATE TABLE T_FSBKJHMLNR(
          BKDH                                    VARCHAR2(7) NOT NULL,
          BKMC                                    VARCHAR2(40) ,
          FBKJMC                                  VARCHAR2(16) ,
          CBRQ                                    VARCHAR2(10) ,
          DANJ                                    NUMBER(6,2) ,
          YJ                                      DECIMAL(6,2) ,
          JJ                                      DECIMAL(6,2) ,
          BNJ                                     DECIMAL(7,2) ,
          NJ                                      DECIMAL(7,2) ,
          HZDDJDRQ                                VARCHAR2(24) ,
          FXFW                                    VARCHAR2(4) ,
          KQ                                      VARCHAR2(6) ,
          TYKH                                    VARCHAR2(12) ,
          JB                                      VARCHAR2(6) ,
          PC                                      NUMBER(2) ,
          KZ                                      NUMBER(2) ,
          BYM                                     NUMBER(4) ,
          XYF                                     CHARACTER(1) ,
          BBDYFL                                  NUMBER(5,2),
          WBDYFL                                  NUMBER(5,2),
          BBLSFL                                  NUMBER(5,2),
          WBLSFL                                  NUMBER(5,2),
          BZ                                      VARCHAR2(160),
          FSXH                                    NUMBER(10) NOT NULL);
CREATE UNIQUE INDEX T_FSBKJHMLNR_PK ON T_FSBKJHMLNR(BKDH,FSXH);
ALTER TABLE T_FSBKJHMLNR ADD (CONSTRAINT T_FSBKJHMLNR_PK
    PRIMARY KEY (BKDH,FSXH));
CREATE TABLE T_FSBSFXML(
          NF                                      NUMBER(4) NOT NULL,
          FSXH                                    NUMBER(10) NOT NULL);
CREATE UNIQUE INDEX T_FSBSFXML_PK ON T_FSBSFXML(FSXH);
ALTER TABLE T_FSBSFXML ADD (CONSTRAINT T_FSBSFXML_PK
    PRIMARY KEY (FSXH));
CREATE TABLE T_FSBSFXMLNR(
          BKDH                                    VARCHAR2(7) NOT NULL,
          BKMC                                    VARCHAR2(40) ,
          FBKJMC                                  VARCHAR2(16) ,
          CBRQ                                    VARCHAR2(10) ,
          DANJ                                    NUMBER(6,2) ,
          YJ                                      DECIMAL(6,2) ,
          JJ                                      DECIMAL(6,2) ,
          BNJ                                     DECIMAL(7,2) ,
          NJ                                      DECIMAL(7,2) ,
          HZDDJDRQ                                VARCHAR2(24) ,
          FXFW                                    VARCHAR2(4) ,
          KQ                                      VARCHAR2(6) ,
          TYKH                                    VARCHAR2(12) ,
          JB                                      VARCHAR2(6) ,
          PC                                      NUMBER(2) ,
          KZ                                      NUMBER(2) ,
          BYM                                     NUMBER(4) ,
          XYF                                     CHARACTER(1) ,
          BBDYFL                                  NUMBER(5,2),
          WBDYFL                                  NUMBER(5,2),
          BBLSFL                                  NUMBER(5,2),
          WBLSFL                                  NUMBER(5,2),
          BZ                                      VARCHAR2(160),
          FSXH                                    NUMBER(10) NOT NULL);
CREATE UNIQUE INDEX T_FSBSFXMLNR_PK ON T_FSBSFXMLNR(BKDH,FSXH);
ALTER TABLE T_FSBSFXMLNR ADD (CONSTRAINT T_FSBSFXMLNR_PK
    PRIMARY KEY (BKDH,FSXH));
CREATE TABLE T_FSGH(
          GHBH                                    VARCHAR2(3) NOT NULL,
          GHXL                                    VARCHAR2(2) NOT NULL,
          GHNR                                    LONG NOT NULL,
          FSXH                                    NUMBER(10) NOT NULL);
CREATE UNIQUE INDEX T_FSGH_PK ON T_FSGH(FSXH);