declare
load_date date; --声明一个循环变量
begin
load_date := to_date('201001', 'yyyymm'); --给循环变量赋初始值
while load_date <=
to_date(to_char(add_months(sysdate, -1), 'yyyymm'), 'yyyymm') --定义循环条件
Loop
insert into dm_custbd_khjz_lishi_flow_1
select *
from (select calday,
cus_code,
lower(value_grade),
lower(value_grade_shang),
new_customer
from dm_custbd_khjz_lishi
where calday = to_char(load_date, 'yyyymm')
union all
select to_char(load_date, 'yyyymm') calday,
cus_code,
'冻结' value_grade,
lower(value_grade) value_grade_shang,
null new_customer
from DM_CUSTBD_KHJZ_LISHI
where calday = to_char(add_months(load_date, -1), 'yyyymm')--满足循环条件的语句
and delete_flag = '冻结');
load_date := add_months(load_date, 1);--循环条件+1
commit;
end loop;
end;
load_date date; --声明一个循环变量
begin
load_date := to_date('201001', 'yyyymm'); --给循环变量赋初始值
while load_date <=
to_date(to_char(add_months(sysdate, -1), 'yyyymm'), 'yyyymm') --定义循环条件
Loop
insert into dm_custbd_khjz_lishi_flow_1
select *
from (select calday,
cus_code,
lower(value_grade),
lower(value_grade_shang),
new_customer
from dm_custbd_khjz_lishi
where calday = to_char(load_date, 'yyyymm')
union all
select to_char(load_date, 'yyyymm') calday,
cus_code,
'冻结' value_grade,
lower(value_grade) value_grade_shang,
null new_customer
from DM_CUSTBD_KHJZ_LISHI
where calday = to_char(add_months(load_date, -1), 'yyyymm')--满足循环条件的语句
and delete_flag = '冻结');
load_date := add_months(load_date, 1);--循环条件+1
commit;
end loop;
end;