具体语句如下：
CREATE OR REPLACE
procedure yiliu_yeji as
cursor emp_cur is select to_char(mb.register_time,'yyyy-mm') as dd from member mb where mb.member_type = 0 group by to_char(mb.register_time,'yyyy-mm');
c_row emp_cur%rowtype;
tableCount number;
v_sql varchar2(2555);
begin_day date := to_date('2013-12-01', 'yyyy-mm-dd');
end_day date := sysdate;
begin
select count(1) into tableCount from all_tables where TABLE_NAME = 'DATA_BAK';
if tableCount=1 then
execute immediate 'drop table data_bak';
end if;
execute immediate 'create table data_bak as
select arpm.id,
arpm.nick_name,
arpm.real_name,
sum(case when mab.trade_Type = ''Buy'' then mab.amount when (mab.description like ''%????,?%'' and mab.trade_Type = ''Refund'') or (mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') or (mab.description like ''%????%'' and mab.trade_Type = ''Refund'') or (mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') or (mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') then mab.amount else 0 end ) * -1 as "BB",
0 as "CC",
0 as "DD",
to_char(mab.trade_time, ''YYYY-MM-dd'') as "TT"
from member mb,
member arpm,
agency_relationship arp,
member_account ma,
member_account_bill mab
where mb.id = arp.customer_account
and mb.id = ma.member_id
and ma.id = mab.account_id
and mb.member_type = 0
and arpm.member_type = 2
and arpm.id = arp.parent_id
group by arpm.id,arpm.nick_name,arpm.real_name, to_char(mab.trade_time, ''YYYY-MM-dd'')';
while begin_day <= end_day loop
v_sql := 'INSERT INTO data_bak
select
m.id,
m.nick_name,
m.real_name,
0,
0,
0,
'''|| to_char(begin_day, 'yyyy-mm-dd') || ''' from member m where m.id not in (select db.id from data_bak db where db.tt = ' || to_char(begin_day, '''yyyy-mm-dd''') || ' group by db.id) and m.member_type = 2';
execute immediate v_sql;
begin_day := begin_day + 1;
commit;
end loop;
for c_row in emp_cur loop
v_sql := ' insert into data_bak
select arpm.id,arpm.nick_name,arpm.real_name,0,count(mb.id),0, '''||c_row.dd || '-01''
from
member mb,
member arpm,
agency_relationship arp
where mb.id = arp.customer_account
and mb.member_type = 0
and arpm.member_type = 2
and arpm.id = arp.parent_id
and to_char(mb.register_time,''yyyy-mm'') = '''|| c_row.dd || '''
and exists (select * from member_account ma, member_account_bill mab where ma.id = mab.account_id and mab.trade_type = ''Buy'' and ma.member_id = mb.id and to_char(mab.trade_time, ''yyyy-mm'') = '''||c_row.dd ||''')
group by arpm.id,arpm.nick_name,arpm.real_name';
execute immediate v_sql;
commit;
v_sql := 'insert into data_bak
select arpm.id,
arpm.nick_name,
arpm.real_name,
0,
0 as "CC",
sum(case
when mab.trade_Type = ''Buy'' then
mab.amount
when (mab.description like ''%????,?%'' and mab.trade_Type = ''Refund'') or
(mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') or
(mab.description like ''%????%'' and mab.trade_Type = ''Refund'') or
(mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') or
(mab.description like ''%?????%'' and mab.trade_Type = ''Refund'') then
mab.amount
else
0
end) * -1 as "BB",
'''||c_row.dd || '-01''
from member mb,
member arpm,
agency_relationship arp,
member_account ma,
member_account_bill mab
where mb.id = arp.customer_account
and mb.id = ma.member_id
and ma.id = mab.account_id
and mb.member_type = 0
and arpm.member_type = 2
and arpm.id = arp.parent_id
and to_char(mb.register_time,''yyyy-mm'') = '''|| c_row.dd || '''
and to_char(mab.trade_time,''yyyy-mm'') = '''|| c_row.dd || '''
group by arpm.id, arpm.nick_name, arpm.real_name';
execute immediate v_sql;
commit;
end loop;
execute immediate 'insert into data_detail select to_char(sysdate, ''yyyy-mm-dd hh24:mi:ss'') from dual';
commit;
end;
提供资料也行，谢谢
