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 ,问题是不知道如何调用参数,执行它,得到结果
解决方案 »
- 在iframe 框,怎么能够让iframe 中的提示框在整个页面查看,而不是只是在那个框中
- You have an error in your SQL syntax; check the manual that corresponds to your
- 数据库中VARCHAR2类型的数据怎么按照实际数值比较大小呢?
- mysql load data infile 怎么跳过某一行
- 批量执行sql的问题
- 请教一个问题,期待过来人给带你建议……
- 存储过程列合并(postgres)
- 100分!在线等!
- 救命! 帮忙看看这个错误是什么原因引发的 , 急,很急,真的很急!
- 我怎样才能把一条记录通过程序插入mysql中的表呢?请教经验的朋友进来看看!
- 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;
这样写,没有结果显示……
看下这个例子