存储过程加入动态sql

1.创建不带参数的存储过程

drop PROCEDURE if exists my_procedure; 

create PROCEDURE my_procedure() 
BEGIN
DECLARE my_sql VARCHAR(2000);
  set my_sql='SELECT order_info.* FROM order_info WHERE 1 = 1 '; 
 SET @sql1=my_sql;
 PREPARE stmt1 FROM @sql1;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1 ;
end;

CALL my_procedure();

2.创建带参数的存储过程

drop PROCEDURE if exists my_procedure; 

create PROCEDURE my_procedure(IN marketName VARCHAR(100)) 
BEGIN
DECLARE my_sql VARCHAR(2000);
  set my_sql='SELECT order_info.* FROM order_info WHERE 1 = 1 '; 
 IF marketName IS NOT NULL THEN
SET my_sql=CONCAT(my_sql,'AND order_info.market_name LIKE "%',marketName,'%" ');
 END IF;
 SET @sql1=my_sql;
 PREPARE stmt1 FROM @sql1;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1 ;
end;

CALL my_procedure('上海');

3.创建带多参数的单表存储过程

create PROCEDURE my_procedure(IN marketName VARCHAR(100),IN brandName VARCHAR(100),IN seriesName VARCHAR(100),
IN beginDate VARCHAR(100),IN endDate VARCHAR(100),IN startIndex INT(6),IN endIndex INT(6)) 
BEGIN
DECLARE my_sql VARCHAR(2000);
  set my_sql='SELECT order_info.* FROM order_info WHERE 1 = 1 '; 

IF (marketName <> NULL OR marketName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.market_name LIKE "%',marketName,'%" ');
END IF;

IF (brandName <> NULL OR brandName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.brand_name LIKE "%',brandName,'%" ');
END IF;

IF (seriesName <> NULL OR seriesName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.series_name LIKE "%',seriesName,'%" ');
END IF;

IF ((beginDate <> NULL OR beginDate <>'') AND (endDate <> NULL OR endDate <>'')) THEN
SET my_sql=CONCAT(my_sql,'and order_info.order_time between ',"'",beginDate,"'",' and ',"'",endDate,"'");
END IF;

SET my_sql=CONCAT(my_sql,' group by order_info.id order by order_info.market_no,order_info.order_sn ',' LIMIT ',startIndex,',',endIndex);

 SET @sql1=my_sql;
 PREPARE stmt1 FROM @sql1;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1;
end;

CALL my_procedure('上海','',NULL,'2015-06-14','2015-07-11',20,80);

4.创建带多参数的多表存储过程（本例为两表）

create PROCEDURE my_procedure(IN pdtType VARCHAR(100),IN pdtCode VARCHAR(100),IN pdtName VARCHAR(100),
IN marketName VARCHAR(100),IN brandName VARCHAR(100),IN seriesName VARCHAR(100),
IN beginDate VARCHAR(100),IN endDate VARCHAR(100),IN startIndex INT(6),IN endIndex INT(6)) 
BEGIN
DECLARE my_sql VARCHAR(2000);
  set my_sql='SELECT order_info.* FROM order_info '; 

IF ((pdtType <> NULL OR pdtType <>'') OR (pdtCode <> NULL OR pdtCode <>'') OR (pdtName <> NULL OR pdtName <>'')) THEN
SET my_sql=CONCAT(my_sql,'inner join order_item oi on oi.market_no = order_info.market_no and oi.order_sn =order_info.order_sn ');

IF (pdtType <> NULL OR pdtType <>'') THEN
SET my_sql=CONCAT(my_sql,'and oi.pdt_type LIKE "%',pdtType,'%" ');
END IF;

IF (pdtCode <> NULL OR pdtCode <>'') THEN
SET my_sql=CONCAT(my_sql,'and oi.pdt_code =',pdtCode);
END IF;

IF (pdtName <> NULL OR pdtName <>'') THEN
SET my_sql=CONCAT(my_sql,'and oi.pdt_name LIKE "%',pdtName,'%" ');
END IF;

END IF;

SET my_sql=CONCAT(my_sql,' WHERE 1 = 1 ');


IF (marketName <> NULL OR marketName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.market_name LIKE "%',marketName,'%" ');
END IF;

IF (brandName <> NULL OR brandName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.brand_name LIKE "%',brandName,'%" ');
END IF;

IF (seriesName <> NULL OR seriesName <>'') THEN
SET my_sql=CONCAT(my_sql,'AND order_info.series_name LIKE "%',seriesName,'%" ');
END IF;

IF ((beginDate <> NULL OR beginDate <>'') AND (endDate <> NULL OR endDate <>'')) THEN
SET my_sql=CONCAT(my_sql,'and order_info.order_time between ',"'",beginDate,"'",' and ',"'",endDate,"'");
END IF;

SET my_sql=CONCAT(my_sql,' group by order_info.id order by order_info.market_no,order_info.order_sn ',' LIMIT ',startIndex,',',endIndex);

 SET @sql1=my_sql;
 PREPARE stmt1 FROM @sql1;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1;
end;

CALL my_procedure('GP','015130844',NULL,'','',NULL,'2015-06-14','2015-07-11',0,80);

5.创建参数为DECIMAL类型的存储过程

create PROCEDURE my_procedure(IN minAmount DECIMAL(20,6),IN maxAmount DECIMAL(20,6)) 
BEGIN
DECLARE my_sql VARCHAR(2000);
  set my_sql='SELECT order_info.* FROM order_info WHERE 1 = 1 '; 
IF (minAmount IS NOT NULL) THEN
SET my_sql=CONCAT(my_sql,'AND order_info.order_amount>=',minAmount);
END IF;

IF (maxAmount IS NOT NULL) THEN
SET my_sql=CONCAT(my_sql,' AND order_info.order_amount<=',maxAmount);
END IF;

SET my_sql=CONCAT(my_sql,' group by order_info.id order by order_info.market_no,order_info.order_sn ');

 SET @sql1=my_sql;
 PREPARE stmt1 FROM @sql1;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1;
end;

CALL my_procedure(5000.999999,NULL);