创建临时表进行性能优化

最新推荐文章于 2024-08-29 18:06:26 发布

liujianhua886

最新推荐文章于 2024-08-29 18:06:26 发布

阅读量318

点赞数

分类专栏：创建临时表进行性能优化 EAS 文章标签：数据库

本文链接：https://blog.csdn.net/liujianhua886/article/details/84506061

版权

EAS 同时被 2 个专栏收录

12 篇文章 1 订阅

订阅专栏

创建临时表进行性能优化

1 篇文章 0 订阅

订阅专栏

	public void updatePurAndSaleOrderByARAP(Context ctx, IObjectPK pk,boolean isAudit) throws EASBizException, BOSException {
//		boolean isAR = com.kingdee.eas.fi.ar.OtherBillFactory.getLocalInstance(ctx).exists(pk);
//		if (isAR) {
		String tempTableDefine[][] = {
				{ "APentryID", "VARCHAR(44)" },
				{ "TOBentryID", "VARCHAR(44)" }};
		Connection connection = CommonServerUtils.getConnection(ctx);
		SysTempTableHelper tem = new SysTempTableHelper(ctx, connection);
		String tempTable = tem.createTempTable(ctx, tempTableDefine);
		if(tempTable==null || tempTable.trim().length()==0)
		{
			System.out.println("临时表创建失败");
			return;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("	INSERT INTO "+tempTable+"	\n");
		sql.append("	select 	\n");
		sql.append("		\n");
		sql.append("	APentry.FID APentryID,	\n"); // 应付单分录ID
		sql.append("	TOBentry.FID TOBentryID	\n"); // 采购订单分录ID
		sql.append("		\n");
		sql.append("	from T_AP_OtherBillentry APentry                  	\n"); // 应付单分录
		sql.append("		\n");
		sql.append("	inner join T_IM_PurInWarehsEntry PIWentry         	\n"); // 应付单源单采购入库单分录
		sql.append("	on PIWentry.FID = APentry.FSourceBillEntryId	\n");
		sql.append("		\n");
		sql.append("	inner join T_IM_TransferOrderBillEntry TOBentry	\n"); // 采购入库单源单调拨订单分录
		sql.append("	on TOBentry.FID = PIWentry.FSourceBillEntryId	\n");
		sql.append("		\n");
		sql.append("	inner join T_IM_TransferOrderBill TOB	\n");
		sql.append("	on TOB.FID = TOBentry.FParentID	\n");
		sql.append("		\n");
		sql.append("	inner join T_SCM_BizType BType	\n");
		sql.append("	on BType.FID = TOB.FBizTypeID	\n");
		sql.append("		\n");
		sql.append("	where APentry.FParentID='"+pk+"'	\n");
		sql.append("	and (BType.FNumber = '370' or BType.FNumber = '310')	\n");

		tem.execute(sql.toString());

		StringBuffer sqla = new StringBuffer();
		sqla.append("	select 	\n");
		sqla.append("		\n");
		sqla.append("	temp.APentryID APentryID,	\n");
		sqla.append("	SOentry.FID SPOentryID,	\n");
		sqla.append("	'C48A423A' entryID	\n");
		sqla.append("		\n");
		sqla.append("	from "+tempTable+" temp	\n");
		sqla.append("		\n");
		sqla.append("	inner join T_SD_SaleOrderEntry SOentry       	\n");
		sqla.append("	on SOentry.FSourceBillEntryId = temp.TOBentryID	\n");
		sqla.append("		\n");
		sqla.append("	union all 	\n");
		sqla.append("		\n");
		sqla.append("	select 	\n");
		sqla.append("		\n");
		sqla.append("	temp.APentryID APentryID,	\n");
		sqla.append("	POentry.FID SPOentryID,	\n");
		sqla.append("	'3171BFAD' entryID	\n");
		sqla.append("		\n");
		sqla.append("	from "+tempTable+" temp	\n");
		sqla.append("		\n");
		sqla.append("	inner join T_SM_PurOrderEntry POentry       	\n");
		sqla.append("	on POentry.FSourceBillEntryId = temp.TOBentryID	\n");

		ResultSet result = DbUtil.executeQuery(ctx,sqla.toString());
		try {
			while (result.next()) 
			{
				String entryID = result.getString("entryID");
				if("C48A423A".equals(entryID))
				{
					SynergyBillAssistUtils.updateSaleByAP(ctx, result.getString("SPOentryID"), result.getString("APentryID"), isAudit);
				}else if("3171BFAD".equals(entryID))
				{
					SynergyBillAssistUtils.updatePurByAP(ctx, result.getString("SPOentryID"), result.getString("APentryID"), isAudit);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		//释放临时表
		if(tempTable==null || tempTable.trim().length()==0)
		{
			tem.releaseTempTable(tempTable);
		}

/*			// 2.应收单反写采购订单
			updatePurByAR(ctx, tempTable,pk, isAudit);
			// 3.应收单反写销售
			updateSaleByAR(ctx, tempTable,pk, isAudit);

			//释放临时表
			if(tempTable==null || tempTable.trim().length()==0)
			{
				tem.releaseTempTable(tempTable);
			}
//		} else {
			// 1.应付单反写采购
			updatePurByAP(ctx, pk, isAudit);
//		}
*/	}