BULK COLLECT、FORALL和INSERT ALL的综合运用案例

约定不等于承诺〃 2022-06-16 11:09 143阅读 0赞

最近参与一个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;

供大家学习和引用！