第一步:拼装字符串,用到concat('exper1','exper2');
作用:将exper1和exper2拼接到一起
eg:
第二步 :执行拼接完的字符串(执行的sql必须定义为 @sql 才能执行 否则报错)
案例:
DELIMITER //
CREATE PROCEDURE baseweb.delete_land11(IN str VARCHAR(40))
BEGIN
SET @str1='DELETE FROM baseweb.`landapply` WHERE la_id IN (';
SET @str1=CONCAT(@str1,str,')');
PREPARE stmt FROM @str1; //预编译sql语句
EXECUTE stmt;
DEALLOCATE PREPARE stmt; //释放sql
END
//
DELIMITER ;
注:如拼装中需要对变量赋值,用用户变量 @k
CREATE DEFINER=`root`@`%` PROCEDURE `insert_landapply`(IN userid VARCHAR(20),IN str VARCHAR(100),IN str1 VARCHAR(1000),OUT flag INT)
BEGIN
SET @str2='SELECT COUNT(*) INTO @numbers';
SET @str2=CONCAT(@str2,' FROM landapply WHERE applicantId=',userid,' AND lid IN ',str);
PREPARE stmt FROM @str2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @numbers=0 THEN
SET flag=1;
SET @str3='INSERT INTO landapply(lid,startTime,endTime,planting,applicantId,landapply.`status`,applyDept) VALUES';
SET @str3=CONCAT(@str3,str1);
PREPARE stmt1 FROM @str3;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
ELSE
SET flag=0;
END IF;
END$$
DELIMITER ;