mysql 存储过程动态执行sql 例子

来源：互联网发布：vs2010 mfc编程实例编辑：程序博客网时间：2024/05/20 05:26

DELIMITER $$;DROP PROCEDURE IF EXISTS `set_col_value`$$CREATE  PROCEDURE `set_col_value`   (InTargetYear     VARCHAR(128),         InTargetWeek     VARCHAR(128),        InCurrentYear    VARCHAR(1000),        InCurrentWeek    VARCHAR(4000)    )BEGIN   DECLARE sql_str TEXT;   SET sql_str = CONCAT('INSERT INTO     res_sales_hotlead(group_id,sales_id,customer_id,year,WEEK,createuser,createtime)SELECT t.group_id,t.sales_id,t.customer_id,',InTargetYear,',',InTargetWeek,',','t.createuser,NOW() FROM res_sales_hotlead t WHERE t.year = ',InCurrentYear,' AND t.week = ',InCurrentWeek,'');       SET @SQL=sql_str;   PREPARE s1 FROM @sql;   EXECUTE s1;   DEALLOCATE PREPARE s1;END$$DELIMITER ;$$

注意两个地方，@SQL 是系统内置的的一个函数，把要执行的动态sql 赋值给 @SQL 动态拼接的sql 要用 CONCAT 函数连接起来，如果拼接的 sql 中的变量要添加引号要用 CONCAT 函数处理例如

 SET v_sql_1= CONCAT('select count(a.concattype) from res_customer_info a left join car_staff b on b.staff_id=a.sales_id where 1=1 and a.concattype = 2 and a.auditflag = 1 and a.entitytype != 3 and UNIX_TIMESTAMP( a.createtime ) BETWEEN UNIX_TIMESTAMP(',CONCAT('"',starttime,'"'),') AND UNIX_TIMESTAMP(',CONCAT('"',endtime,'"'),')');

编译成功后call 存储过程名（参数一，参数二）;调试的时候可以把变量用 select 变量名; 的方式打印。