欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-04-02 19:18:40
...
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