Mysql存储过程的创建
-- 员工 分页存储过程start
DROP PROCEDURE IF EXISTS `pagebean`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pagebean`(
IN _content VARCHAR(500), /*查询的内容*/
IN _relationship VARCHAR(500), /*表关系*/
IN _order VARCHAR(20), /*排序内容*/
IN _pagecurrent INT, /*当前页*/
IN _pagesize INT, /*每页的记录数*/
IN _count VARCHAR(50) /*查询的某表总行数*/
)
COMMENT '分页存储过程'
BEGIN
IF _pagesize<=1 THEN
SET _pagesize=20;
END IF;
IF _pagecurrent < 1 THEN
SET _pagecurrent = 1;
END IF;
SET @strsql = CONCAT('SELECT ',_content,' FROM ',_relationship,' ORDER BY ',_order,' DESC LIMIT ',_pagecurrent*_pagesize-_pagesize,',',_pagesize);
PREPARE stmtsql FROM @strsql;
EXECUTE stmtsql;
DEALLOCATE PREPARE stmtsql;
SET @strsqlcount=CONCAT('SELECT COUNT(1) FROM ',_count) ;/*count(1) 这个字段最好是主键*/
PREPARE stmtsqlcount FROM @strsqlcount;
EXECUTE stmtsqlcount;
DEALLOCATE PREPARE stmtsqlcount;
END
;;
DELIMITER ;
-- 分页存储过程end
DROP PROCEDURE IF EXISTS `pagebean`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pagebean`(
IN _content VARCHAR(500), /*查询的内容*/
IN _relationship VARCHAR(500), /*表关系*/
IN _order VARCHAR(20), /*排序内容*/
IN _pagecurrent INT, /*当前页*/
IN _pagesize INT, /*每页的记录数*/
IN _count VARCHAR(50) /*查询的某表总行数*/
)
COMMENT '分页存储过程'
BEGIN
IF _pagesize<=1 THEN
SET _pagesize=20;
END IF;
IF _pagecurrent < 1 THEN
SET _pagecurrent = 1;
END IF;
SET @strsql = CONCAT('SELECT ',_content,' FROM ',_relationship,' ORDER BY ',_order,' DESC LIMIT ',_pagecurrent*_pagesize-_pagesize,',',_pagesize);
PREPARE stmtsql FROM @strsql;
EXECUTE stmtsql;
DEALLOCATE PREPARE stmtsql;
SET @strsqlcount=CONCAT('SELECT COUNT(1) FROM ',_count) ;/*count(1) 这个字段最好是主键*/
PREPARE stmtsqlcount FROM @strsqlcount;
EXECUTE stmtsqlcount;
DEALLOCATE PREPARE stmtsqlcount;
END
;;
DELIMITER ;
-- 分页存储过程end
存储过程调用
CALL pagebean('StaffNumber,NAME,Age,Sex,Phone,FixedTelephone,IDNumber,Address,EntryTime,Birthday,PlaceOfOrigin,Education,DepartmentName,PositionName',
'Staffs s INNER JOIN Department d ON s.DepartmentNumber=d.DepartmentNumber INNER JOIN Positions p ON s.PositionNumber = p.PositionNumber',
'Number',1,5,'Staffs');
'Staffs s INNER JOIN Department d ON s.DepartmentNumber=d.DepartmentNumber INNER JOIN Positions p ON s.PositionNumber = p.PositionNumber',
'Number',1,5,'Staffs');