做项目用到了动态存储过程,记录一下写的方式:
declare citycode1 varchar(32);
declare code1 varchar(32);
DECLARE at_end INT DEFAULT 0;
declare sSql varchar(1000);
declare sSql2 varchar(1000);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
declare cur1 cursor for select citycode, code from ADM_BC_CU_DIQU where cnt <> 0 and amt < 0.005;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
open cur1;
INS_LOOP:
LOOP
FETCH cur1 INTO citycode1, code1;
if at_end = 1 then
LEAVE INS_LOOP;
END IF;
set sSql = 'update adm_bc_cu_diqu set amt = 0.01 where citycode = '''||citycode1||''' and code = '''||code1||'''';
PREPARE s1 FROM sSql;
EXECUTE s1;
set sSql2 = 'insert into pisa_opt_2.test(A,B,C) select INDICCODE, INDICCUBECODE, TARGET_EXPRESSION from pisa_opt_2.PISA_VALIDATION_TARGET where indicode = '''||INDI||''' and target_expression like ''%'||DN||'%'' and TARGET_REPORTID = ''H_CJBD-BC-DB''';
PREPARE s2 FROM sSql2;
EXECUTE s2;
END LOOP;
close cur1;