需求：達夢資料庫，每個省份有對應的一張表，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
