package com.ume.framework.util.timer;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import com.ume.framework.Globals;
import com.ume.framework.base.BaseDAO;
import com.ume.framework.base.BaseVO;
import com.ume.framework.base.DAOFactory;
import com.ume.framework.orgmanage.User;
import com.ume.framework.orgmanage.UserVO;
import com.ume.framework.util.DBUtil;
import com.ume.framework.util.ExceptionMessage;
import com.ume.framework.util.Pub;
import com.ume.framework.util.timer.TimerInterface;
import com.yswz.common.vo.FailLogVO;
public class TaskSchedule implements TimerInterface {
private static Logger log;
public TaskSchedule()
{
}
public void executeTask(){
Connection conn = null;
String ywlx = "000000";
User user = new UserVO();
user.setAccount("superman");
user.setName("管理员");
user.setDepartment("150000100");
user.setFlag(User.FLAG_NORMAL);
try{
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
//清空临时表
String delRs[][] = {{"delete from ys_cx_bjcx_lsb"},{"delete from ys_cx_kfdw_lsb"},
{"delete from ys_cx_kfdw_xm_lsb"},{"delete from ys_cx_kfdw_xm_ld_lsb"},
{"delete from ys_cx_kfdw_xm_ld_fj_lsb"},{"delete from ys_cx_spyfysxk_lsb"}
,{"delete from ys_cx_wqhtba_lsb"},{"delete from ys_cx_esfy_lsb"}};
for(int k = 0; k < delRs.length; k++ ){
Thread.sleep(1000);
DBUtil.exec(conn, delRs[k][0]);
conn.commit();
}
//业务数据备份到临时表中
String[] insSql_lsb={" INSERT INTO Ys_Cx_Kfdw_Xm_Lsb (SID, SFXS,Jlxgsj,Jltbsj,JLXGZT, Jltbzt)"
+ " SELECT SID , SFXS , SYSDATE ,SYSDATE ,'1','1' FROM Ys_Cx_Kfdw_Xm "
};
try{
for(int k1 = 0; k1 < insSql_lsb.length; k1++ ){
BaseVO cvo = new BaseVO();
DBUtil.exec(conn, insSql_lsb[k1]);
conn.commit();
}
}catch(Exception w){
FailLog(ExceptionMessage.getMessageInfo(w),insSql_lsb[0],null);
}
//清空业务表
String[] del_sql1={"delete from ys_cx_bjcx","delete from ys_cx_kfdw","delete from ys_cx_kfdw_xm",
"delete from ys_cx_kfdw_xm_ld","delete from ys_cx_kfdw_xm_ld_fj","delete from ys_cx_spyfysxk",
"delete from ys_cx_wqhtba","delete from ys_cx_esfy"};
for(int k = 0; k < del_sql1.length; k++ ){
DBUtil.exec(conn, del_sql1[k]);
Thread.sleep(1000);
conn.commit();
}
//榆树业务表-->业务表
String[] insSql={
"insert into ys_cx_kfdw_xm_ld_fj(SID,KF_SID,XM_SID,LP_SID,FJH,DY,FWMJ,TNMJ,HX,FWYT,zt,LCH，"
+ "JLXGSJ,JLTBSJ,JLXGZT,JLTBZT) select b.id sid,a.KFSID KF_SID,a.XMJBXXID XM_SID,c.id LP_SID,"
+ " b.FH FJH, b.DY DY, b.JZMJ FWMJ,b.TNMJ TNMJ,b.HX HX,b.FWYT FWYT,b.FWZT zt,b.ljcs LCH ,"
+ "sysdate , sysdate ,'1' ,'1' from XMGL_XM_XMJBXXB@YSWZ_SY a, GGFC_HB@YSWZ_SY b, GGFC_LB@YSWZ_SY c"
+ " where c.id_xmxq = a.xmjbxxid and c.id = b.id_lid and c.sfyx = '1' and b.sfyx = '1' and a.sbzt = '5' "
,"insert into ys_cx_wqhtba(sid,HTHM,xmmc,LD,DY,FJH,ZFK,HTZT,BASJ,lc,YT,ZMJ,GFRMM,GFRSFZH,gfr,csdw,jlxgsj,"
+ "jltbsj,jlxgzt,jltbzt) select a.id sid, a.hth HTHM, a.xmxqmc xmmc, c.zh LD,c.DY DY,c.FH FJH,"
+ " a.JJFSJK_ZJ ZFK, decode(a.TEC_HTZT,'01','网签','02','已备案','03','已撤销','04','变更审核中',"
+ "'05','撤销审核中','06','锁定','07','提交备案','08','提交备案变更审核中','09','提交备案撤销审核中') HTZT,"
+ " to_date(a.HTBAYD_YQCL1_YQSJ,'yyyy-MM-dd HH24:mi:ss') BASJ,e.ljcs lc,e.fwyt YT, c.JZMJ ZMJ,"
+ " (select g.password from HTBA_HT_PASSWORD@yswz_sy g where g.id_entity = a.id_entity) GFRMM,"
+ " (select wm_concat( distinct DSRZJHM) from HTBA_HT_QLRMX@yswz_sy h where h.id_zb = a.id and h.dsrlx = '02') GFRSFZH,"
+ " (select wm_concat ( distinct h.dsrmc) from HTBA_HT_QLRMX@yswz_sy h where h.id_zb = a.id and h.dsrlx = '02') gfr,"
+ " (select wm_concat(distinct h.dsrmc) from HTBA_HT_QLRMX@yswz_sy h where h.id_zb = a.id and h.dsrlx = '01') csdw,"
+ " sysdate , sysdate ,'1' ,'1' from htba_ht_htzb@yswz_sy a, HTBA_HT_HMX@yswz_sy c, GGFC_HB@yswz_sy e "
+ "where a.id = c.id_zb(+) and e.id = c.id_bz and a.tec_sfyx = '1'"
,"INSERT INTO ys_cx_esfy(sid, ZL,LC,DY, fjh,MJ,HX,FYZT,JLXGSJ,JLTBSJ,JLXGZT,JLTBZT )SELECT a.id sid, "
+ " a.FWZL ZL, a.LJCS LC, A.DY DY, a.fh fjh, a.JZMJ MJ, a.HX HX, '' FYZT, SYSDATE,"
+ " SYSDATE,'1','1' FROM GGFC_HB@yswz_sy a WHERE a.YSXKZT = 1 and a.fwzt = 05 and a.sfyx = 1"
, "INSERT INTO ys_cx_bjcx(SID,YWSLH,XSYWLX,SLSJ,SFBJ,jlXGSJ,Jltbsj,JLXGZT,JLTBZT) select jjbh sid,"
+ " slh YWSLH, ywlx_name XSYWLX, slsj SLSJ, case when yw_state in ('2', '3', '5') then '是' else '否' end SFBJ ,"
+ "SYSDATE , SYSDATE, '1', '1' from ggfc_jjb@yswz_sy j WHERE yw_state <> '4'"
,"INSERT INTO ys_cx_kfdw_xm_ld (SID,KF_SID, XM_SID,ldh ,LDMC,ZCS,ZTS,ZJZMJ, ZT,JLXGSJ,JLTBSJ,JLXGZT,JLTBZT ) "
+ "select b.id sid, a.KFSID KF_SID, a.XMJBXXID XM_SID, b.ZH ldh, b.LMC LDMC, b.ZCS ZCS, "
+ " (select count(1) from ggfc_hb@yswz_sy h where h.id_lid = b.id and h.sfyx = '1') ZTS, "
+ "(select sum(h.jzmj) from ggfc_hb@yswz_sy h where h.id_lid = b.id and h.sfyx = '1') ZJZMJ,"
+ " (select case when wm_concat(distinct(c.CSDJZT)) like '%1%' then '现房' "
+ " when wm_concat(distinct(c.syqlzt)) like '%1%' then '现房' else '期房' end as zt"
+ " from GGFC_HB@YSWZ_SY c where b.id = c.id_lid and c.sfyx = '1') ZT ,SYSDATE , SYSDATE , "
+ "'1','1' from XMGL_XM_XMJBXXB@YSWZ_SY a, GGFC_LB@YSWZ_SY b, CYZT_CYJG_KFS@YSWZ_SY d "
+ "where a.xmjbxxid = b.id_xmxq and a.kfsid = d.id_seq"
, "INSERT INTO ys_cx_kfdw_xm (SID,Kf_Sid,XMMC,XMZL,ds, KPSJ, Jlxgsj,JLTBSJ,Jltbzt,JLXGZT ) Select b.XMJBXXID sid,"
+ " a.ID_SEQ KF_SID, b.XMMC XMMC, b.XXDZ XMZL, "
+ " (select count(1) from ggfc_lb@yswz_sy l where l.id_xmxq = b.xmjbxxid and l.sfyx = '1') as ds ,"
+ " b.KPRQ KPSJ ,SYSDATE , SYSDATE ,'1','1' from CYZT_CYJG_KFS@YSWZ_SY a, XMGL_XM_XMJBXXB@YSWZ_SY b "
+ "where a.id_seq = b.kfsid"
,"INSERT INTO ys_cx_spyfysxk (SID ,ysxkzh,xmsid,xmmc,kfs,YSLD, yszts,SJ,zt , Jlxgsj,JLTBSJ,JLXGZT,JLTBZT )"
+ " select ysdj.id sid, fzxx.zsbh ysxkzh, "
+ " (select j.XMJBXXID from xmgl_xm_xmjbxxb@yswz_sy j where j.xmjbxxid = ysdj.id_xmxq ) xmsid,"
+ " (select j.xmmc from xmgl_xm_xmjbxxb@yswz_sy j where j.xmjbxxid = ysdj.id_xmxq ) xmmc,"
+ " kfs.kfsmc kfs, ysdj.ysfw YSLD, fzxx.yszzts yszts,fzxx.fzrq SJ, "
+ " Decode (ysdj.EXT3,'01','预售','02','销售') zt , SYSDATE , SYSDATE , '1', '1'"
+ " from htba_ys_ysdj@yswz_sy ysdj "
+ "left join htba_ys_ysdj_fzxx@yswz_sy fzxx on ysdj.jjbh = fzxx.jjbh "
+ "left join htba_ys_ysdj_kfsmx@yswz_sy kfs on ysdj.id = kfs.id_zb "
+ "left join (select c.code_name,c.code from up_codelist_code@yswz_sy c"
+ " where c.codelist_id = 'PRESALEFWYT') ysyt on ysyt.code = ysdj.ext8 where ysdj.state in ('03','06')"
,"INSERT INTO ys_cx_kfdw (SID , KFSMC, FDDBR,YYZZHM, DZ, LXDH,Jlxgsj,Jltbsj,JLXGZT, Jltbzt ) select ID_SEQ sid,"
+ " jgmc KFSMC, FRDB FDDBR, YYJGH YYZZHM, ZCDZ DZ, LXDH LXDH ,SYSDATE ,"
+ "SYSDATE ,'1','1' from CYZT_CYJG_KFS@YSWZ_SY where CYLX = 01 and sfyx = '1'"
};
try{
for(int k1 = 0; k1 < insSql.length; k1++ ){
DBUtil.exec(conn, insSql[k1]);
conn.commit();
}
}catch(Exception w){
FailLog(ExceptionMessage.getMessageInfo(w),insSql[0],null);
}
//临时表-->正式表
String[] insSql_ywb={"Update Ys_Cx_Kfdw_Xm a set (sfxs)=(select sfxs from Ys_Cx_Kfdw_Xm_Lsb b where a.sid=b.sid)"
+ " where exists (select 1 from Ys_Cx_Kfdw_Xm_Lsb where sid=a.sid) "
};
try{
for(int k1 = 0; k1 < insSql_ywb.length; k1++ ){
DBUtil.exec(conn, insSql_ywb[k1]);
conn.commit();
}
}catch(Exception w){
FailLog(ExceptionMessage.getMessageInfo(w),insSql_ywb[0],null);
}
}catch(Exception e){
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
/**
* 失败日志
* 表： FAIL_LOG
* @throws Exception
*/
public static void FailLog(String message,String sql,String data) throws Exception{
Document failLog = DocumentHelper.createDocument();
Element failElement = failLog.addElement("ROW");
Element failSid = failElement.addElement("SID");
failSid.setText(Pub.getGUID32());
Element failSbyy = failElement.addElement("SBYY");
failSbyy.setText(message);
Element failSql = failElement.addElement("SQL");
failSql.setText(sql);
Element failData = failElement.addElement("DATA");
failData.setText(data);
Connection conn = null;
FailLogVO vo = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
BaseDAO dao = DAOFactory.getInstance().createDAO(conn);
List list = failLog.selectNodes("/ROW");
for (int i = 0; i < list.size(); i++) {
Element row = (Element) list.get(i);
vo = new com.yswz.common.vo.FailLogVO();
vo.setValue(row);
vo.setInternal("JLXGZT",Globals.JLXGZT_INSERT);
vo.setInternal("JLXGSJ",Pub.getCurrentDate());
vo.setInternal("JLTBZT",Globals.JLTBZT_WTB);
vo.setInternal("JLTBSJ",Pub.getCurrentDate());
dao.insert(conn, vo);
}
conn.commit();
}catch (Exception e)
{
log.error(e,e);
}
finally
{
if (conn != null)
conn.close();
}
}
}
sql语句可学习内容
对于时间格式的处理及获取当前系统时间
批量插入格式:insert into talbe1 （a,b,c） select a,b,c from table2
批量更新格式:Update table1 a set (c1,c2,c3)=(select c1,c2,c3 from table2 b where a.id=b.id)
where exists (select 1 from table2 where id=a.id)；
要加上后面的where 条件，以保证数据能够完全匹配，从而确保数据能够正确、顺利的更新完成！
package com.ume.framework.util.timer;
import javax.servlet.ServletException;
import org.apache.struts.action.ActionServlet;
import org.apache.struts.action.PlugIn;
import org.apache.struts.config.ModuleConfig;
public class UmeTimerService implements PlugIn
{
private static UmeTimer timer = null;
//@Override
public void destroy()
{
if(null!=timer)
timer.cancel();
}
//@Override
public void init(ActionServlet arg0, ModuleConfig arg1)
throws ServletException
{
if(null==timer)
timer = new UmeTimer("UmeTimerInstance");
long delay = 1000*30*100000;
UmeTimerTask cfp = new UmeTimerTask(new TaskSchedule());
//1000*60*60
addTask(cfp, delay, 1000*60000);
}
//添加定时任务
public static void addTask(UmeTimerTask utt, long delay, long period)
{
delay= 0;
period=60*1000;
timer.schedule(utt, delay, period);
}
//添加定时任务
public static void addInitTask(InitInterface ii)
{
ii.executeInitialize();
}
}
失败日志表结构
接下来更换一种导表方式 通过走中间表进行导表
package com.ume.framework.util.timer;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import com.ume.framework.Globals;
import com.ume.framework.base.BaseDAO;
import com.ume.framework.base.BaseResultSet;
import com.ume.framework.base.BaseVO;
import com.ume.framework.base.DAOFactory;
import com.ume.framework.base.PageManager;
import com.ume.framework.event.EventManager;
import com.ume.framework.event.EventVO;
import com.ume.framework.orgmanage.User;
import com.ume.framework.orgmanage.UserVO;
import com.ume.framework.util.DBUtil;
import com.ume.framework.util.ExceptionMessage;
import com.ume.framework.util.Pub;
import com.ume.framework.util.timer.TimerInterface;
import com.yswz.common.vo.FailLogVO;
public class TaskSchedule implements TimerInterface {
private static Logger log;
public TaskSchedule()
{
//log = Log.getLogger(ZdptService.class);
}
public void executeTask(){
BaseResultSet bs = null;
Document doc = null;
PageManager page = null;
Connection conn = null;
EventVO evo = null;
String ywlx = "000000";
User user = new UserVO();
user.setAccount("superman");
user.setName("管理员");
user.setDepartment("150000100");
user.setFlag(User.FLAG_NORMAL);
String dPrimay = "";
try{
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
BaseDAO dao = DAOFactory.getInstance().createDAO(conn);
evo = EventManager.createEvent(conn, ywlx, user);
//清空临时表
String delRs[][] = {{"delete from ys_cx_bjcx_lsb"},{"delete from ys_cx_kfdw_lsb"},{"delete from ys_cx_kfdw_xm_lsb"},
{"delete from ys_cx_kfdw_xm_ld_lsb"},{"delete from ys_cx_kfdw_xm_ld_fj_lsb"},{"delete from ys_cx_spyfysxk_lsb"}
,{"delete from ys_cx_wqhtba_lsb"},{"delete from ys_cx_esfy_lsb"},{"delete from TABLETEMP"}};
for(int k = 0; k < delRs.length; k++ ){
Thread.sleep(1000);
DBUtil.exec(conn, delRs[k][0]);
conn.commit();
}
//业务数据备份到临时表中
String sql11=" select sql , vo ,swhere from config_table where sid ='7' ";
String configRs11[][] = DBUtil.query(conn, sql11);
for(int j = 0 ; j < configRs11.length; j++){
int num = 0;
try{
if (page == null)
page = new PageManager();
page.setPageRows(1000);
bs = DBUtil.query(conn, configRs11[j][0],page);
doc = bs.getDocument();
List<?> list = doc.selectNodes("/RESPONSE/RESULT/ROW");
BaseVO vo = (BaseVO)Class.forName(configRs11[j][1]).newInstance();
for (int i = 0; i < list.size(); i++) {
num = list.size();
Element row = (Element) list.get(i);
dPrimay = row.elementText("SID");
BaseVO cvo = new BaseVO();
cvo.copyDefinition(vo);
cvo.setValue(row);
cvo.setInternal("SJBH",evo.getSjbh());
cvo.setInternal("JLXGZT",Globals.JLXGZT_INSERT);
cvo.setInternal("JLXGSJ",Pub.getCurrentDate());
cvo.setInternal("JLTBZT",Globals.JLTBZT_WTB);
cvo.setInternal("JLTBSJ",Pub.getCurrentDate());
DBUtil.executeUpdate(conn,"insert into tabletemp(id,tablename,sid,tbsj,jd) values "
+ "(sys_guid(),'"+cvo.getVOTableName()+"','"+cvo.getInternal("SID")+"',sysdate,'业务数据备份到临时表中') ",null);
dao.insert(cvo);
}
} catch (Exception e) {
FailLog(ExceptionMessage.getMessageInfo(e),configRs11[j][0],dPrimay);
}
conn.commit();
if(num==1000){
j = j - 1 ;
}
}
//清空业务表
String[] del_sql1={"delete from ys_cx_bjcx","delete from ys_cx_kfdw","delete from ys_cx_kfdw_xm","delete from ys_cx_kfdw_xm_ld",
"delete from ys_cx_kfdw_xm_ld_fj","delete from ys_cx_spyfysxk","delete from ys_cx_wqhtba","delete from ys_cx_esfy"};
System.out.println(del_sql1.length);
for(int k = 0; k < del_sql1.length; k++ ){
DBUtil.exec(conn, del_sql1[k]);
Thread.sleep(1000);
conn.commit();
}
//榆树业务表-->业务表
String sql111=" select sql , vo ,swhere from config_table where sid >10 and sid < 90 order by to_number (sid) ";
String configRs111[][] = DBUtil.query(conn, sql111);
for(int j = 0 ; j < configRs111.length; j++){
int num = 0;
try{
if (page == null)
page = new PageManager();
page.setPageRows(1000);
bs = DBUtil.query(conn, configRs111[j][0],page);
doc = bs.getDocument();
List<?> list = doc.selectNodes("/RESPONSE/RESULT/ROW");
BaseVO vo = (BaseVO)Class.forName(configRs111[j][1]).newInstance();
for (int i = 0; i < list.size(); i++) {
num = list.size();
Element row = (Element) list.get(i);
dPrimay = row.elementText("SID");
BaseVO cvo = new BaseVO();
cvo.copyDefinition(vo);
cvo.setValue(row);
cvo.setInternal("SJBH",evo.getSjbh());
cvo.setInternal("JLXGZT",Globals.JLXGZT_INSERT);
cvo.setInternal("JLXGSJ",Pub.getCurrentDate());
cvo.setInternal("JLTBZT",Globals.JLTBZT_WTB);
cvo.setInternal("JLTBSJ",Pub.getCurrentDate());
DBUtil.executeUpdate(conn,"insert into tabletemp(id,tablename,sid,tbsj,jd) values "
+ "(sys_guid(),'"+cvo.getVOTableName()+"','"+cvo.getInternal("SID")+"',sysdate,'榆树业务表到业务表') ",null);
dao.insert(cvo);
}
} catch (Exception e) {
FailLog(ExceptionMessage.getMessageInfo(e),configRs111[j][0],dPrimay);
}
conn.commit();
if(num==1000){
j = j - 1 ;
}
}
//临时表-->正式表
String sql12="select sql , vo from config_table where sid =99 ";
String configRs12[][] = DBUtil.query(conn, sql12);
for(int j = 0 ; j < configRs12.length; j++){
int num = 0;
if (page == null)
page = new PageManager();
page.setPageRows(1000);
bs = DBUtil.query(conn, configRs12[j][0],page);
doc = bs.getDocument();
List<?> list = doc.selectNodes("/RESPONSE/RESULT/ROW");
BaseVO vo = (BaseVO)Class.forName(configRs12[j][1]).newInstance();
try{
for (int i = 0; i < list.size(); i++) {
num = list.size();
Element row = (Element) list.get(i);
dPrimay = row.elementText("SID");
BaseVO cvo = new BaseVO();
cvo.copyDefinition(vo);
cvo.setValue(row);
cvo.setInternal("SJBH",evo.getSjbh());
cvo.setInternal("JLXGZT",Globals.JLXGZT_INSERT);
cvo.setInternal("JLXGSJ",Pub.getCurrentDate());
cvo.setInternal("JLTBZT",Globals.JLTBZT_WTB);
cvo.setInternal("JLTBSJ",Pub.getCurrentDate());
DBUtil.executeUpdate(conn,"insert into tabletemp(id,tablename,sid,tbsj) values "
+ "(sys_guid(),'"+cvo.getVOTableName()+"临时表到正式表','"+cvo.getInternal("SID")+"',sysdate) ",null);
dao.update(cvo);
}
} catch (Exception e) {
log.error(e, e);
FailLog(ExceptionMessage.getMessageInfo(e),configRs12[j][0],dPrimay);
}
conn.commit();
if(num==1000){
j = j - 1 ;
}
}
}catch(Exception e){
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
if (conn != null)
try {
conn.close();
bs.Close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
/**
* 失败日志
* 表： FAIL_LOG
* @throws Exception
*/
public static void FailLog(String message,String sql,String data) throws Exception{
Document failLog = DocumentHelper.createDocument();
Element failElement = failLog.addElement("ROW");
Element failSid = failElement.addElement("SID");
failSid.setText(Pub.getGUID32());
Element failSbyy = failElement.addElement("SBYY");
failSbyy.setText(message);
Element failSql = failElement.addElement("SQL");
failSql.setText(sql);
Element failData = failElement.addElement("DATA");
failData.setText(data);
Connection conn = null;
FailLogVO vo = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
BaseDAO dao = DAOFactory.getInstance().createDAO(conn);
List list = failLog.selectNodes("/ROW");
for (int i = 0; i < list.size(); i++) {
Element row = (Element) list.get(i);
vo = new com.yswz.common.vo.FailLogVO();
vo.setValue(row);
vo.setInternal("JLXGZT",Globals.JLXGZT_INSERT);
vo.setInternal("JLXGSJ",Pub.getCurrentDate());
vo.setInternal("JLTBZT",Globals.JLTBZT_WTB);
vo.setInternal("JLTBSJ",Pub.getCurrentDate());
dao.insert(conn, vo);
}
conn.commit();
}catch (Exception e)
{
log.error(e,e);
}
finally
{
if (conn != null)
conn.close();
}
}
}
中间表表结构
另外导表的sql也要修改哦
举例说明
--网签合同备案
select a.id sid,
a.hth HTHM, --合同号码
a.xmxqmc xmmc, --项目名称
c.zh LD, --楼栋
c.DY DY, --单元
c.FH FJH, --房间号
a.JJFSJK_ZJ ZFK, --总房款
decode(a.TEC_HTZT,'01','网签','02','已备案','03','已撤销','04','变更审核中','05','撤销审核中','06','锁定','07','提交备案','08','提交备案变更审核中','09','提交备案撤销审核中') HTZT, --合同状态
a.HTBAYD_YQCL1_YQSJ BASJ, --备案时间
e.ljcs lc,
e.fwyt YT, --用途 暂时没有
c.JZMJ ZMJ, --总面积
(select g.password
from HTBA_HT_PASSWORD@yswz_sy g
where g.id_entity = a.id_entity) GFRMM, --购房人密码
(select wm_concat( distinct DSRZJHM)
from HTBA_HT_QLRMX@yswz_sy h
where h.id_zb = a.id
and h.dsrlx = '02') GFRSFZH, --购房人身份证号
(select wm_concat ( distinct h.dsrmc)
from HTBA_HT_QLRMX@yswz_sy h
where h.id_zb = a.id
and h.dsrlx = '02') gfr, --购房人
(select wm_concat(distinct h.dsrmc)
from HTBA_HT_QLRMX@yswz_sy h
where h.id_zb = a.id
and h.dsrlx = '01') csdw --出售单位
from htba_ht_htzb@yswz_sy a, HTBA_HT_HMX@yswz_sy c, GGFC_HB@yswz_sy e
where a.id = c.id_zb(+)
and e.id = c.id_bz
and a.tec_sfyx = '1'
and a.id not in (select sid from tabletemp where tablename ='YS_CX_WQHTBA')
最后面的not in 一定要加上哦 要不然会陷入死循环
287
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
