存储过程遍历

需求：达梦数据库，每个省份有对应的一张表，effective_11 ~ effective_65 差不多30多个表，里边存储的企业数据，现在要写一个存储过程，遍历查询这么多表并且求个数，最后以（地区code、数量）的方式插入到一个新表中！
新表字段（id，area_code，valid_num，create_time）

存储过程：

AS
	--声明地区数组
	type vcode is table of varchar;
	areaCodeARR vcode:=vcode('11','12','13','14','15','21','22','23','31','32','33','34','35',
	'36','37','41','42','43','44','45','46','50','51','52','53','54','61','62','63','64','65');
	numSql varchar;--求个数的sql
	insertSql varchar;-- 各省的添加sql
	totalSql varchar;-- 合计的sql
	effNum integer := 0;--各省的有效个数
	totalNum integer := 0;--合计
BEGIN
	for i in 1 .. areaCodeARR.count LOOP
	-- 求各省的有效个数，并累加给合计
	numSql :=' select count(1) from effective_'||areaCodeARR(i)||' where licence_type = 0';
	execute immediate numSql into effNum;
	totalNum := totalNum + effNum;
	-- 一个省给LICENCE_VALID表添加一条记录
	insertSql :='insert into LICENCE_VALID (AREA_CODE,VALID_NUM,CREATE_TIME)';
	insertSql := insertSql || ' select * from (select '||areaCodeARR(i)||'0000 as AREA_CODE,count(1) as VALID_NUM,SYSDATE as CREATE_TIME';
	insertSql := insertSql || ' from effective_'||areaCodeARR(i)||' where licence_type = 0)';
	execute immediate insertSql;
	end LOOP;
	-- 最后添加合计
	totalSql := 'insert into LICENCE_VALID (AREA_CODE,VALID_NUM,CREATE_TIME) values (''total'','||totalNum||',SYSDATE)';
	execute immediate totalSql;
	commit;
END

调用存储过程call proc_effective();

查询刚插入的表结果如下：select * from licence_valid where create_time=‘2020-01-15’;