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