<a href="http://motolies.tistory.com/340" target="_blank">2014/04/10 - [프로그램 자료/MS-SQL] - [MS-SQL]동적조회 조건에서의 정적 프로시져와 동적프로시져 Dynamic Where</a><a href="http://motolies.tistory.com/700" target="_blank">2018/07/27 - [프로그램 자료/MySQL &amp; MariaDB] - [MySQL &amp; MariaDB] 페이지네이션 / 페이징 처리 pagination, paging 쿼리</a><a href="http://motolies.tistory.com/706" target="_blank">2018/08/08 - [프로그램 자료/MySQL &amp; MariaDB] - [MySQL &amp; MariaDB] 동적쿼리 사용시 in clause 사용하기</a>&nbsp;<a href="http://motolies.tistory.com/710" target="_blank">2018/09/05 - [프로그램 자료/MySQL &amp; MariaDB] - [MySQL &amp; MariaDB] 하위부서 포함하여 검색하기</a>&nbsp;&nbsp;&nbsp;[개발환경]mariadb 10.0.x&nbsp;요새 한참 하이버네이트와 마리아디비로 제품을 만들다보니&nbsp;mysql에서 동적 프로시저를 사용할 일이 생겼다.&nbsp;&nbsp;샘플을 올리니 참고.&nbsp;&nbsp;&nbsp;<div style="background-color:#282c34;color:#abb2bf;"><pre style="font-family:'JetBrains Mono',monospace;font-size:9.4pt;"># call procName(null, null) DELIMITER $$ USE `demoDB`$$ DROP PROCEDURE IF EXISTS usp_test$$ CREATE DEFINER = 'demo_user'@'%' PROCEDURE usp_test( in pOffset int, in pSize int, in startDate date, in endDate date, out totalCount int ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '테스트' BEGIN SET @startDate := TIMESTAMP(IFNULL(startDate, DATE_ADD(NOW(), INTERVAL -1 MONTH))); IF (endDate IS NULL) THEN SET @endDate := DATE_ADD(DATE_ADD(NOW(), INTERVAL 1 DAY), INTERVAL -1 MICROSECOND); ELSE SET @endDate := DATE_ADD(DATE_ADD(endDate, INTERVAL 1 DAY), INTERVAL -1 MICROSECOND); END IF; SET @pOffset := IFNULL(pOffset, 0); SET @pSize := IFNULL(pSize, 10); SET @totalCount := 0; SET @Sql = ""; SET @Sql = CONCAT(@Sql, CHAR(10), "select * from test where 1=1"); SET @SQL = CONCAT(@SQL, CHAR(10), " and ("); SET @SQL = CONCAT(@SQL, CHAR(10), " (@startDate &lt;= l.starttime and l.endtime &lt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@startDate &gt;= l.starttime and l.endtime &gt;= @startDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@endDate &gt;= l.starttime and l.endtime &gt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@startDate &gt;= l.starttime and l.endtime &gt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " )"); SET @Sql = CONCAT(@Sql, CHAR(10), "limit ?,?"); PREPARE dquery FROM @Sql; EXECUTE dquery USING @pOffset, @pSize; SET @Sql = CONCAT("select count(*) into @totalCount from test where 1=1"); SET @SQL = CONCAT(@SQL, CHAR(10), " and ("); SET @SQL = CONCAT(@SQL, CHAR(10), " (@startDate &lt;= l.starttime and l.endtime &lt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@startDate &gt;= l.starttime and l.endtime &gt;= @startDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@endDate &gt;= l.starttime and l.endtime &gt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " or (@startDate &gt;= l.starttime and l.endtime &gt;= @endDate)"); SET @SQL = CONCAT(@SQL, CHAR(10), " )"); PREPARE dquery FROM @Sql; EXECUTE dquery; DEALLOCATE PREPARE dquery; SET totalCount := @totalCount; END$$ DELIMITER ;</pre></div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

[MySQL & MariaDB] 동적 프로시저 dynamic procedure - 기간 포함