2014/04/10 - [프로그램 자료/MS-SQL] - [MS-SQL]동적조회 조건에서의 정적 프로시져와 동적프로시져 Dynamic Where

2018/07/27 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 페이지네이션 / 페이징 처리 pagination, paging 쿼리

2018/08/08 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 동적쿼리 사용시 in clause 사용하기

2018/09/05 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 하위부서 포함하여 검색하기

[개발환경]

mariadb 10.0.x

요새 한참 하이버네이트와 마리아디비로 제품을 만들다보니

mysql에서 동적 프로시저를 사용할 일이 생겼다.

샘플을 올리니 참고.

# 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 <= l.starttime and l.endtime <= @endDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@startDate >= l.starttime and l.endtime >= @startDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@endDate >= l.starttime and l.endtime >= @endDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@startDate >= l.starttime and l.endtime >= @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 <= l.starttime and l.endtime <= @endDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@startDate >= l.starttime and l.endtime >= @startDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@endDate >= l.starttime and l.endtime >= @endDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," or (@startDate >= l.starttime and l.endtime >= @endDate)");

SET @SQL = CONCAT(@SQL, CHAR(10)," )");

PREPARE dquery FROM @Sql;

EXECUTE dquery;

DEALLOCATE PREPARE dquery;

SET totalCount := @totalCount;

END$$

DELIMITER ;

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