本文介绍了Oracle数据库中游标的基本使用，通过一个具体的游标实例展示了如何声明、打开、遍历和关闭游标，以在过程中处理数据。
平时写数据库过程时，很多时候需要用到游标，下面给出一个游标使用实例，游标的使用很简单。
首先定义游标：
Type t_cursor Is Ref Cursor; --声明游标
v_cursor t_cursor; --定义游标
定义好后，在过程里就可以对定义的游标进行使用了：
open v_cursor for
select v.next_dept_code
from view_next_busi v
where v.dept_code = busi_code;
LOOP
Fetch v_cursor
Into v_busi_code;
Exit When v_cursor%Notfound;
BEGIN
。。。。。。
END;
END LOOP;
close v_cursor;
CREATE OR REPLACE PROCEDURE YK_P_VISIT_BILL(in_stat_mon date, --统计月份
busi_code varchar2 --统计的营业区域) AS
/*
*上门服务统计
*/
v_result varchar2(5);
v_stat_mon number(6);
v_busi_code varchar2(8);
Type t_cursor Is Ref Cursor; --声明游标
v_cursor t_cursor; --定义游标
BEGIN
v_stat_mon := replace(to_char(in_stat_mon, 'YYYY-MM'), '-', '');
v_result := -10;
--首先删除本月的统计数据
delete from YK_VISIT_BILL t where t.stat_mon = in_stat_mon;
v_result := -20;
--删除所有数据
delete from YK_VISIT_BILL_RE;
v_result := -30;
--按区域和月份统计
insert into YK_VISIT_BILL_RE
(BUSINESS_PLACE_CODE,
USER_CLASS,
INVOICE_GET_MODE1,
INVOICE_GET_MODE2,
INVOICE_GET_MODE3,
INVOICE_GET_MODE4,
INVOICE_GET_MODE5,
INVOICE_GET_MODE6,
NUM)
select A1.business_place_code 营业区域,
A1.user_class 客户类型,
invoice_get_mode1 邮寄平信,
invoice_get_mode2 营业网点自取,
invoice_get_mode3 银行自取,
invoice_get_mode4 邮寄VIP挂号,
invoice_get_mode5 供电公司上门派送,
invoice_get_mode6 发票派送方式未维护户数,
num 客户数
from (select t2.business_place_code,
t2.user_class,
nvl(max(decode(t2.invoice_get_mode, '1', t2.cn)), 0) invoice_get_mode1,
nvl(max(decode(t2.invoice_get_mode, '2', t2.cn)), 0) invoice_get_mode2,
nvl(max(decode(t2.invoice_get_mode, '3', t2.cn)), 0) invoice_get_mode3,
nvl(max(decode(t2.invoice_get_mode, '4', t2.cn)), 0) invoice_get_mode4,
nvl(max(decode(t2.invoice_get_mode, '5', t2.cn)), 0) invoice_get_mode5,
nvl(max(decode(t2.invoice_get_mode, '', t2.cn)), 0) invoice_get_mode6
from (select t.business_place_code,
t.user_class,
t.invoice_get_mode,
count(1) cn
from (select (case
when c.user_type1 < 20 and c.ms_mode < 3 then
3 --高压
else
(case
when c.user_type1 = 20 then
(case
when (select nvl(max(j.price_code), 1)
from jl_point_file j
where j.user_no = c.customer_id) in
('50000', '50200', '50100') then
1 --居民
else
(case
when (select nvl(max(j.price_code), 1)
from jl_point_file j
where j.user_no = c.customer_id) not in
('50000', '50200', '50100') then
2 --低压非居民
else
1 --默认为居民
end) end) else 1 --默认为居民
end) end) user_class,
c.*
from cim_customer c, sf_note_info s
where c.customer_id = s.user_no
and c.user_type1 <= '20'
and c.user_state = '1'
and s.mon = v_stat_mon --统计6月份
and s.business_place_code in
(select v.next_dept_code
from view_next_busi v
where v.dept_code = busi_code) --统计营业区域
) t
group by t.business_place_code,
t.user_class,
t.invoice_get_mode
order by t.business_place_code) t2
group by t2.business_place_code, t2.user_class
order by t2.business_place_code) A1,
(select t.business_place_code, t.user_class, count(1) num
from (select (case
when c.user_type1 < 20 and c.ms_mode < 3 then
3 --高压
else
(case
when c.user_type1 = 20 then
(case
when (select nvl(max(j.price_code), 1)
from jl_point_file j
where j.user_no = c.customer_id) in
('50000', '50200', '50100') then
1 --居民
else
(case
when (select nvl(max(j.price_code), 1)
from jl_point_file j
where j.user_no = c.customer_id) not in
('50000', '50200', '50100') then
2 --低压非居民
else
1 --默认为居民
end) end) else 1 --默认为居民
end) end) user_class,
c.*
from cim_customer c
where c.user_type1 <= '20'
and c.user_state = '1'
and exists (select 1
from sf_note_info s
where s.user_no = c.customer_id
and s.mon = v_stat_mon --统计6月份
and s.business_place_code in
(select v.next_dept_code
from view_next_busi v
where v.dept_code = busi_code) --统计营业区域
)) t
group by t.business_place_code, t.user_class
order by t.business_place_code) A2
where A1.user_class = A2.user_class
and A1.business_place_code = A2.business_place_code;
v_result := -40;
--更新本月
open v_cursor for
select v.next_dept_code
from view_next_busi v
where v.dept_code = busi_code;
LOOP
Fetch v_cursor
Into v_busi_code;
Exit When v_cursor%Notfound;
BEGIN
insert into YK_VISIT_BILL
(BUSINESS_PLACE_CODE,
STAT_MON,
billtall_sum,
bill_visit_num,
billtall_resident_sum,
bill_visit_resident_num,
billtall_low_sum,
bill_visit_low_num,
billtall_high_sum,
bill_visit_high_num)
select v_busi_code,
in_stat_mon,
nvl(sum(A1.billtall_sum), 0),
nvl(sum(A1.bill_visit_num), 0),
nvl(sum(A1.billtall_resident_sum), 0),
nvl(sum(A1.bill_visit_resident_num), 0),
nvl(sum(A1.billtall_low_sum), 0),
nvl(sum(A1.bill_visit_low_num), 0),
nvl(sum(A1.billtall_high_sum), 0),
nvl(sum(A1.bill_visit_high_num), 0)
from (select nvl(sum(b.invoice_get_mode1 + b.invoice_get_mode2 +
b.invoice_get_mode3 + b.invoice_get_mode4 +
b.invoice_get_mode5 + b.invoice_get_mode6),
0) billtall_sum,
nvl(sum(b.invoice_get_mode5), 0) bill_visit_num,
decode(b.user_class,
'1',
nvl(sum(b.invoice_get_mode1 +
b.invoice_get_mode2 +
b.invoice_get_mode3 +
b.invoice_get_mode4 +
b.invoice_get_mode5 +
b.invoice_get_mode6),
0)) billtall_resident_sum,
decode(b.user_class,
'1',
nvl(sum(b.invoice_get_mode5), 0)) bill_visit_resident_num,
decode(b.user_class,
'2',
nvl(sum(b.invoice_get_mode1 +
b.invoice_get_mode2 +
b.invoice_get_mode3 +
b.invoice_get_mode4 +
b.invoice_get_mode5 +
b.invoice_get_mode6),
0)) billtall_low_sum,
decode(b.user_class,
'2',
nvl(sum(b.invoice_get_mode5), 0)) bill_visit_low_num,
decode(b.user_class,
'3',
nvl(sum(b.invoice_get_mode1 +
b.invoice_get_mode2 +
b.invoice_get_mode3 +
b.invoice_get_mode4 +
b.invoice_get_mode5 +
b.invoice_get_mode6),
0)) billtall_high_sum,
decode(b.user_class,
'3',
nvl(sum(b.invoice_get_mode5), 0)) bill_visit_high_num
from YK_VISIT_BILL_RE b
where exists
(select 1
from view_next_busi v
where v.next_dept_code = b.business_place_code
and v.dept_code = v_busi_code)
group by b.user_class) A1;
END;
END LOOP;
close v_cursor;
v_result := -50;
----更新比率
update YK_VISIT_BILL a
set (a.bill_visit_rate, a.bill_visit_resident_rate, a.bill_visit_low_rate, a.bill_visit_high_rate) = (select decode(a.billtall_sum,
0,
0,
y.bill_visit_num /
a.billtall_sum) * 100,
decode(a.billtall_resident_sum,
0,
0,
y.bill_visit_resident_num /
a.billtall_resident_sum) * 100,
decode(a.billtall_low_sum,
0,
0,
y.bill_visit_low_num /
a.billtall_low_sum) * 100,
decode(a.billtall_high_sum,
0,
0,
y.bill_visit_high_num /
a.billtall_high_sum) * 100
from YK_VISIT_BILL y
where y.business_place_code =
a.business_place_code
and y.stat_mon =
a.stat_mon)
where a.stat_mon = in_stat_mon;
commit;
Exception
when others then
begin
p_pub_error_log(v_result, sqlcode, sqlerrm, 'YK_P_VISIT_BILL');
rollback;
end;
END;
扫一扫
361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
