在写存储过程的时候，有时不直接使用insert into 表名 values select * from XXX where XXX
而是使用游标进行数据的一条条插入，主要原因是数据库资源的问题。
使用insert直接插入整个查询的表的数据，数据库会等到都执行完成之后才能释放资源，若查询的过程比较慢，那么资源就一直处于使用状态。比较费资源。
若使用游标进行插入的话，可以设置游标多少条数据进行插入一次，然后释放一下资源。
游标的形式：
create or replace procedure PRC_RPN_REVISIT_REPORT is
cursor cur_rpn_revisit_report is
select A.*,
case when A.STATUS is not null and A.STATUS = '3'
then '回访不成功'
when A.STATUS is null and CONCAT(A.SERVICE_RESULT,' ') <> CONCAT( '不回访通过',' ')
then '回访成功'
when A.STATUS is null and A.SERVICE_RESULT ='不回访通过'
then '不回访通过'
end RINGBACK_STATUS--回访处理状态
from (
SELECT S.ROW_ID,
S.BRANCH_ID,
S.CUSTOMER_NAME,
S.MID_PRODUCT_ID,
S.MODEL_NAME,
P.PROD_SERIAL_NO,
F.SERVICE_TYPE_ID,
F.SERVICE_MODE_ID,
F.AUTO_AUDIT_RESULT,
F.IF_WARRANT,
S.AREA_CODE1,
F.AUDIT_BILL,
C.RINGBACK_TIME,
S.TELEPHONE2,
S.TELEPHONE1,
S.TELEPHONE3,
case when S.AREA_CODE1 is not null and S.TELEPHONE1 is not null
then
'家庭电话:' || S.AREA_CODE1 || '-' || S.TELEPHONE1
end HOMEPHONE,
case when S.TELEPHONE2 is not null
then
'移动电话:' || S.TELEPHONE2
end MOBILEPHONE,
case when S.TELEPHONE3 is not null
then
'其他电话:' || S.TELEPHONE3
end OFFICEPHONE,
C.DISSATIS_CODE,
C.RINGBACK_DESC,
F.WITHDRAW_REASON,
E.PRICE_DIFFERENCE,
F.SERVICE_FINISH_TIME,
C.SERVICE_RESULT,
S.REQUIRE_SERVICE_DATE,
C.VISIT_TIMES,
C.TARGET_CALLCENTER_ID,
C.RINGBACKER,
C.CALLCENTER_ID,
C.RINGBACK_RESULT,
C.STATUS,
F.SERVICE_CORP_NAME,
P.BUY_DATE,
I.REMARK,
A.APPEAL_DESC,
F.SERVICE_CORP_ID,
C.SATIS_ID,
C.SATIS_DESC,
C.SATIS_DEDUCT,
F.RETURN_TIMES,
S.BELONG_CENTER,
F.FIRST_BRANCH_ID,
S.product_id,
( select t.codename from v_codelist t where t.kindvalue='RINGBACKER' and t.codevalue =C.RINGBACKER) RINGBACKER_NAME ,
( select t.codename from v_codelist t where t.kindvalue='PROD_MID' and t.codevalue =S.MID_PRODUCT_ID) MID_PRODUCT_NAME ,
( select t.codename from v_codelist t where t.kindvalue='RINGBACK_RESULT' and t.codevalue =C.RINGBACK_RESULT) RINGBACK_RESULT_NAME ,
( select t.codename from v_codelist t where t.kindvalue='ORG_DEPT_DV' and t.codevalue =S.BRANCH_ID) BRANCH_NAME ,
( select t.codename from v_codelist t where t.kindvalue='SERVICE_TYPE' and t.codevalue =F.SERVICE_TYPE_ID) SERVICE_TYPE_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='SERVICE_MODE' and t.codevalue =F.SERVICE_MODE_ID) SERVICE_MODE_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='DISSATIS_CODE' and t.codevalue =C.DISSATIS_CODE) DISSATIS_CODE_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='IF_WARRANT' and t.codevalue =F.IF_WARRANT) IF_WARRANT_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='SATISFACTION' and t.codevalue =C.SATIS_ID) SATIS_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='CALL_CENTER' and t.codevalue =C.TARGET_CALLCENTER_ID) TARGET_CALLCENTER_NAME ,
( select distinct t.codename from v_codelist t where t.kindvalue='CALL_CENTER' and t.codevalue =C.CALLCENTER_ID) CALLCENTER_NAME
FROM WB_SERVICE_INFO S,
WB_CALL_ON_TASK C,
WB_SERVICE_FEEDBACK F,
WB_CUSTOMER_PRODUCT P,
EM_RETURN_REQ_BILL E,
WB_SERVICE_FEEDBACK_ITEM I,
WB_SERVICE_CORP_APPEAL A
WHERE 1 = 1
AND C.ACTIVE_FLAG = '1'
AND C.WB_ID = F.ROW_ID
AND S.ROW_ID = F.ROW_ID
AND S.ROW_ID = A.WB_ID(+)
AND P.ROW_ID = S.CUSTOMER_PROD_ID
AND S.ROW_ID = E.RETURN_WB_NO(+)
AND (C.STATUS = '3' OR C.STATUS IS NULL)
AND F.ROW_ID = I.WB_ID(+)
AND I.PRIMARY_FLAG(+) = '1'
AND F.WB_STATUS != '20'
and C.RINGBACK_TIME >= trunc(sysdate-90)
and C.RINGBACK_TIME < trunc(sysdate)
order by a.row_id asc
) A;
lv_rpn_revisit_report RPN_REVISIT_REPORT%rowtype;
begin
delete from RPN_REVISIT_REPORT t where t.RINGBACK_TIME < add_months(sysdate,-3);
--commit;
for sendRec in cur_rpn_revisit_report loop
begin
lv_rpn_revisit_report.ROW_ID := sendRec.ROW_ID;
lv_rpn_revisit_report.BRANCH_ID := sendRec.BRANCH_ID;
lv_rpn_revisit_report.CUSTOMER_NAME := sendRec.CUSTOMER_NAME;
lv_rpn_revisit_report.MID_PRODUCT_ID := sendRec.MID_PRODUCT_ID;
lv_rpn_revisit_report.MODEL_NAME := sendRec.MODEL_NAME;
lv_rpn_revisit_report.PROD_SERIAL_NO := sendRec.PROD_SERIAL_NO;
lv_rpn_revisit_report.SERVICE_TYPE_ID := sendRec.SERVICE_TYPE_ID;
lv_rpn_revisit_report.SERVICE_MODE_ID := sendRec.SERVICE_MODE_ID;
lv_rpn_revisit_report.AUTO_AUDIT_RESULT := sendRec.AUTO_AUDIT_RESULT;
lv_rpn_revisit_report.IF_WARRANT := sendRec.IF_WARRANT;
lv_rpn_revisit_report.AREA_CODE1 := sendRec.AREA_CODE1;
lv_rpn_revisit_report.AUDIT_BILL := sendRec.AUDIT_BILL;
lv_rpn_revisit_report.RINGBACK_TIME := sendRec.RINGBACK_TIME;
lv_rpn_revisit_report.TELEPHONE2 := sendRec.TELEPHONE2;
lv_rpn_revisit_report.TELEPHONE1 := sendRec.TELEPHONE1;
lv_rpn_revisit_report.TELEPHONE3 := sendRec.TELEPHONE3;
lv_rpn_revisit_report.HOMEPHONE := sendRec.HOMEPHONE;
lv_rpn_revisit_report.MOBILEPHONE := sendRec.MOBILEPHONE;
lv_rpn_revisit_report.OFFICEPHONE := sendRec.OFFICEPHONE;
lv_rpn_revisit_report.DISSATIS_CODE := sendRec.DISSATIS_CODE;
lv_rpn_revisit_report.RINGBACK_DESC := sendRec.RINGBACK_DESC;
lv_rpn_revisit_report.WITHDRAW_REASON := sendRec.WITHDRAW_REASON;
lv_rpn_revisit_report.PRICE_DIFFERENCE := sendRec.PRICE_DIFFERENCE;
lv_rpn_revisit_report.SERVICE_FINISH_TIME := sendRec.SERVICE_FINISH_TIME;
lv_rpn_revisit_report.SERVICE_RESULT := sendRec.SERVICE_RESULT;
lv_rpn_revisit_report.REQUIRE_SERVICE_DATE := sendRec.REQUIRE_SERVICE_DATE;
lv_rpn_revisit_report.VISIT_TIMES := sendRec.VISIT_TIMES;
lv_rpn_revisit_report.TARGET_CALLCENTER_ID := sendRec.TARGET_CALLCENTER_ID;
lv_rpn_revisit_report.RINGBACKER := sendRec.RINGBACKER;
lv_rpn_revisit_report.CALLCENTER_ID := sendRec.CALLCENTER_ID;
lv_rpn_revisit_report.RINGBACK_RESULT := sendRec.RINGBACK_RESULT;
lv_rpn_revisit_report.STATUS := sendRec.STATUS;
lv_rpn_revisit_report.RINGBACK_STATUS := sendRec.RINGBACK_STATUS;
lv_rpn_revisit_report.SERVICE_CORP_NAME := sendRec.SERVICE_CORP_NAME;
lv_rpn_revisit_report.BUY_DATE := sendRec.BUY_DATE;
lv_rpn_revisit_report.REMARK := sendRec.REMARK;
lv_rpn_revisit_report.APPEAL_DESC := sendRec.APPEAL_DESC;
lv_rpn_revisit_report.SERVICE_CORP_ID := sendRec.SERVICE_CORP_ID;
lv_rpn_revisit_report.SATIS_ID := sendRec.SATIS_ID;
lv_rpn_revisit_report.SATIS_DESC := sendRec.SATIS_DESC;
lv_rpn_revisit_report.SATIS_DEDUCT := sendRec.SATIS_DEDUCT;
lv_rpn_revisit_report.RETURN_TIMES := sendRec.RETURN_TIMES;
lv_rpn_revisit_report.BELONG_CENTER := sendRec.BELONG_CENTER;
lv_rpn_revisit_report.FIRST_BRANCH_ID := sendRec.FIRST_BRANCH_ID;
lv_rpn_revisit_report.product_id := sendRec.product_id;
lv_rpn_revisit_report.RINGBACKER_NAME := sendRec.RINGBACKER_NAME;
lv_rpn_revisit_report.RINGBACK_RESULT_NAME := sendRec.RINGBACK_RESULT_NAME;
lv_rpn_revisit_report.BRANCH_NAME := sendRec.BRANCH_NAME;
lv_rpn_revisit_report.SERVICE_TYPE_NAME := sendRec.SERVICE_TYPE_NAME;
lv_rpn_revisit_report.SERVICE_MODE_NAME := sendRec.SERVICE_MODE_NAME;
lv_rpn_revisit_report.DISSATIS_CODE_NAME := sendRec.DISSATIS_CODE_NAME;
lv_rpn_revisit_report.IF_WARRANT_NAME := sendRec.IF_WARRANT_NAME;
lv_rpn_revisit_report.SATIS_NAME := sendRec.SATIS_NAME;
lv_rpn_revisit_report.TARGET_CALLCENTER_NAME := sendRec.TARGET_CALLCENTER_NAME;
lv_rpn_revisit_report.CALLCENTER_NAME := sendRec.CALLCENTER_NAME;
exception when others then
null;
end;
insert into RPN_REVISIT_REPORT values lv_rpn_revisit_report;
end loop;
commit;
EXCEPTION WHEN OTHERS THEN
rollback;
end PRC_RPN_REVISIT_REPORT;
直接插入的形式：
create or replace procedure PRC_RPN_VISITER_COUNT is
begin
DELETE FROM RPN_VISITER_COUNT_CALLCENTER T where T.RINGBACK_TIME <add_months(sysdate,-3);
-- commit;
INSERT INTO RPN_VISITER_COUNT_CALLCENTER
(ROW_ID,
PRODUCT_ID,
RINGBACK_TIME,
SERVICE_RESULT,
PROJECT_CODE,
STATUS,
PSN_NAME,
org_id)
SELECT f_get_report_rowid('rpn_visiter_count_callcenter'),
t1.product_id,
T1.RINGBACK_TIME,
T1.SERVICE_RESULT,
T1.PROJECT_CODE,
T1.STATUS,
T2.PSN_NAME,
t2.org_id
FROM WB_CALL_ON_TASK T1, V_PERSON_DEPT_MAP T2
WHERE T1.RINGBACKER = T2.PSN_ACCOUNT(+)
AND T1.RINGBACK_TIME >= trunc(sysdate-1)
AND T1.RINGBACK_TIME < trunc(sysdate);
commit;
EXCEPTION WHEN OTHERS THEN
rollback;
end PRC_RPN_VISITER_COUNT;
3119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
