SQL codeCREATE OR REPLACE PROCEDURE thi_pro AS
randomnum NUMBER;
randomnum2 NUMBER;
randomnum3 NUMBER;
randomnum4 NUMBER;
randomnum5 NUMBER;
randomnum6 NUMBER;
BEGIN
--插入数据
FOR i IN REVERSE 1 .. 7 LOOP
randomnum := round(dbms_random.value(0, 100)); --基本通话费
randomnum2 := round(dbms_random.value(0, 100)); --长途费
randomnum3 := round(dbms_random.value(0, 100)); --短信费
randomnum4 := round(dbms_random.value(0, 100)); --漫游费
randomnum5 := round(dbms_random.value(0, 100)); --增值业务费
randomnum6 := round(dbms_random.value(0, 100)); --其他费用
INSERT /*+append*/
INTO VIP_RECKONING
SELECT seq_VIP_RECKONING.nextval,
v_phone_num,
trunc(add_months(SYSDATE, -i), 'MM'),
10,
randomnum,
randomnum2,
randomnum3,
randomnum4,
randomnum5,
randomnum6,
650,
0
FROM VIP_CUS_BASEINFO NOLOGGING;
END LOOP;
--建立索引
EXECUTE IMMEDIATE 'create index idx_PHONE_NUM on VIP_RECKONING(VIP_RECKONING)';
--更新
FOR i IN REVERSE 1 .. 7 LOOP
IF i = 7 THEN
UPDATE VIP_RECKONING v
SET v.n_live = 800 - 10 - N_BASERATE - N_LONGRATE - N_NOTERATE -
N_RAMBLERATE - N_INCREMENTRATE - N_OTHERRATE;
ELSE
UPDATE VIP_RECKONING v
SET v.n_live =
(SELECT 800 - 10 - v.N_BASERATE - v.N_LONGRATE - v.N_NOTERATE -
v.N_RAMBLERATE - v.N_INCREMENTRATE - v.N_OTHERRATE +
v1.n_live
FROM VIP_RECKONING v1
WHERE v1.phone_num = v.phone_num
AND v1.d_accountdate =
trunc(add_months(SYSDATE, -i - 1), 'MM'))
WHERE v.d_accountdate = trunc(add_months(SYSDATE, -i), 'MM');
END IF;
END LOOP;
COMMIT;
END;
------解决方案--------------------
这句就是：INSERT /*+append*/
是一种优化指示，写法类似注释
告诉ORACLE在插入数据时使用新块
