Mysql存储过程

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

存储过程调用

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