[MySQL] Paging(ページング)処理 (同条件2つの場合)

inno
2023-04-10 17:15 395 0

[MySQL] Paging(ページング)処理 (同条件2つの場合)

 

1) テストデータベース及びデータ作成

 


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');


 

2) ページング処理関連SP

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 ;

 

 

3) SP実行

 

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);

コメント