dbWriter

<?xml version="1.0" encoding="GBK"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="AIMS.AccountApplyDao">
 <!-- 分页 start -->
 <sql id="pagingStartSql">
 <isNotNull property="paging">
 SELECT * FROM (SELECT N.*,ROWNUM AS R
 FROM (
 </isNotNull>
 </sql>
 
 <!-- 分页 end -->
 <sql id="pagingEndSql">
 <isNotNull property="paging">
 ) N WHERE ROWNUM <![CDATA[<=]]> (#paging.endRow#)) T
 WHERE T.R > #paging.startRow#
 </isNotNull>
 </sql>
 
 <!-- 查询 账户申请工单表公共SQL-->
 <sql id="queryAccountApplyScopeSQL">
 <isNotEmpty property="fmId" prepend="and">
 AAA.FMID = #fmId#
 </isNotEmpty>
 ...
 <isNotEmpty property="abolishmentFlag" prepend="and">
 AAA.ABOLISHMENT_FLAG = #abolishmentFlag#
 </isNotEmpty>
 <isNotEmpty property="isSendN9" prepend="and">
 AAA.IS_SEND_N9 = #isSendN9#
 </isNotEmpty>
 </sql>
 
 <!-- 显示 账户申请工单表公共SQL-->
 <sql id="showAccountApplyViewSQL">
 AAA.FMID AS "fmId",
 AAA.APPLYID AS "applyId",
 	...
 AAA.ABOLISHMENT_FLAG AS "abolishmentFlag",
 AAA.IS_SEND_N9 AS "isSendN9"
 </sql>
 <!-- 新增账户申请工单表 -->
 <insert id="saveAccountApply" parameterClass="com.nstc.aims.model.AccountApply">
 <selectKey resultClass="java.lang.Integer" keyProperty="fmId">
 SELECT AIMS_ACCOUNT_APPLY_SEQ.NEXTVAL AS fmId FROM DUAL
 </selectKey>
 INSERT INTO AIMS_ACCOUNT_APPLY (
 FMID,
 APPLYID,
 ASSID,
 	...
 IS_SEND_N9
 ) VALUES(
 #fmId#,
 #applyId#,
 #assId#,
 #accountId#,
 #cltNo#,
 	...
 #isSendN9#
 )
 </insert>
 <!-- 修改账户申请工单表 -->
 <update id="updateAccountApply" parameterClass="com.nstc.aims.model.AccountApply">
 UPDATE AIMS_ACCOUNT_APPLY AAA SET
 AAA.FMID = #fmId#
 <isNotNull prepend="," property="applyId">
 AAA.APPLYID = #applyId#
 </isNotNull>
 	...
 <isNotNull prepend="," property="isSendN9">
 AAA.IS_SEND_N9 = #isSendN9#
 </isNotNull>
 WHERE AAA.FMID = #fmId#
 </update>
 <!-- 查询账户申请工单表列表 -->
 <select id="getAccountApplyList" parameterClass="com.nstc.aims.model.scope.AccountApplyScope" resultClass="com.nstc.aims.model.view.AccountApplyView">
 SELECT
 <include refid="showAccountApplyViewSQL" />
 FROM AIMS_ACCOUNT_APPLY AAA WHERE 1 = 1
 <include refid="queryAccountApplyScopeSQL" />
 ORDER BY AAA.FMID ASC
 </select>
 <!-- 根据条件查询账户申请工单表View的第一条记录 -->
 <select id="getAccountApplyByScope" parameterClass="com.nstc.aims.model.scope.AccountApplyScope" resultClass="com.nstc.aims.model.view.AccountApplyView">
 SELECT
 <include refid="showAccountApplyViewSQL" />
 FROM AIMS_ACCOUNT_APPLY AAA WHERE 1 = 1
 <include refid="queryAccountApplyScopeSQL" />
 AND ROWNUM = 1
 </select>
 
 <!-- 根据Id集合删除账户申请工单表 -->
 <delete id="deleteAccountApplyByIds" parameterClass="java.util.ArrayList">
 DELETE FROM AIMS_ACCOUNT_APPLY AAA WHERE AAA.FMID IN
 <iterate open="(" close=")" conjunction=","> 
 #ids[]# 
 </iterate>
 </delete> 
 <!-- 查询账户申请工单表记录数 -->
 <select id="getAccountApplyPageCount" parameterClass="com.nstc.aims.model.scope.AccountApplyScope" resultClass="java.lang.Integer">
 SELECT
 COUNT(0)
 FROM AIMS_ACCOUNT_APPLY AAA WHERE 1 = 1
 <include refid="queryAccountApplyScopeSQL" />
 </select> 
 
 <!-- 查询账户申请工单表分页列表 -->
 <select id="getAccountApplyPageList" parameterClass="com.nstc.aims.model.scope.AccountApplyScope" resultClass="com.nstc.aims.model.view.AccountApplyView">
 <include refid="pagingStartSql" />
 SELECT
 <include refid="showAccountApplyViewSQL" />
 FROM AIMS_ACCOUNT_APPLY AAA WHERE 1 = 1
 <include refid="queryAccountApplyScopeSQL" />
 ORDER BY AAA.FMID ASC
 <include refid="pagingEndSql" />
 </select>
 <!-- 删除账户申请工单表 -->
 <delete id="deleteAccountApply" parameterClass="com.nstc.aims.model.scope.AccountApplyScope">
 DELETE AIMS_ACCOUNT_APPLY AAA WHERE 1 = 1
 <include refid="queryAccountApplyScopeSQL" />
 </delete> 
 
 <!-- 保存或修改账户申请工单表集合 --> 
 <update id="saveOrUpdateAccountApplyList" parameterClass="java.util.ArrayList">
 MERGE INTO AIMS_ACCOUNT_APPLY AAA 
 USING
 <iterate open="(" close=") A" conjunction="UNION"> 
 SELECT
 <!-- 主键为空时候返回字符串,与原类型不匹配,非字符串类型都需要做转换 -->
 DECODE(#list[].fmId#,NULL,NULL,#list[].fmId#) AS FMID,
 DECODE(#list[].applyId#,NULL,NULL,#list[].applyId#) AS APPLYID,
 ...
 DECODE(#list[].abolishmentFlag#,NULL,NULL,#list[].abolishmentFlag#) AS ABOLISHMENT_FLAG,
 DECODE(#list[].isSendN9#,NULL,NULL,#list[].isSendN9#) AS IS_SEND_N9
 FROM DUAL
 </iterate>
 ON (A.FMID = AAA.FMID)
 WHEN MATCHED THEN
 UPDATE SET
 AAA.APPLYID = A.APPLYID,
 AAA.ASSID = A.ASSID,
 	...
 AAA.ABOLISHMENT_FMID = A.ABOLISHMENT_FMID,
 AAA.ABOLISHMENT_REASON = A.ABOLISHMENT_REASON,
 AAA.ABOLISHMENT_FLAG = A.ABOLISHMENT_FLAG,
 AAA.IS_SEND_N9 = A.IS_SEND_N9
 WHEN NOT MATCHED THEN 
 INSERT (
 FMID,
 APPLYID,
 	...
 ABOLISHMENT_REASON,
 ABOLISHMENT_FLAG,
 IS_SEND_N9
 ) VALUES (
 AIMS_ACCOUNT_APPLY_SEQ.NEXTVAL,
 A.APPLYID,
 A.ASSID,
 	...
 A.IS_SEND_N9
 ) 
 </update>
 
 <!-- 保存或修改账户申请工单表实体 --> 
 <update id="saveOrUpdateAccountApply" parameterClass="com.nstc.aims.model.AccountApply">
 MERGE INTO AIMS_ACCOUNT_APPLY AAA
 USING DUAL
 ON (AAA.FMID = #fmId#)
 WHEN MATCHED THEN
 UPDATE
 SET
 AAA.APPLYID = #applyId#,
 AAA.ASSID = #assId#,
 ...
 AAA.IS_SEND_N9 = #isSendN9#
 WHEN NOT MATCHED THEN
 INSERT (
 FMID,
 APPLYID,
 	...
 ABOLISHMENT_FLAG,
 IS_SEND_N9
 ) 
 VALUES (
 AIMS_ACCOUNT_APPLY_SEQ.NEXTVAL,
 #applyId#,
 #assId#,
 ...
 #isSendN9#
 )
 </update>
 
</sqlMap>

DECLARE
 CNT INTEGER;
BEGIN
 SELECT COUNT(0) INTO CNT FROM USER_ALL_TABLES
 WHERE TABLE_NAME = UPPER('G_REMIND_BUSS');
 IF CNT = 0 THEN
 EXECUTE IMMEDIATE 'CREATE TABLE G_REMIND_BUSS(
 "RB_ID" NUMBER ,
 "BUSS_CODE" VARCHAR2(64) NOT NULL,
 "BUSS_NAME" VARCHAR2(64) NOT NULL,
 "REMARKS" VARCHAR2(128) ,
 "ORDER_NUM" NUMBER DEFAULT ON NULL 0 NOT NULL,
 "APPNO" VARCHAR2(32) ,
 "CREATE_TIME" DATE DEFAULT ON NULL SYSDATE ,
 "UPDATE_TIME" DATE DEFAULT ON NULL SYSDATE 
 )';
 EXECUTE IMMEDIATE 'COMMENT ON TABLE G_REMIND_BUSS IS ''提醒表''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."RB_ID" IS ''主键''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."BUSS_CODE" IS ''提醒编号''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."BUSS_NAME" IS ''提醒名称''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."REMARKS" IS ''备注''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."ORDER_NUM" IS ''排序''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."APPNO" IS ''模块编号''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."CREATE_TIME" IS ''创建时间''';
 EXECUTE IMMEDIATE 'COMMENT ON COLUMN G_REMIND_BUSS."UPDATE_TIME" IS ''修改时间''';
 END IF;
END;
/
DECLARE
 CNT INTEGER;
BEGIN
 SELECT COUNT(0) INTO CNT FROM USER_CONSTRAINTS
 WHERE CONSTRAINT_NAME = UPPER('G_REMIND_BUSS_PK');
 IF CNT = 0 THEN
 EXECUTE IMMEDIATE 'ALTER TABLE G_REMIND_BUSS ADD CONSTRAINT G_REMIND_BUSS_PK PRIMARY KEY("RB_ID")';
 END IF;
END;
/