create or replace procedure div_ins(table_name varchar2, instance number) is min_row number; max_row number; num number; n_ave_count number; n_min_row number; n_max_row number; n_row_count number; n_row_id number; sqlstr1 varchar2(100 char); sqlstr2 varchar2(100 char); sqlstr3 varchar2(100 char); type ref_cursor is ref cursor; t_cursor ref_cursor; i number;
begin num := 0; i := 1; sqlstr1 := 'select count(1) from ' || table_name ||' where status is null'; execute immediate sqlstr1 into n_row_count; sqlstr2 := 'select min(row_id), max(row_id) from ' || table_name ||' where status is null'; execute immediate sqlstr2 into n_min_row, n_max_row; n_ave_count := ceil(n_row_count / instance); sqlstr3 := 'select row_id from ' || table_name ||' where status is null order by row_id'; open t_cursor for sqlstr3; loop fetch t_cursor into n_row_id; exit when t_cursor%notfound; num := num + 1; max_row := n_row_id; if (num = n_ave_count * i) then if (i < instance) then if (i = 1) then min_row := n_min_row; end if; insert into xcc_instance values (table_name, i, min_row, max_row, null, null, null, null); commit; min_row := max_row + 1; i := i + 1; end if; end if; end loop; insert into xcc_instance values (table_name, instance, min_row, n_max_row, null, null, null, null); commit; close t_cursor; end; 有什么不对,请多多指教!
原來是用fun解決的呀! 可以考慮用一個sql解決嘛! 我的思路是: 1、select CEIL((count(1)/5)) as ss from tab1 where trim(status) is null; ( 先取出表.status=空 记录的个数,用CEIL((count(1)/5)) 计算出每组‘status=空’的个数,假设ss=3) 2、SELECT MOD(ROWNUM-1,ss)+1 as ord_num,a.row_id from tab1 a where trim(a.status) is null order by a.row_id; 结果类似如下: ord_num rowid 1 1 2 4 3 5 1 6 2 9 3 10 3、之后取出第二个sql结果中ord_num=3的rowid,如下样子: ord_num rowid 3 5 3 10 4、使用3的结果, instance min_row max_row 1 1 3.rowid 2 3.rowid + 1 3.rowid 3 3.rowid + 1 3.rowid 4 3.rowid + 1 3.rowid 以上sql步骤简化每考虑,理论思路上应该可以。
最小row_id与最大row_id之差如果不是5的整数倍,咋办?
要求每组中的空值、ok值 的个数相同?
1 ok
2 ok
3
4
5 ok
6
7 ok
8
9 ok
10
11
12
13
14
15
16
17
18
19
如果最小row_id与最大row_id之差如果不是5的整数倍,则最后一段的数据可以少一个或多个。
只要求status为空的数据,在每组里的个数一样。状态为OK的数据不用理它。要求得到的结果为(插到另一张表)
instance min_row max_row
1 1 6 ----status is null 3
2 7 11 ----status is null 3
3 12 14 ----status is null 3
4 15 17 ----status is null 3
5 18 19 ---status is null 2 [最后的数据可以少一个,或多个]
用到 CEIL((count(1)/5)) 了吗?
楼主如果是用sql搞定的,请贴出来看看,学习学习!
min_row number;
max_row number;
num number;
n_ave_count number;
n_min_row number;
n_max_row number;
n_row_count number;
n_row_id number;
sqlstr1 varchar2(100 char);
sqlstr2 varchar2(100 char);
sqlstr3 varchar2(100 char);
type ref_cursor is ref cursor;
t_cursor ref_cursor;
i number;
begin
num := 0;
i := 1;
sqlstr1 := 'select count(1) from ' || table_name ||' where status is null';
execute immediate sqlstr1
into n_row_count;
sqlstr2 := 'select min(row_id), max(row_id) from ' || table_name ||' where status is null';
execute immediate sqlstr2
into n_min_row, n_max_row;
n_ave_count := ceil(n_row_count / instance);
sqlstr3 := 'select row_id from ' || table_name ||' where status is null order by row_id';
open t_cursor for sqlstr3;
loop
fetch t_cursor
into n_row_id;
exit when t_cursor%notfound;
num := num + 1;
max_row := n_row_id;
if (num = n_ave_count * i) then
if (i < instance) then
if (i = 1) then
min_row := n_min_row;
end if;
insert into xcc_instance
values
(table_name, i, min_row, max_row, null, null, null, null);
commit;
min_row := max_row + 1;
i := i + 1;
end if;
end if;
end loop;
insert into xcc_instance
values
(table_name, instance, min_row, n_max_row, null, null, null, null);
commit;
close t_cursor;
end;
有什么不对,请多多指教!
可以考慮用一個sql解決嘛!
我的思路是:
1、select CEIL((count(1)/5)) as ss from tab1 where trim(status) is null;
( 先取出表.status=空 记录的个数,用CEIL((count(1)/5)) 计算出每组‘status=空’的个数,假设ss=3) 2、SELECT MOD(ROWNUM-1,ss)+1 as ord_num,a.row_id from tab1 a
where trim(a.status) is null
order by a.row_id;
结果类似如下:
ord_num rowid
1 1
2 4
3 5
1 6
2 9
3 10
3、之后取出第二个sql结果中ord_num=3的rowid,如下样子:
ord_num rowid
3 5
3 10
4、使用3的结果, instance min_row max_row
1 1 3.rowid
2 3.rowid + 1 3.rowid
3 3.rowid + 1 3.rowid
4 3.rowid + 1 3.rowid 以上sql步骤简化每考虑,理论思路上应该可以。
意义就不打了,个人意见。欢迎拍砖!不知道有没有误解你的思路?
明天回去试一下。
instance min_row max_row
1 1 6
2 4 9
3 5 10(根据你2的结果)
instance min_row max_row
1 1 5
2 6 10
(根据2的结果)备注:
第二条记录的min_row=6,是第一条max_row=5 +1 的结果。sql可以用延迟函数得到。
这样的题目最好用函数解决了,每组的平均个数是个点。
能有这样刁钻的题目也不常见,行变列,列变行都属这类。