动态执行sql,游标的使用方法

----------------------传值-------------------------------------

IN _prod_name varchar(50), IN _class_id int, IN _attr_sql varchar(1000)

 

_attr_sql  传值 如下示例：

SELECT 1 AS attr_id,'32开' as attr_value,30 as prod_price UNION SELECT 2 AS attr_id,'64开' as attr_value,40 as prod_price

----------------------存储过程--------------------------------

BEGIN

  DECLARE isend int DEFAULT 0;
  DECLARE _prod_id int;  
  DECLARE _attr_id int;
  DECLARE _attr_value varchar(20);
  DECLARE _prod_price DECIMAL(5,2);
  DECLARE cur CURSOR FOR  select  prod_id,attr_id,attr_value,prod_price from temp_a;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend = 1;

    SET @pid=0;
    INSERT INTO prod_main(prod_name,prod_classid) VALUES(_prod_name,_class_id);#商品主表插入数据
    SET @pid=LAST_INSERT_ID();#获取上一条语句执行返回的主键ID

   #生成临时表 将sql语句中的数据插入其中

    DROP TEMPORARY TABLE IF EXISTS temp_a;

-------------------------动态执行SQL语句-----------BEGIN---------------------------------------------
    SET@ss=CONCAT('CREATE TEMPORARY TABLE temp_a as SELECT ? as prod_id,a.* FROM (',_attr_sql,' ) a');
    PREPARE pname FROM @ss;

   #select 中的？ 替换掉 若有多个？ 如：? as prod_id1，? as prod_id2 则语句为EXECUTE pname USING @pid,@pid2;
    EXECUTE pname USING @pid;
    DEALLOCATE PREPARE pname;

-------------------------动态执行SQL语句-----------END---------------------------------------------

-------------------------游标-----------BEGIN---------------------------------------------
    OPEN cur;
    fetch cur into   _prod_id,_attr_id,_attr_value,_prod_price;
    WHILE isend != 1 DO
        insert into prod_attr(prod_id,attr_id,attr_value) values(_prod_id,_attr_id,_attr_value); #插入商品属性表
        SET @getid = LAST_INSERT_ID();
        insert into prod_price(prod_id,prod_attr_id,prod_price) values(_prod_id,@getid,_prod_price);
        FETCH cur INTO _prod_id,_attr_id,_attr_value,_prod_price;
    END WHILE;
    CLOSE cur;

    -------------------------游标-----------END---------------------------------------------
    DROP TEMPORARY TABLE temp_a;#删除临时表 释放占用资源
END