1106修改.txt

行业应急系统 包括: 应急系统（待遇核发） 发放系统 开发环境:Delphi5 (sp1)+ Oracle 8.05 MultiExport是一个可以将数据按指定字段倒出

查询
SELECT tryjbxx.*
  FROM tryjbxx,
       tswdysb
 WHERE tryjbxx.ryid = tswdysb.ryid
   AND (    tryjbxx.ryzt <> 5
        AND tryjbxx.ryzt <> 6
       )
       
修改
UPDATE tryjbxx
   SET ryzt = 6
 WHERE ryid IN (SELECT tryjbxx.ryid
                  FROM tryjbxx,
                       tswdysb
                 WHERE tryjbxx.ryid = tswdysb.ryid
                   AND (    tryjbxx.ryzt <> 5
                        AND tryjbxx.ryzt <> 6
                       ))
	and ryzt=2 
/	
COMMIT
/	

查询每天日志查询
SELECT *
  FROM tryjbxx_his
 WHERE ryid IN (SELECT tryjbxx.ryid
                  FROM tryjbxx,
                       tswdysb
                 WHERE tryjbxx.ryid = tswdysb.ryid
                   AND (    tryjbxx.ryzt <> 5
                        AND tryjbxx.ryzt <> 6
                       ))

删除触发器
TRIGGER "SIDB2000".tri_tswdysb_delete
BEFORE DELETE
ON tswdysb
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  v_XGCS NUMBER;  --修改次数
Begin
  SELECT NVL(MAX(XGCS),0) + 1
    INTO v_XGCS
  FROM tSWDYSB_HIS
  WHERE RYID = :OLD.RYID;

  INSERT INTO TSWDYSB_HIS
     VALUES(
            :OLD.RYID,   --人员ID
			v_XGCS,   --修改次数
			:OLD.SBRQ,   --申报日期
			:OLD.SWSJ,   --死亡日期
			:OLD.SWZH,   --死亡证号
			:OLD.SWYY,   --死亡原因
			:OLD.YWYS,   --有无遗属（0-没有遗属；1-有遗属）
			:OLD.SHYJ,   --审核意见
			:OLD.YWZT,   --业务状态（1-未审核；3-已审核）
			:OLD.JBR,   --经办人
			:OLD.JBRQ,   --经办日期
			'删除',   --审核人
			:OLD.SHRQ   --审核日期
            );
End;
	      
	      
	      
	      
---------------------查询内容加上单位代码
CREATE OR REPLACE VIEW VRYJBXX ( RYID, 
HY, 单位代码,单位名称, 姓名, 社会保障号, 
出生时间, 参加工作时间, 参保时间, 人员状态
 ) AS SELECT 
	tRYJBXX.RYID
	,DM_HY.DMMC	AS "HY"
	,tDWXX.DWDM 	AS "单位代码"
	,tDWXX.DWMC 	AS "单位名称"
	,tRYJBXX.XM 	AS "姓名"
	,tRYJBXX.SFZH	AS "社会保障号"
	,tRYJBXX.CSSJ	AS "出生时间"
	,tRYJBXX.CJGZSJ	AS "参加工作时间"
	,tRYJBXX.CBSJ	AS "参保时间"
	,DM_RYZT.DMMC	AS "人员状态"
FROM 	
	tRYJBXX,
	tDWXX,
	tDMB DM_HY,
	tDMB DM_RYZT
WHERE
	tRYJBXX.DWID=tDWXX.DWID AND
	tDWXX.SSHY=DM_HY.DM(+) AND DM_HY.ZDBZ='SSHY' AND
	tRYJBXX.RYZT=DM_RYZT.DM(+) AND DM_RYZT.ZDBZ='RYZT' 

修改

DELETE FROM tsysfield
      WHERE tableid = 1
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(1,1,'RYID',0,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(2,1,'HY',0,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(3,1,'单位代码',1,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(4,1,'单位名称',1,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(5,1,'姓名',1,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(6,1,'社会保障号',1,'','String    ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(7,1,'出生时间',1,'','Date      ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(8,1,'参加工作时间',1,'','Date      ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(9,1,'参保时间',1,'','Date      ','','','','')
/
INSERT INTO tsysfield
(ID,TABLEID,FIELDNAME,VISIBLE,CONDITION,TYPE,COMMENTS,TABLENAME,JOINCOND,ALIASTABLE)
VALUES 
(10,1,'人员状态',1,'SELECT dmmc as Value FROM tDMB WHERE zdbz=''RYZT'' ORDER BY DM','String    ','',
'','','')
/