[ORACLE] 파이널 프로젝트 매치업 조회부분 sql 쿼리 짠 것

파이널 프로젝트, 에러와 좌절로 점철되어 있지만... 그래도 뿌듯한 걸 하나 기록. 

매치업 이력서 조회 시 더보기 버튼으로 페이징을 대신하고 검색기능을 추가했는데 그러다 보니까 sql부터 vo, 로직까지 전부 직접 짜게 되었다. 수업시간에 한 건 일반적인 페이징이었는데 왠지 재미없어보여서... 처음엔 이걸 내가 할 수 있을까, 했는데 하고 보니까 되게 뿌듯하고 마음에 든다.

실무에 뛰는 분들이야 이건 당연한거지, 하겠지만 난 처음이니깐 (._.

view 만드는 sql문 ▼

<!-- 검색용 view 0217 업데이트
	create or replace view matchupMemSearchView
	as
	select rownum as RN,  A.*
       from (
        select m.resume_no AS RESUMENO, m.mcumem_no as MCUMEMNO, m.mem_no AS MEMNO, m.expertise_no AS EXPERTISENO, 
            m.jobsearch_flag AS JOBSEARCHFLAG, m.resumeopen_flag AS RESUMEOPENFLAG, r.resume_title AS RESUMETITLE,
            r.resume_name AS RESUMENAME, DBMS_LOB.SUBSTR(r.resume_introduce, 4000, 1) AS RESUMEINTRODUCE, 
            r.lang_flag AS LANGFLAG, c.career_name AS CAREERNAME, e.edu_name AS EDUNAME, e.edu_major AS EDUMAJOR, 
            x.jikGun_code AS JIKGUNCODE, x.career AS CAREER, x.skill AS SKILL,
            (select jikmu_name from jikmu j where j.JIKMU_CODE=
  		    (select jikmu_code from matchupmemjikmu mjm where mjm.expertise_no=m.expertise_no and rownum=1)) as JIKMUNAME, 
     		(select jikgun_name from jikgun jk where jk.jikgun_code=
            (select jikgun_code from expertise e where e.expertise_no=m.expertise_no)) as JIKGUNNAME,
	        ROW_NUMBER() OVER(PARTITION BY m.mcumem_no ORDER BY r.resume_regdate) rcnt
        from matchupMem m join resume r on m.resume_No = r.resume_no 
        left outer join career c on c.resume_no=m.resume_no
        left outer join education e on e.resume_no=m.resume_no
        left outer join expertise x on x.expertise_no=m.expertise_no
        where m.resumeopen_flag='Y'
        )A
        where rcnt=1;
	 -->

 

view를 활용한 sql문. 매치업멤넘을 모아서 셀렉트하는거라 이렇게 되었다. ▼

<select id="selectMcumemSearchList" parameterType="matchupMemSearchVo" resultType="map">
		select  * from 
	    (
	    	select C.* from
		    (
		        select rownum as RN, 
	        	(select count(*) from matchuplike l where com_code=#{comCode} and l.mcumem_no=m.mcumemno) as CNT,
	        	m.* from matchupMemSearchView m
		        where m.mcumemNo in
		        <foreach collection="mcumemNoList" item="mcumemNo" open="(" close=")" separator=",">
					#{mcumemNo}
				</foreach>
	            and to_number(translate(career, '신입', '0')) between #{searchMinCareer} and #{searchMaxCareer}
			) C
			<![CDATA[
			where RN > #{viewMoreSize}
			and RN <= #{viewMoreSize}+5
			]]>
	    ) 
	</select>