MySQL 存储过程中使用动态SQL语句示例

MySQL 存储过程中使用动态SQL语句示例

CREATE PROCEDURE CaluHejije(IN ind int,IN iyf int)

BEGIN
  DECLARE ihv INT;
    DECLARE sfname varchar(20);
    declare var_sql varchar(800);
    DECLARE jeheji decimal(18,2);
    DECLARE no_more INT DEFAULT 0;
    DECLARE my_cur CURSOR FOR 
        select field_name from cgform_field where type='BigDecimal' and table_id in(select id from cgform_head  where table_name='d_salaryinf');
    /* 将结束标志绑定到游标 */
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more = 1;

  select count(*) into ihv from d_salaryheji where dpyear=ind and dpmonth=iyf;
  if ihv<=0 then
         insert into d_salaryheji(id,sys_org_code,sys_company_code,bpm_status,dpyear,dpmonth)  
            select REPLACE(UUID(),'-',''),sys_org_code,sys_company_code,bpm_status,dpyear,dpmonth 
            from d_salaryinf where dpyear=ind and dpmonth=iyf limit 1;
  end if;

   OPEN my_cur;
  -- 开始循环
    read_loop: LOOP
     -- 提取游标里的数据
    FETCH my_cur INTO sfname; 
    -- 声明结束的时候
    IF no_more THEN
      LEAVE read_loop;
    END IF;

        set var_sql=CONCAT('select sum(',sfname,') into @para1 from d_salaryinf where dpyear=',ind,' and dpmonth=',iyf);
        set @sql = var_sql;
        PREPARE stmt1 FROM @sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
      SET jeheji=@para1;

        set var_sql=CONCAT('update d_salaryheji set ',sfname,'=',jeheji,' where dpyear=',ind,' and dpmonth=',iyf);
        set @sql = var_sql;
        PREPARE stmt1 FROM @sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

 END LOOP;
 -- 关闭游标 
 CLOSE my_cur;

END