存储过程中分区不能用变量,比如:insert into t_aaa select * from t_bbb partition(v_变量); 必须用动态sql
动态 SQL,这个是必须的。 如下,系统认为 v_pname 是分区的名称。select * from mytable partition(v_pname);
统一回复一下,现在把sql给你们看: FOR CC IN (SELECT * FROM D) LOOP --插入数据 INSERT /*+ APPEND*/ INTO C NOLOGGING SELECT /*+PARALLEL(A,4)*/ ACCT_MON, A.USER_ID, A.DEVICE_NUMBER, A.FAV_FEE, B.INTER_NUMS, B.GJMY_NUMS FROM (SELECT /*+PARALLEL(T,4)*/ GATHER_MON, DEVICE_NUMBER, USER_ID, SUM(FAV_FEE) FAV_FEE FROM A T WHERE T.GATHER_MON = ACCT_MON AND AREA_NO = CC.AREA_NO GROUP BY GATHER_MON, USER_ID, DEVICE_NUMBER) A, (SELECT MONTH_ID, USER_ID, SUM(INTER_DURA) INTER_NUMS, SUM(ROAM_GAT_DURA) + SUM(ROAM_INTER_DURA) GJMY_NUMS FROM B@DB1 WHERE MONTH_ID = V_ACCT_MON AND AREA_NO = CC.AREA_NO GROUP BY MONTH_ID, USER_ID) B WHERE A.USER_ID = B.USER_ID(+); COMMIT; END LOOP;
必须用动态sql
如下,系统认为 v_pname 是分区的名称。select * from mytable partition(v_pname);
FOR CC IN (SELECT * FROM D) LOOP
--插入数据
INSERT /*+ APPEND*/
INTO C NOLOGGING
SELECT /*+PARALLEL(A,4)*/
ACCT_MON,
A.USER_ID,
A.DEVICE_NUMBER,
A.FAV_FEE,
B.INTER_NUMS,
B.GJMY_NUMS
FROM (SELECT /*+PARALLEL(T,4)*/
GATHER_MON, DEVICE_NUMBER, USER_ID, SUM(FAV_FEE) FAV_FEE
FROM A T
WHERE T.GATHER_MON = ACCT_MON
AND AREA_NO = CC.AREA_NO
GROUP BY GATHER_MON, USER_ID, DEVICE_NUMBER) A,
(SELECT MONTH_ID,
USER_ID,
SUM(INTER_DURA) INTER_NUMS,
SUM(ROAM_GAT_DURA) + SUM(ROAM_INTER_DURA) GJMY_NUMS
FROM B@DB1
WHERE MONTH_ID = V_ACCT_MON
AND AREA_NO = CC.AREA_NO
GROUP BY MONTH_ID, USER_ID) B
WHERE A.USER_ID = B.USER_ID(+);
COMMIT;
END LOOP;