Oracle_WHERE 1=1

WHERE 1=1

동적 쿼리에서 특정상황마다 WHERE절을 다르게 작성해줘야 할 때 편리하다. 

 

동적 쿼리에서 if문을 사용하여 조건을 특정상황마다 바꾸고 싶을 때 WHERE 1=1 선언 후 AND 조건을 적어주면 된다.

단, 일반 쿼리문 작성시 WHERE 조건의 1=1은 무의미하다.

 

 

 

번호, 이름, 시작일, 마지막일 조회하는 기능

<sql id="list-where">
	<if test="prNo != null and prNo != ''">
		AND PM.PR_NO like '%' || upper(#{prNo}) || '%'
	</if>
	<if test="prCmpyCd != null and prCmpyCd != ''">
		AND PM.PR_CMPY_CD like '%' || #{prCmpyCd} || '%'
	</if>
	<if test="prStartDt != null and prStartDt != ''">
		<![CDATA[AND PM.PR_START_DT >= #{prStartDt}]]>
	</if>
	<if test="prEndDt != null and prEndDt != ''">
		<![CDATA[AND PM.PR_END_DT <= #{prEndDt}]]>
	</if>
</sql>

 

 

<select id="selectListPdMnm" parameterType="com.abc.def.domain.PdMnmVO" resultType="com.abc.def.domain.PdMnmVO">
	<include refid="com.abc.def.mapper.CommonMapper.sqlPagingHeader"/>
	    SELECT PM.PR_NO AS prNo
		   , PM.PR_CMPY_CD AS prCmpyCd
                   , CD.DESC_KOR AS prCmpyNm
		   , TO_CHAR(PM.PR_START_DT, 'yyyy-mm-dd') AS prStartDt
		   , TO_CHAR(PM.PR_END_DT, 'yyyy-mm-dd') AS prEndDt
		   , PM.PR_TITLE AS prTitle
		   , TO_CHAR(PM.UPDT_DT, 'yyyy-mm-dd') AS updtDt
		   , PM.UPDT_EMPNO AS updtEmpno
		   , TO_CHAR(PM.CRTN_DT, 'yyyy-mm-dd') AS crtnDt
		   , PM.CRTR_EMPNO AS crtrEmpno
		   , PM.PR_DESCRIPTION AS prDescription
	    FROM PR_MNM PM
            LEFT OUTER JOIN CODE_DTL CD
            ON PM.PR_CMPY_CD = CD.CD_DATA
            AND CD.CD_TYPE = 'PO'
            WHERE 1=1
        <include refid="list-where"/>
	<include refid="com.abc.def.mapper.CommonMapper.sqlPagingFooter"/>
</select>