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

bitsCN.com

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

 

[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(64);              /*构造语句*/          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;  

 

 

bitsCN.com