ID startday start end
1 20120221 080259 080650
2 20120221 235900 025600
最后拆分成
ID startday t0 t1 t2 t3 t4 t5 t6 ......t23(t0-t23为24个小时片段)
t8
1 20120221 (080259-080650)
t23
2 20120221 (235900-235959)
t0 t2
2 20120222 (000000-015959) (020000-025600)
1 20120221 080259 080650
2 20120221 235900 025600
最后拆分成
ID startday t0 t1 t2 t3 t4 t5 t6 ......t23(t0-t23为24个小时片段)
t8
1 20120221 (080259-080650)
t23
2 20120221 (235900-235959)
t0 t2
2 20120222 (000000-015959) (020000-025600)
ID startday t0 t1 t2 t3 t4 t5 t6 ......t23(t0-t23为24个小时片段)
t8
1 20120221 (080259-080659)4(分钟)
t23
2 20120221 (235900-235959)=59/60
t0 t2
2 20120222 (000000-015959)=60 (020000-025600)=56
t0 t2
2 20120222 (000000-015959) (020000-025600)
不是
t0 t1 t2
2 20120222 (000000-005959) (010000-015959)(020000-025600)
不是24个小时片段吗?
1 20120221 080259 080650
2 20120221 235900 025600ID 2 是20120221 235900开始 结束时025600(这个一定是20120222 假设不会垮2天)
tmp(
ID number(2),
startday varchar2(8),
start varchar2(6),
end varchar2(6)
)OUTPUT表
tmp1(
ID number(2),
startday varchar2(8),
t1 varchar2(13),
……
t23 varchar2(13)
)
就数据而言实现了……
create or replace procedure P_DIV is
str_sql_1 varchar2(1000);
str_sql_2 varchar2(1000);
str_sql varchar2(2000);
row_start number(2);
row_end number(2);
str_startday varchar2(8);
cursor cursor_tmp is
select * from Tmp;
row_cursor_tmp cursor_tmp%rowtype;begin
execute immediate 'truncate table tmp1';
for row_cursor_tmp in cursor_tmp
loop
row_start := to_number(substrb(row_cursor_tmp.start_d, 1, 2));
row_end := to_number(substrb(row_cursor_tmp.end_d, 1, 2));
if row_start < row_end then
str_sql_1 := 'insert into tmp1 (id,startday';
str_sql_2 := ')values (' || row_cursor_tmp.id || ',' || row_cursor_tmp.startday;
for i in row_start .. row_end
loop
if i = row_start then
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || row_cursor_tmp.start_d || '-' ||
lpad(i, 2, 0) || '5959''';
elsif i = row_end then
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || lpad(i, 2, 0) || '0000-' ||
row_cursor_tmp.end_d || '''';
else
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || lpad(i, 2, 0) || '0000-' || lpad(i, 2, 0) ||
'5959''';
end if;
end loop;
str_sql := str_sql_1 || str_sql_2 || ')';
--dbms_output.put_line(str_sql)
dbms_output.put_line(str_sql);
execute immediate str_sql;
elsif row_start = row_end then
str_sql := 'insert into tmp1 (id,startday,';
str_sql := str_sql || 't' || row_start || ')values (' || row_cursor_tmp.id || ',' ||
row_cursor_tmp.startday || ',''';
str_sql := str_sql || row_cursor_tmp.start_d || '-' || row_cursor_tmp.end_d || ''')';
--dbms_output.put_line(str_sql)
dbms_output.put_line(str_sql);
execute immediate str_sql;
else
str_sql_1 := 'insert into tmp1 (id,startday';
str_sql_2 := ')values (' || row_cursor_tmp.id || ',' || row_cursor_tmp.startday;
for i in row_start .. 23
loop
if i = row_start then
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || row_cursor_tmp.start_d || '-' ||
lpad(i, 2, 0) || '5959''';
else
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || lpad(i, 2, 0) || '0000-' || lpad(i, 2, 0) ||
'5959''';
end if;
end loop;
str_sql := str_sql_1 || str_sql_2 || ')';
--dbms_output.put_line(str_sql)
dbms_output.put_line(str_sql);
execute immediate str_sql;
str_startday := to_char(to_number(row_cursor_tmp.startday) + 1);
str_sql_1 := 'insert into tmp1 (id,startday';
str_sql_2 := ')values (' || row_cursor_tmp.id || ',' || str_startday;
for i in 0 .. row_end
loop
if i = row_end then
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || lpad(i, 2, 0) || '0000-' ||
row_cursor_tmp.end_d || '''';
else
str_sql_1 := str_sql_1 || ',t' || i;
str_sql_2 := str_sql_2 || ',''' || lpad(i, 2, 0) || '0000-' || lpad(i, 2, 0) ||
'5959''';
end if;
end loop;
str_sql := str_sql_1 || str_sql_2 || ')';
--dbms_output.put_line(str_sql)
dbms_output.put_line(str_sql);
execute immediate str_sql;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line('error');
rollback;
end P_DIV;