CREATE DATABASE `test_paging` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
use test_paging;
create table tTemp
(
Sn int not null,
Str varchar(10) not null
);
INSERT INTO tTemp (Sn, Str) VALUES (1, 'A');
INSERT INTO tTemp (Sn, Str) VALUES (1, 'B');
INSERT INTO tTemp (Sn, Str) VALUES (1, 'C');
INSERT INTO tTemp (Sn, Str) VALUES (1, 'D');
INSERT INTO tTemp (Sn, Str) VALUES (2, 'A');
INSERT INTO tTemp (Sn, Str) VALUES (2, 'B');
INSERT INTO tTemp (Sn, Str) VALUES (2, 'C');
INSERT INTO tTemp (Sn, Str) VALUES (2, 'D');
INSERT INTO tTemp (Sn, Str) VALUES (3, 'A');
INSERT INTO tTemp (Sn, Str) VALUES (3, 'B');
INSERT INTO tTemp (Sn, Str) VALUES (3, 'C');
INSERT INTO tTemp (Sn, Str) VALUES (3, 'D');
use test_paging;
drop procedure if exists pList_Get;
DELIMITER //
CREATE PROCEDURE pList_Get(
IN prmPage int
,IN prmListSize int
,IN prmSearchType int
,IN prmSearchWord varchar(100)
,OUT RtnVal int
,OUT RtnCode int
)
root:BEGIN
/*******************************************************************************************************
Name : pList_Get
Description :
--------------------------------------------------------------------------------------------------------
ReturnValue,コード定義
0 : 成功
-1 : 処理失敗。
--------------------------------------------------------------------------------------------------------
作業日 作業者 作業内容
2022-01-01 inno 新規作成
--------------------------------------------------------------------------------------------------------
使用例
DELIMITER ;
caLL pList_Get(1, 3, 1, '', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(2, 3, 1, '', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(3, 3, 1, '', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(4, 3, 1, '', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(5, 3, 1, '', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(1, 2, 1, 'B', @RtnVal, @RtnCode);
DELIMITER ;
caLL pList_Get(2, 2, 1, 'B', @RtnVal, @RtnCode);
SELECT @RtnCode AS RtnCode, @RtnVal AS RtnVal;
SELECT * FROM tTemp;
*******************************************************************************************************/
DECLARE prmStrSearch varchar(1000);
SET prmStrSearch = CONCAT(' 1=1 ');
-- SearchType : 1 (Str)
IF (length(prmSearchWord) > 0 ) THEN
BEGIN
IF (prmSearchType = 1) THEN
SET prmStrSearch = CONCAT(prmStrSearch, ' AND B.Str like ''%', prmSearchWord, '%'' ');
END IF;
END;
END IF;
-- #### TOTAL件数取得 #### START #################################################
SET @StrSQL := ('SELECT COUNT(Sn) INTO @RtnVal FROM tTemp AS B');
SET @StrSQL := CONCAT(@StrSQL , ' WHERE ' , prmStrSearch , ';');
PREPARE stmt FROM @StrSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- #### TOTAL件数取得 #### END #################################################
SET @StrSQL := CONCAT('SELECT ROW_NUMBER() OVER (ORDER BY B.Sn ASC, B.Str ASC) Num
, B.* FROM
(
SELECT Sn, Str FROM
(
SELECT Sn, Str
FROM tTemp B
WHERE ' , prmStrSearch , '
ORDER BY Sn DESC, Str desc limit ' , ((prmPage -1) * prmListSize + 1) , '
) A
ORDER BY Sn ASC, Str ASC limit 1
) A JOIN tTemp B
ON (A.Sn != B.Sn and A.Sn >= B.Sn) OR (A.Sn >= B.Sn and A.Str >= B.Str)
WHERE ' , prmStrSearch , '
ORDER BY Sn DESC, Str DESC limit ', prmListSize ,'');
PREPARE stmt FROM @StrSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET RtnVal = @RtnVal;
SET RtnCode = 0;
END
//
DELIMITER ;
DELIMITER ;
caLL pList_Get(1, 3, 1, '', @RtnVal, @RtnCode);
caLL pList_Get(2, 3, 1, '', @RtnVal, @RtnCode);
caLL pList_Get(3, 3, 1, '', @RtnVal, @RtnCode);
caLL pList_Get(4, 3, 1, '', @RtnVal, @RtnCode);
caLL pList_Get(5, 3, 1, '', @RtnVal, @RtnCode);