create table tmp_test (
--k number(10) primary key,
n01 number(10),
n02 number(10),
n03 number(10),
n04 number(10),
n05 number(10),
n06 number(10),
n07 number(10),
n08 number(10),
n09 number(10),
n10 number(10),
n11 number(10),
n12 number(10),
n13 number(10),
n14 number(10),
n15 number(10),
n16 number(10),
n17 number(10),
n18 number(10),
n19 number(10),
n20 number(10)
);
insert into tmp_test
with
a as (
select rownum n from dual connect by rownum <= 1e3
)
select n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n from a;
commit;
declare
gr number := 1;
s varchar2 (32767);
n number;
r rowid;
tot number := 0;
fast number := 1;
begin
with
a as (
select max(rowid) rid
from tmp_test
group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
),
b as (
select rid, rownum rn from a
)
select rid
into r
from b where rn = gr;
for x in 1 .. 1e1
loop
s := 'select sum (%1) from tmp_test where rowid = :1 /*%2*/';
for c in 1 .. 20
loop
if bitand (x, power (2, c-1)) > 0 or fast=1
then
s := replace (s, '%1', 'n' || lpad(c, 2, '0') || '+%1');
end if;
end loop;
if instr(s, '+%1') > 0
then
s := replace (s, '+%1');
if fast=1
then
s := replace (s, '%2', gr);
end if;
--DBMS_OUTPUT.PUT_LINE(s);
execute immediate s into n using r;
tot := tot + n;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(tot);
-- 572966090
end;
/
Telegram: DB developers channel