本文共 1585 字,大约阅读时间需要 5 分钟。
-
-
- create procedure p_procedurecode(in sumdate varchar(10))
- begin
- declare v_sql varchar(500);
- declare sym varchar(6);
-
- declare var1 varchar(20);
- declare var2 varchar(70);
- declare var3 integer;
-
-
- declare no_more_departments integer DEFAULT 0;
-
-
- DECLARE C_RESULT CURSOR FOR
- SELECT barcode,barname,barnum FROM tmp_table;
-
-
- DECLARE CONTINUE HANDLER FOR NOT FOUND
- SET no_more_departments=1;
-
- set sym=substring(sumdate,1,6);
-
-
-
-
- set v_sql= concat('Create TEMPORARY Table tmp_table(select aa as aacode,bb as aaname,count(cc) as ccnum from h',sym,' where substring(dd,1,8)=''',sumdate,''' group by aa,bb)');
-
- set @v_sql=v_sql;
- prepare stmt from @v_sql;
- EXECUTE stmt;
- deallocate prepare stmt;
-
- OPEN C_RESULT;
- REPEAT
- FETCH C_RESULT INTO VAR1, VAR2, VAR3;
-
-
- select @oldaacode:=vcaaCode from T_sum where vcaaCode=var1 and dtDate=sumdate;
- if @oldaacode=var1 then
- update T_sum set iNum=var3 where vcaaCode=var1 and dtDate=sumdate;
- else
- insert into T_sum(vcaaCode,vcaaName,iNum,dtDate) values(var1,var2,var3,sumdate);
- end if;
- UNTIL no_more_departments END REPEAT;
- CLOSE C_RESULT;
-
- DROP TEMPORARY TABLE tmp_table;
- end;
转载地址:http://qxkai.baihongyu.com/