pc-new.sql

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

 create view v_checkjsfspd as 
 select t_jsrz.jsxh from t_fbkj,t_snmlfb,t_jspdyssm,t_jsrz
 where t_jsrz.sjzt='2' and (t_fbkj.jsfs='2' or t_fbkj.jsfs='3') 
	and t_jspdyssm.bkdh=t_snmlfb.bkdh and t_jspdyssm.jsxh=t_jsrz.jsxh
	and t_snmlfb.fbkjbh=t_fbkj.fbkjbh;

 create table t_zcghnr  ( ghnr varchar2(80) , xh number(4) ) ;
 create table t_tempysflag ( fbkjbh varchar2(2), jsfs char(1), jsxh number(10), sjbh varchar2(2), jssj date );
 create table t_fbkj_wjjs ( fbkjbh varchar2(2), wjlen varchar2(10),
 	cjsj varchar2(14),xgsj varchar2(14));

CREATE UNIQUE INDEX T_ZFJ_PK ON T_ZFJ(ZFJBH,YXNF,SJBH);
ALTER TABLE T_ZFJ ADD (CONSTRAINT T_ZFJ_PK
    PRIMARY KEY (ZFJBH,YXNF,SJBH));
/* FK for reference to T_CZYB through unnamed relation */
CREATE INDEX  T_CZRZ_X1
	ON T_CZRZ (CZYH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSBKJHML_X2
	ON T_FSBKJHML (FSXH);

/* FK for reference to T_FSBKJHML through unnamed relation */
CREATE INDEX  T_FSBKJHMLNR_X3
	ON T_FSBKJHMLNR (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSBSFXML_X4
	ON T_FSBSFXML (FSXH);

/* FK for reference to T_FSBSFXML through unnamed relation */
CREATE INDEX  T_FSBSFXMLNR_X5
	ON T_FSBSFXMLNR (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSGH_X6
	ON T_FSGH (FSXH);

/* FK for reference to T_FSPDYSSM through unnamed relation */
CREATE INDEX  T_FSPDYSSJ_X7
	ON T_FSPDYSSJ (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSPDYSSM_X8
	ON T_FSPDYSSM (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSTJBB_X9
	ON T_FSTJBB (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSYXSJ_X10
	ON T_FSYXSJ (FSXH);

/* FK for reference to T_FSZDYSSM through unnamed relation */
CREATE INDEX  T_FSZDYSSJ_X11
	ON T_FSZDYSSJ (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSZDYSSM_X12
	ON T_FSZDYSSM (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_FSZFJB_X13
	ON T_FSZFJB (FSXH);

/* FK for reference to T_FSZFJB through unnamed relation */
CREATE INDEX  T_FSZFJBNR_X14
	ON T_FSZFJBNR (FSXH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_HZ_X15
	ON T_HZ (FSXH);

/* FK for reference to T_SJBH through unnamed relation */
CREATE INDEX  T_HZ_X16
	ON T_HZ (SJBH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSBKHZML_X17
	ON T_JSBKHZML (JSXH,SJBH);

/* FK for reference to T_JSBKHZML through unnamed relation */
CREATE INDEX  T_JSBKHZMLNR_X18
	ON T_JSBKHZMLNR (SJBH,JSXH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSGH_X19
	ON T_JSGH (JSXH,SJBH);

/* FK for reference to T_JSPDYSSM through unnamed relation */
CREATE INDEX  T_JSPDYSSJ_X20
	ON T_JSPDYSSJ (SJBH,JSXH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSPDYSSM_X21
	ON T_JSPDYSSM (JSXH,SJBH);

/* FK for reference to T_SNML through unnamed relation */
CREATE INDEX  T_JSPDYSSM_X22
	ON T_JSPDYSSM (BKDH,YXNF);

/* FK for reference to T_SJBH through unnamed relation */
CREATE INDEX  T_JSRZ_X23
	ON T_JSRZ (SJBH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSYXSJ_X24
	ON T_JSYXSJ (JSXH,SJBH);

/* FK for reference to T_JSZDYSSM through unnamed relation */
CREATE INDEX  T_JSZDYSSJ_X25
	ON T_JSZDYSSJ (SJBH,JSXH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSZDYSSM_X26
	ON T_JSZDYSSM (JSXH,SJBH);

/* FK for reference to T_SNML through unnamed relation */
CREATE INDEX  T_JSZDYSSM_X27
	ON T_JSZDYSSM (BKDH,YXNF);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_JSZFJB_X28
	ON T_JSZFJB (JSXH,SJBH);

/* FK for reference to T_JSZFJB through unnamed relation */
CREATE INDEX  T_JSZFJBNR_X29
	ON T_JSZFJBNR (SJBH,JSXH);

/* FK for reference to T_SNML through unnamed relation */
CREATE INDEX  T_SNMLFB_X30
	ON T_SNMLFB (BKDH,YXNF);

/* FK for reference to T_FBKJ through unnamed relation */
CREATE INDEX  T_SNMLFB_X31
	ON T_SNMLFB (FBKJBH);

/* FK for reference to T_SJBH through unnamed relation */
CREATE INDEX  T_SNMLFB_X32
	ON T_SNMLFB (SJBH);

/* FK for reference to T_FSRZ through unnamed relation */
CREATE INDEX  T_ZCFSSJ_X33
	ON T_ZCFSSJ (FSXH);

/* FK for reference to T_JSRZ through unnamed relation */
CREATE INDEX  T_ZCJSSJ_X34
	ON T_ZCJSSJ (JSXH,SJBH);

/* FK for reference to T_SJBH through unnamed relation */
CREATE INDEX  T_ZFJ_X35
	ON T_ZFJ (SJBH);

/* FK for reference to T_CZYB through unnamed relation */
/*ALTER TABLE T_CZRZ ADD (
    FOREIGN KEY (CZYH)
    REFERENCES T_CZYB (CZYH));
  this foreign key be deleted for security manager
*/

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSBKJHML ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSBKJHML through unnamed relation */
ALTER TABLE T_FSBKJHMLNR ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSBKJHML (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSBSFXML ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSBSFXML through unnamed relation */
ALTER TABLE T_FSBSFXMLNR ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSBSFXML (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSGH ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSPDYSSM through unnamed relation */
ALTER TABLE T_FSPDYSSJ ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSPDYSSM (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSPDYSSM ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSTJBB ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSYXSJ ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSZDYSSM through unnamed relation */
ALTER TABLE T_FSZDYSSJ ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSZDYSSM (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSZDYSSM ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_FSZFJB ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_FSZFJB through unnamed relation */
ALTER TABLE T_FSZFJBNR ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSZFJB (FSXH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_HZ ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_SJBH through unnamed relation */
ALTER TABLE T_HZ ADD (
    FOREIGN KEY (SJBH)
    REFERENCES T_SJBH (SJBH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSBKHZML ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_JSBKHZML through unnamed relation */
ALTER TABLE T_JSBKHZMLNR ADD (
    FOREIGN KEY (SJBH,JSXH)
    REFERENCES T_JSBKHZML (SJBH,JSXH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSGH ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_JSPDYSSM through unnamed relation */
ALTER TABLE T_JSPDYSSJ ADD (
    FOREIGN KEY (SJBH,JSXH)
    REFERENCES T_JSPDYSSM (SJBH,JSXH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSPDYSSM ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_SNML through unnamed relation */
ALTER TABLE T_JSPDYSSM ADD (
    FOREIGN KEY (BKDH,YXNF)
    REFERENCES T_SNML (BKDH,YXNF));

/* FK for reference to T_SJBH through unnamed relation */
ALTER TABLE T_JSRZ ADD (
    FOREIGN KEY (SJBH)
    REFERENCES T_SJBH (SJBH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSYXSJ ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_JSZDYSSM through unnamed relation */
ALTER TABLE T_JSZDYSSJ ADD (
    FOREIGN KEY (SJBH,JSXH)
    REFERENCES T_JSZDYSSM (SJBH,JSXH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSZDYSSM ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_SNML through unnamed relation */
ALTER TABLE T_JSZDYSSM ADD (
    FOREIGN KEY (BKDH,YXNF)
    REFERENCES T_SNML (BKDH,YXNF));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_JSZFJB ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_JSZFJB through unnamed relation */
ALTER TABLE T_JSZFJBNR ADD (
    FOREIGN KEY (SJBH,JSXH)
    REFERENCES T_JSZFJB (SJBH,JSXH));

/* FK for reference to T_SNML through unnamed relation */
ALTER TABLE T_SNMLFB ADD (
    FOREIGN KEY (BKDH,YXNF)
    REFERENCES T_SNML (BKDH,YXNF));

/* FK for reference to T_FBKJ through unnamed relation */
ALTER TABLE T_SNMLFB ADD (
    FOREIGN KEY (FBKJBH)
    REFERENCES T_FBKJ (FBKJBH));

/* FK for reference to T_SJBH through unnamed relation */
ALTER TABLE T_SNMLFB ADD (
    FOREIGN KEY (SJBH)
    REFERENCES T_SJBH (SJBH));

/* FK for reference to T_FSRZ through unnamed relation */
ALTER TABLE T_ZCFSSJ ADD (
    FOREIGN KEY (FSXH)
    REFERENCES T_FSRZ (FSXH));

/* FK for reference to T_JSRZ through unnamed relation */
ALTER TABLE T_ZCJSSJ ADD (
    FOREIGN KEY (JSXH,SJBH)
    REFERENCES T_JSRZ (JSXH,SJBH));

/* FK for reference to T_SJBH through unnamed relation */
ALTER TABLE T_ZFJ ADD (
    FOREIGN KEY (SJBH)
    REFERENCES T_SJBH (SJBH));

delete from t_czyb;
insert into t_czyb values(01,'bkfx','\TN>W?R2','1');
COMMIT;