oracle游标

最新推荐文章于 2025-06-05 09:27:24 发布

最新推荐文章于 2025-06-05 09:27:24 发布 · 37 阅读

文章标签：

#Oracle

本文通过一系列SQL语句展示了如何创建新表、删除旧表并进行数据筛选及更新操作。其中包括使用ROW_NUMBER()函数进行行编号，以及通过DECLARE声明变量来执行复杂的数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

drop table ocscz0904new_dhhm01 ;
create table ocscz0904new_dhhm01 nologging as
select
a.SERVNUMBER ,
a.TRADE_TIME ,
a.TRADE_TYPE ,
a.TRADE_AMT ,
'0' user_falg ,
row_number() over(partition by servnumber,
TRADE_TIME
order by TRADE_TIME desc
) rn
from ocscz0904new_dhhm a
where a.TRADE_AMT>0 and a.SERVNUMBER in(select dhhm from dhhmgy)
;

drop table tb_ocscz0904new_dhhm02;
create table tb_ocscz0904new_dhhm02 nologging as
select
a.SERVNUMBER ,
a.TRADE_TIME ,
a.TRADE_TYPE ,
a.TRADE_AMT ,
'0' user_falg ,
row_number() over(partition by servnumber,
TRADE_TIME
order by TRADE_TIME desc
) rn
from ocscz0904new_dhhm a
where a.TRADE_AMT<0
;
select * from ocscz0904new_dhhm ;

declare
vv_cusor_servnumber varchar2(32);
vv_cusor_chgtime date ;
vv_servnumber_tmp varchar2(32);
vv_chgtime_tmp date ;
cursor v_cursor is
select servnumber ,
trade_time
from tb_ocscz0904new_dhhm02
order by rn desc
;
begin
open v_cursor;
loop
fetch v_cursor into vv_cusor_servnumber, vv_cusor_chgtime;
exit when v_cursor%notfound;

vv_servnumber_tmp := vv_cusor_servnumber;
vv_chgtime_tmp := vv_cusor_chgtime;

update ocscz0904new_dhhm01 a set user_falg = '1'
where a.TRADE_TIME= (select max(TRADE_TIME)
from ocscz0904new_dhhm01 b
where b.servnumber = vv_servnumber_tmp
and b.TRADE_TIME <= vv_chgtime_tmp
and b.TRADE_AMT = a.TRADE_AMT
and b.user_falg = 0
and rownum = 1
)
and a.user_falg = '0'
and rownum = 1
;
commit;
end loop;
close v_cursor;
end;

declare i integer;
begin
for vddate in 20090401..20090430 loop
insert into tb_flj_gpqf_tmp1
select /*+ parallel(a, 10) */
a.serv_number ,
a.statis_date
from dgdm_mk.tb_mk_ls_call_day a
where a.statis_date = to_date(vddate, 'yyyymmdd')
and (m12593_cg_mob_cnt+m12593_ch_mob_cnt+m12593_cm_toll_cnt+m12593_ct_toll_cnt+m12593_ci_toll_cnt) >= 1
;
commit;
end loop;
end
;
/