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

页面导航:首页 > 数据库 > Mysql > MySQL带INOUT参数执行搜索提示的存储过程

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

来源: 作者: 时间:2016-02-17 10:07 【

MySQL带INOUT参数执行搜索提示的存储过程[sql] DELIMITER $$ USE `b10k`$$ DROP PROCEDURE IF EXISTS `sp_suggest`$$ CREATE DEFINER=`luth`@`%` PROCEDURE `sp_suggest`( ...
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;  

 

 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<