天表向月表整合的存储过程(oracle）

来源：互联网发布：英语句子分析器软件编辑：程序博客网时间：2024/05/16 23:59

create or replace procedure DayIntoMonth(today date,day integer,)
as
login_name z_day_1.login_name%TYPE;
login_ip z_day_1.login_ip%TYPE;
lab_ip z_day_1.lab_ip%TYPE;

day_cur integer;
month_cur integer;
dropI_cur integer;
month_all_cur integer;

yestoday date;

time_dur_all number;
time_dur_hour number;
ret integer;
begin
day_cur:=dbms_sql.open_cursor;
month_cur:=dbms_sql.open_cursor;
dropI_cur:=dbms_sql.open_cursor;
month_all_cur:=dbms_sql.open_cursor;

dbms_sql.parse(dropI_cur,'drop index z_i_month_'||day,dbms_sql.native);
ret:=dbms_sql.execute(dropI_cur);

yestoday:=today-1;
dbms_sql.parse(day_cur,'select login_name,login_ip,lab_ip,sum(time_duration) from z_day_'||day||' where logout_date between :lasthour_b and :today_b group by login_name,login_ip,lab_ip',dbms_sql.native);
dbms_sql.bind_variable(day_cur,':lasthour_b',yestoday);
dbms_sql.bind_variable(day_cur,':today_b',today);
dbms_sql.define_column(day_cur,1,login_name,10);
dbms_sql.define_column(day_cur,2,login_ip,32);
dbms_sql.define_column(day_cur,3,lab_ip,32);
dbms_sql.define_column(day_cur,4,time_dur_all);
ret:=dbms_sql.execute(day_cur);

loop
  if dbms_sql.fetch_rows(day_cur)>0 then
   dbms_sql.column_value(day_cur,1,login_name);
   dbms_sql.column_value(day_cur,2,login_ip);
   dbms_sql.column_value(day_cur,3,lab_ip);
   dbms_sql.column_value(day_cur,4,time_dur_all);
   dbms_sql.parse(month_cur,'insert into z_month_'||day||'(login_name,login_ip,lab_ip,logout_date,time_duration) values('||login_name||','||login_ip||','||lab_ip||',:todayb,'||time_dur_all||')',dbms_sql.native);
   dbms_sql.bind_variable(month_cur,':todayb',today);
   ret:=dbms_sql.execute(month_cur);
  else
   exit;
  end if;
end loop;

dbms_sql.parse(dropI_cur,'create index z_i_month_'||day||' on z_month_'||day||'(login_name)',dbms_sql.native);
ret:=dbms_sql.execute(dropI_cur);

dbms_sql.parse(month_all_cur,'select lab_ip,round(sum(time_duration)/60) from z_day_'||day||' group by lab_ip',dbms_sql.native);
dbms_sql.define_column(month_all_cur,1,lab_ip,32);
dbms_sql.define_column(month_all_cur,2,time_dur_hour);
ret:=dbms_sql.execute(month_all_cur);

loop
  if dbms_sql.fetch_rows(month_all_cur)>0 then
    dbms_sql.column_value(month_all_cur,1,lab_ip);
    dbms_sql.column_value(month_all_cur,2,time_dur_hour);
    dbms_sql.parse(day_cur,'INSERT INTO t_month(logout_date,lab_ip,time_duration) VALUES(:todayb,'||lab_ip||','||time_dur_hour||')',dbms_sql.native);
    dbms_sql.bind_variable(day_cur,':todayb',today);
    ret:=dbms_sql.execute(day_cur);
  else
    exit;
  end if;
end loop;

commit;

dbms_sql.close_cursor(dropI_cur);
dbms_sql.close_cursor(day_cur);
dbms_sql.close_cursor(month_cur);
dbms_sql.close_cursor(month_all_cur);
exception
when others then
rollback;
dbms_sql.parse(dropI_cur,'create index z_i_day'||day||' on z_day_'||day||'(login_name)',dbms_sql.native);
ret:=dbms_sql.execute(dropI_cur);
if dbms_sql.is_open(dropI_cur) then
  dbms_sql.close_cursor(dropI_cur);
end if;
if dbms_sql.is_open(day_cur) then
  dbms_sql.close_cursor(day_cur);
end if;
if dbms_sql.is_open(month_cur) then
  dbms_sql.close_cursor(month_cur);
end if;
if dbms_sql.is_open(month_all_cur) then
  dbms_sql.close_cursor(month_all_cur);
end if;
raise;
end DayIntoMonth;