Применение таблиц PL/SQL.

--Вариант вставки в TABLE целой строки таблицы saldo

DECLARE
TYPE saldo_table IS TABLE of saldo%rowtype INDEX BY binary_integer;
saldo_tbl saldo_table ;
i number;
BEGIN
select * INTO saldo_tbl(1) FROM saldo WHERE id_isubacc=141 AND day='01.12.94';
DBMS_OUTPUT.PUT_LINE(saldo_tbl(1).day||' '||saldo_tbl(1).saldo||' '|| saldo_tbl.count);
end;

--Вариант вставки в TABLE столбца day таблицы saldo

DECLARE
TYPE saldo_table IS TABLE of saldo.day%TYPE INDEX BY binary_integer;
saldo_tbl saldo_table ;
BEGIN
select day INTO saldo_tbl(1) FROM saldo WHERE id_isubacc=141 AND day='01.12.94';
DBMS_OUTPUT.PUT_LINE(saldo_tbl(1)||' '|| saldo_tbl.count);
end;

--Вариант вставки в TABLE результатов запроса используя RECORD (одиночная запись)

DECLARE
TYPE cl_rec IS RECORD (id_cl client.id_client%type, grp_cnt country.GROUP_CNT%type);
TYPE cl_table IS TABLE of cl_rec INDEX BY binary_integer;
cl_tbl cl_table ;
BEGIN
SELECT id_client,GROUP_CNT INTO cl_tbl(1).id_cl,cl_tbl(1).grp_cnt
FROM client,country
WHERE id_client=100 AND client.id_country=country.ID_COUNTRY;
DBMS_OUTPUT.PUT_LINE(cl_tbl(1).id_cl||' '||cl_tbl(1).grp_cnt||' '|| cl_tbl.count);
end;

--Вариант вставки в TABLE результатов запроса используя RECORD (все строки)

DECLARE
TYPE cl_rec IS RECORD (id_cl client.id_client%type, grp_cnt country.GROUP_CNT%type);
TYPE cl_table IS TABLE of cl_rec INDEX BY binary_integer;
cl_tbl cl_table ;
i number:=0;
BEGIN
FOR cl_cur IN
(SELECT id_client,GROUP_CNT FROM client,country
WHERE client.id_country=country.ID_COUNTRY) LOOP
i:=i+1;
cl_tbl(i).id_cl:=cl_cur.id_client;
cl_tbl(1).grp_cnt:=cl_cur.GROUP_CNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(cl_tbl(1).id_cl||' '||cl_tbl(1).grp_cnt||' '|| cl_tbl.count);
end;

Применение таблиц. (конкретный пример)

/* Исходный запрос. В процедуре идёт с небольшими вариациями счетов 10 раз подряд. Выполняется каждый раз с учётом кэша 0,59 сек. Итого 6 сек.

SELECT nvl(sum(decode(i.type_isubacc,0,1,i.type_isubacc)*i.saldo),0)
into ss
FROM ISUBACC i, SUBACC_MAPPING sm, SUBACC s
WHERE i.id_isubacc=sm.id_isubacc AND sm.id_subacc=s.id_subacc
AND i.id_cur=l_id_cur AND sm.id_list_acc=l_id_list_acc AND
(subacc LIKE '2502%' OR subacc LIKE '2512%' OR subacc LIKE '2513%'
OR subacc LIKE '2514%' OR subacc LIKE '2520%' OR subacc LIKE '2523%'
OR subacc LIKE '2525%' OR subacc LIKE '2526%' OR subacc LIKE '2530%'
OR subacc LIKE '2531%' OR subacc LIKE '2540%' OR subacc LIKE '2541%'
OR subacc LIKE '2542%' OR subacc LIKE '2543%' OR subacc LIKE '2544%'
OR subacc LIKE '2545%' OR subacc LIKE '2546%' OR subacc LIKE '2550%'
OR subacc LIKE '2551%' OR subacc LIKE '2552%' OR subacc LIKE '2553%'
OR subacc LIKE '2554%' OR subacc LIKE '2555%' OR subacc LIKE '2560%'
OR subacc LIKE '2561%' OR subacc LIKE '2562%' OR subacc LIKE '2565%'
OR subacc LIKE '2570%' OR subacc LIKE '2571%' OR subacc LIKE '2600%'
OR subacc LIKE '2603%' OR subacc LIKE '2604%' OR subacc LIKE '2620%'
OR subacc LIKE '2622%'
OR subacc LIKE '2602%' OR subacc LIKE '2611%' OR subacc LIKE '3630%'
OR subacc LIKE '3631%' OR subacc LIKE '3739%');

Запись в массив интересующей половины ISUBACC ~0,7 сек
выполнение запроса аналогичному исходному из массивов - 0,05 сек
Итого 0,7 + 10*0,05 = 1,2 сек

Вывод:

1. В случае, если идут подряд много однотипных сложных запросов или много различных запросов к одной и той же таблице, то это очень эффективно по времени
2. Во многих случаях позволяет объединять в одном цикле несколько запросов (например получить остатки сразу по нескольким группам счетов, как в данном случае)
3. В случае долгого выполнения процедуры позволяет добиться меньшей противоречивости данных, так как чтение всей информации из таблиц происходит только в начале работы процедуры

DECLARE
CURSOR isub IS
SELECT FROM i.ID_ISUBACC ID_ISUBACC, substr(s.SUBACC,1,4) bal_acc, i.id_cur id_cur,
i.TYPE_ISUBACC*i.saldo saldo, i.rowid rwid
isubacc i, subacc_mapping sm, subacc s
WHERE i.ID_ISUBACC=sm.ID_ISUBACC AND sm.ID_SUBACC=s.ID_SUBACC
AND sm.ID_LIST_ACC=2
AND i.d_close IS NULL AND i.saldo<>0 --дальше указать все б/счета из процедуры
AND (subacc LIKE '2502%' OR subacc LIKE '2512%' OR subacc LIKE '2513%'
OR subacc LIKE '2514%' OR subacc LIKE '2520%' OR subacc LIKE '2523%'
OR subacc LIKE '2525%' OR subacc LIKE '2526%' OR subacc LIKE '2530%'
OR subacc LIKE '2531%' OR subacc LIKE '2540%' OR subacc LIKE '2541%'
OR subacc LIKE '2542%' OR subacc LIKE '2543%' OR subacc LIKE '2544%'
OR subacc LIKE '2545%' OR subacc LIKE '2546%' OR subacc LIKE '2550%'
OR subacc LIKE '2551%' OR subacc LIKE '2552%' OR subacc LIKE '2553%'
OR subacc LIKE '2554%' OR subacc LIKE '2555%' OR subacc LIKE '2560%'
OR subacc LIKE '2561%' OR subacc LIKE '2562%' OR subacc LIKE '2565%'
OR subacc LIKE '2570%' OR subacc LIKE '2571%' OR subacc LIKE '2600%'
OR subacc LIKE '2602%' OR subacc LIKE '2603%' OR subacc LIKE '2604%'
OR subacc LIKE '2605%' OR subacc LIKE '2610%' OR subacc LIKE '2611%'
OR subacc LIKE '2615%' OR subacc LIKE '2620%' OR subacc LIKE '2622%'
OR subacc LIKE '2640%' OR subacc LIKE '2641%' OR subacc LIKE '2643%'
OR subacc LIKE '3630%' OR subacc LIKE '3631%' OR subacc LIKE '3739%');

--организуем интересующие нас простые массивы,( или один на основе RECORDS с 3-мя полями)

TYPE t_bal IS TABLE of varchar2(4) INDEX BY BINARY_INTEGER;
TYPE t_int IS TABLE of number INDEX BY BINARY_INTEGER;
TYPE t_cr IS TABLE of isubacc.id_cur%type INDEX BY BINARY_INTEGER;
-- TYPE t_row IS TABLE of rowid INDEX BY BINARY_INTEGER; --это работает, но тут не нужно
t_bal_acc t_bal;
t_saldo t_int;
t_cur t_cr;
-- t_rowid t_row;

ss number:=0;
sm number:=0;
t number;
s2610 number:=0;
BEGIN

--заполняем массивы
FOR cur_isub IN isub LOOP
t_bal_acc(cur_isub.id_isubacc):=cur_isub.bal_acc;
t_saldo(cur_isub.id_isubacc):=cur_isub.saldo;
t_cur(cur_isub.id_isubacc):=cur_isub.id_cur;
-- t_rowid(cur_isub.id_isubacc) :=cur_isub.rwid;
END LOOP;
ss:=dbms_utility.get_time; -- замер времени выполнения в 0,01 сек

t:= t_bal_acc.first; -- выполняем запрос для 2-х групп счетов
LOOP
IF t_bal_acc(t) IN (2502, 2512, 2513, 2514, 2520, 2523, 2525, 2526,
2530, 2531, 2540, 2541, 2542, 2543, 2544, 2545, 2546, 2550,
2551, 2552, 2553, 2554, 2555, 2560, 2561, 2562, 2565, 2570,
2571, 2600, 2603, 2604, 2620, 2622, 2602, 2611, 3630, 3631, 3739)
AND t_cur(t)=1
THEN
sm:=t_saldo(t)+sm;
ELSIF t_bal_acc(t)=2610 AND t_cur(t)=1
THEN
s2610:=t_saldo(t)+s2610;
END IF ;
EXIT WHEN t=t_bal_acc.last;
t:=t_bal_acc.next(t);
END loop;

ss:=dbms_utility.get_time-ss;
DBMS_OUTPUT.PUT_LINE(sm||' '||ss||' '||s2610);
END;

Хостинг от uCoz