如何在MYSQL query brower 中执行这个MYSQL存储过程

xiaotiange 2011-08-06 01:52:20

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 ，问题是不知道如何调用参数，执行它，得到结果

...全文

181 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiaotiange 2011-08-08

打赏
举报

[Quote=引用 4 楼 rucypli 的回复:]
http://www.blogjava.net/nonels/archive/2008/10/09/233324.html
看下这个例子
[/Quote]
看例子，懂了

rucypli 2011-08-06

打赏
举报

http://www.blogjava.net/nonels/archive/2008/10/09/233324.html
看下这个例子

xiaotiange 2011-08-06

打赏
举报

SET @p_page_now=1;

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;

这样写，没有结果显示……

xiaotiange 2011-08-06