MySQL带INOUT参数执行搜索提示的存储过程_MySQL

[sql] DELIMITER $$ USE `b10k`$$ DROP PROCEDURE IF EXISTS `sp_suggest`$$ CREATE DEFINER=`luth`@`%` PROCEDURE `sp_suggest`( IN query_column VARCHAR(100), /*被检索的字段名*/ IN keyword VARCHAR(100), /*检索的关键字*/ IN table_name VARCHAR(100), /*被检索的表名*/ INOUT result_count INT /*要取出匹配记录的数量*/ ) COMMENT '执行关键字搜索' BEGIN /*定义变量*/ DECLARE m_begin_row INT DEFAULT 0; DECLARE m_where_string CHAR(128); DECLARE m_order_string CHAR(128); DECLARE m_limit_string CHAR(); /*构造语句*/ SET m_begin_row = result_count; SET m_where_string = CONCAT(' WHERE ', query_column, ' LIKE /'', keyword, '%/' '); SET m_order_string = CONCAT(' ORDER BY ', query_column); SET m_limit_string = CONCAT(' LIMIT ', result_count); SET @COUNT_STRING = CONCAT('SELECT DISTINCT COUNT(*) INTO @ROWS_TOTAL FROM ', table_name, ' ', m_where_string, ' ', m_order_string, ' ', m_limit_string); SET @MAIN_STRING = CONCAT('SELECT DISTINCT ', query_column, ' FROM ', table_name, ' ', m_where_string, ' ', m_order_string, ' ', m_limit_string); /*预处理*/ PREPARE count_stmt FROM @COUNT_STRING; EXECUTE count_stmt; DEALLOCATE PREPARE count_stmt; SET result_count = @ROWS_TOTAL; PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; DEALLOCATE PREPARE main_stmt; END$$ DELIMITER ; /*调用*/ SET @aa=10; CALL sp_suggest('latin_name','A','species',@aa); SELECT @aa;