最近参与一个BI项目，在将ODS区中数据分流适配到F明细区中时，运用到了BULK COLLECT、FORALL和INSERT ALL用来提高性能。关键语句如下：
[@more@]最近参与一个BI项目，在将ODS区中数据分流适配到F明细区中时，运用到了BULK COLLECT、FORALL和INSERT ALL用来提高性能。关键语句如下：
select PLATFORMID,PRODUCTID,IP,DEAL_DATE,SUB_PATH,URL_PARA,DOMAIN_NAME,REQ_CODE,SEND_BYTE,UAID,UASTR,GATEWAY,
MSISDN,MID,CITYID,VISIT_PERIOD,PROVINCEID,BRAND_ID
BULK COLLECT INTO PLATFORMIDS,PRODUCTIDS,IPS,DEAL_DATES,SUB_PATHS,URL_PARAS,DOMAIN_NAMES,REQ_CODES,SEND_BYTES,UAIDS,UASTRS,GATEWAYS,
MSISDNS,MIDS,CITYIDS,VISIT_PERIODS,PROVINCEIDS,BRAND_IDS --LIMIT 10000
from O_APACHE_LOG
WHERE m_datefrom<= DEAL_DATE AND DEAL_DATE < m_dateto; -- 分流适配
v_SQLERRLOG:='INSERT ALL INTO TMP_UC_EXPLORE_D,TMP_UC_INTERACT_D,TMP_UC_DOWNLOAD_D,TMP_UC_FORECAST_D,TMP_UC_HELPFUL_D,TMP_UC_SEARCH_D';
if(sql%rowcount>0) then --有数据
FORALL i IN 1 .. PLATFORMIDS.COUNT
INSERT ALL
WHEN substr(UC_CONDITION_CODE,1,4)='UC01' THEN --浏览类
INTO TMP_UC_EXPLORE_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
WHEN substr(UC_CONDITION_CODE,1,4)='UC02' THEN --互动类
INTO TMP_UC_INTERACT_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
WHEN substr(UC_CONDITION_CODE,1,4)='UC03' THEN --下载类
INTO TMP_UC_DOWNLOAD_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
WHEN substr(UC_CONDITION_CODE,1,4)='UC04' THEN --测算类
INTO TMP_UC_FORECAST_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
WHEN substr(UC_CONDITION_CODE,1,4)='UC05' THEN --实用类
INTO TMP_UC_HELPFUL_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
WHEN substr(UC_CONDITION_CODE,1,4)='UC06' THEN --搜索类
INTO TMP_UC_SEARCH_D(STAT_TIME,MOBILE,BRAND_ID,PROVINCEID,CITYID,CLASS_STR1,CLASS_STR2,CLASS_STR3)
VALUES(DEAL_DATES(i),MSISDNS(i),BRAND_IDS(i),PROVINCEIDS(i),CITYIDS(i),UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3)
SELECT UC_CONDITION_CODE,UC_CLASS_STR1,UC_CLASS_STR2,UC_CLASS_STR3
FROM D_UC_APACHE_CONFIG
WHERE CONDITION_CLAUSE;
供大家学习和引用！