需求：达梦数据库，每个省份有对应的一张表，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