DELIMITER $$
/*DROP PROCEDURE IF EXISTS `automachine`.`PRO_PAGER`*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `automachine`.`PRO_PAGER`(
IN p_table_name VARCHAR(1000),
IN p_fields VARCHAR(1000),
IN p_page_size INT,
IN p_page_now INT,
IN p_order_string VARCHAR(128),
IN p_where_string VARCHAR(1000),
OUT p_out_rows INT)
BEGIN DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
SET m_begin_row = (p_page_now - 1) * p_page_size;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size); SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET p_out_rows = @ROWS_TOTAL; PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END $$DELIMITER ;刚接触MYSQL……
以上是一个分页存储过程,COPY过来的, 我在使用MYSQL query brower ,问题是不知道如何调用参数,执行它,得到结果
/*DROP PROCEDURE IF EXISTS `automachine`.`PRO_PAGER`*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `automachine`.`PRO_PAGER`(
IN p_table_name VARCHAR(1000),
IN p_fields VARCHAR(1000),
IN p_page_size INT,
IN p_page_now INT,
IN p_order_string VARCHAR(128),
IN p_where_string VARCHAR(1000),
OUT p_out_rows INT)
BEGIN DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
SET m_begin_row = (p_page_now - 1) * p_page_size;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size); SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET p_out_rows = @ROWS_TOTAL; PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END $$DELIMITER ;刚接触MYSQL……
以上是一个分页存储过程,COPY过来的, 我在使用MYSQL query brower ,问题是不知道如何调用参数,执行它,得到结果
解决方案 »
- 300名医师的医院,用什么级别的数据库服务器?
- Server characterset和Db characterset,这俩货之间神马关系?求知道:)
- 怎样优化数据查询语句?
- 从mysql 中取出 最后十条 msg = 101 的记录
- 用MySql怎么根据时间查询前一天的数据,在线等,急、、、、
- mysql: 将数据库内数据根据条件读入内存,竟然花了3个小时,请教。。。
- 我用php程序连接 mysql 显示文章,为什么全为 ? 输出
- 请教一个复杂的SQL语句
- 请问:如何快速更新大量的记录?
- 80分,我想取得刚存入表中记录的id号?如何做,该id是auto_increment,所以只好等存入后再取出,最正确的最法?
- MYSQL怎么验证,某个字段的值里,存在不存在"G"这个字母,
- 数据表中的索引id如何重置?
SET @p_page_size=20;
SET @m_limit_string=CONCAT(' LIMIT ', 1, ', ', 20);
SET @p_where_string='WHERE ID > 0';
SET @p_fields='*';SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', 'record', ' ', 'where id>0');
SET @MAIN_STRING = CONCAT('SELECT ', '*', ' FROM ', 'record', ' ', 'where id>0', ' ', 'order by id ', @m_limit_string);CALL `automachine`.`PRO_PAGER`('record',@p_fields,20,5,'order by id ', @p_where_string ,@ROWS_TOTAL);
SELECT @ROWS_TOTAL;
这样写,没有结果显示……
看下这个例子