create or replace procedure bill_gj_serv_prod is V_BILLING_PROD_ATTR VARCHAR2(60);
V_BILLING_PROD_ATTR_PARA VARCHAR2(60);
V_BILLING_PROD_ATTR_PARA_VALUE VARCHAR2(60);
V_CREATE_DATE DATE;
V_REMOVE_DATE DATE;
i number;
v_err_mss varchar2(400); CURSOR C2 IS
select B.SERV_ACC_NBR, C.PROD_ID, A.SERV_ID
from SERV_BACK_BILL a, serv_acc_nbr b, serv_billing_prod c
where a.serv_id = b.serv_id and
b.serv_id = c.serv_id;BEGIN
i := 0;
insert into bill_gj_log
select 'BEGIN', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'PTIME'
from dual;
FOR REC2 IN C2 LOOP
BEGIN
SELECT CREA_DATE
INTO V_CREATE_DATE
FROM SERV_REMOVE
WHERE SERV_ID = REC2.SERV_ID AND REMOVE_TYPE = 'BIL';
SELECT REMOVE_DATE
INTO V_REMOVE_DATE
FROM SERV_REMOVE
WHERE SERV_ID = REC2.SERV_ID AND REMOVE_TYPE = 'BIL';
SELECT BILLING_RULE_ID
INTO V_BILLING_PROD_ATTR
FROM pm_prod_billing
WHERE PROD_ID = REC2.PROD_ID;
select b.param_id
into V_BILLING_PROD_ATTR_PARA
from pm_prod_billing a, billing_rule_param b
where a.billing_rule_id = b.billing_rule_id(+) and
a.prod_id = rec2.prod_id;
select c.value
into V_BILLING_PROD_ATTR_PARA_VALUE
from pm_prod_billing a,
billing_rule_param b,
billing_rule_param_value c
where a.billing_rule_id = b.billing_rule_id(+) and
b.param_id = c.param_id(+) and a.prod_id = rec2.prod_id;
INSERT INTO GJ_SERV_BILLING_PROD(ACC_NBR,
PRODUCT_ID,
BILLING_PROD_ATTR,
BILLING_PROD_ATTR_PARA,
BILLING_PROD_ATTR_PARA_VALUE,
CREATE_DATE,
REMOVE_DATE)
VALUES(REC2.SERV_ACC_NBR,
REC2.PROD_ID,
V_BILLING_PROD_ATTR,
V_BILLING_PROD_ATTR_PARA,
V_BILLING_PROD_ATTR_PARA_VALUE,
V_CREATE_DATE,
V_REMOVE_DATE);
commit;
exception
when others then
v_err_mss := SQLERRM;
insert into bill_gj_log
select SERV_ID, v_err_mss, 'SERV_PROD'
from SERV_BACK_BILL
where serv_id = rec2.serv_id;
COMMIT;
END;
i := i + 1;
if mod(i, 100) = 0 then
COMMIT;
end if;
end loop;
insert into bill_gj_log
select 'END', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'PTIME'
from dual;
commit;
exception
when others then
v_err_mss := SQLERRM;
insert into bill_gj_log
select NULL, v_err_mss, 'OTHER' from dual;
commit;END bill_gj_serv_prod;以上是我的一个存储过程,但是运行以后,只能插入一条数据,估计是游标的问题,但是小弟郁闷,查不出是什么问题,请哪位大侠指点一下
V_BILLING_PROD_ATTR_PARA VARCHAR2(60);
V_BILLING_PROD_ATTR_PARA_VALUE VARCHAR2(60);
V_CREATE_DATE DATE;
V_REMOVE_DATE DATE;
i number;
v_err_mss varchar2(400); CURSOR C2 IS
select B.SERV_ACC_NBR, C.PROD_ID, A.SERV_ID
from SERV_BACK_BILL a, serv_acc_nbr b, serv_billing_prod c
where a.serv_id = b.serv_id and
b.serv_id = c.serv_id;BEGIN
i := 0;
insert into bill_gj_log
select 'BEGIN', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'PTIME'
from dual;
FOR REC2 IN C2 LOOP
BEGIN
SELECT CREA_DATE
INTO V_CREATE_DATE
FROM SERV_REMOVE
WHERE SERV_ID = REC2.SERV_ID AND REMOVE_TYPE = 'BIL';
SELECT REMOVE_DATE
INTO V_REMOVE_DATE
FROM SERV_REMOVE
WHERE SERV_ID = REC2.SERV_ID AND REMOVE_TYPE = 'BIL';
SELECT BILLING_RULE_ID
INTO V_BILLING_PROD_ATTR
FROM pm_prod_billing
WHERE PROD_ID = REC2.PROD_ID;
select b.param_id
into V_BILLING_PROD_ATTR_PARA
from pm_prod_billing a, billing_rule_param b
where a.billing_rule_id = b.billing_rule_id(+) and
a.prod_id = rec2.prod_id;
select c.value
into V_BILLING_PROD_ATTR_PARA_VALUE
from pm_prod_billing a,
billing_rule_param b,
billing_rule_param_value c
where a.billing_rule_id = b.billing_rule_id(+) and
b.param_id = c.param_id(+) and a.prod_id = rec2.prod_id;
INSERT INTO GJ_SERV_BILLING_PROD(ACC_NBR,
PRODUCT_ID,
BILLING_PROD_ATTR,
BILLING_PROD_ATTR_PARA,
BILLING_PROD_ATTR_PARA_VALUE,
CREATE_DATE,
REMOVE_DATE)
VALUES(REC2.SERV_ACC_NBR,
REC2.PROD_ID,
V_BILLING_PROD_ATTR,
V_BILLING_PROD_ATTR_PARA,
V_BILLING_PROD_ATTR_PARA_VALUE,
V_CREATE_DATE,
V_REMOVE_DATE);
commit;
exception
when others then
v_err_mss := SQLERRM;
insert into bill_gj_log
select SERV_ID, v_err_mss, 'SERV_PROD'
from SERV_BACK_BILL
where serv_id = rec2.serv_id;
COMMIT;
END;
i := i + 1;
if mod(i, 100) = 0 then
COMMIT;
end if;
end loop;
insert into bill_gj_log
select 'END', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'PTIME'
from dual;
commit;
exception
when others then
v_err_mss := SQLERRM;
insert into bill_gj_log
select NULL, v_err_mss, 'OTHER' from dual;
commit;END bill_gj_serv_prod;以上是我的一个存储过程,但是运行以后,只能插入一条数据,估计是游标的问题,但是小弟郁闷,查不出是什么问题,请哪位大侠指点一下
CURSOR C2 IS
select B.SERV_ACC_NBR, C.PROD_ID, A.SERV_ID
from SERV_BACK_BILL a, serv_acc_nbr b, serv_billing_prod c
where a.serv_id = b.serv_id and
b.serv_id = c.serv_id;
可能2: 出现异常.
when others then
v_err_mss := SQLERRM;
insert into bill_gj_log
select SERV_ID, v_err_mss, 'SERV_PROD'
from SERV_BACK_BILL
where serv_id = rec2.serv_id;
COMMIT;先确认一下大概有多少记录数~ 然后查看是那条数据引起的异常.
建议用 pl/sql developer 来Test一下~ :)
open c2
fetch c2 into ......;
loop
exit when c2%notfount;
........
.......
fetch c2 into ......;
end loop;
close c2;