0.
PLAN (C1 INDEX (CARRIES_IDX1, CARRIES_IDX5))SORT (JOIN (JOIN (JOIN (JOIN (C NATURAL, CORD INDEX (PK_CLIENTS_ORDERS)), TP INDEX (PK_TYPE_PAYMENTS), MACC INDEX (PK_MONEY_ACCOUNTS)), P INDEX (PK_PERSONAL_ACCOUNTS)), C1 INDEX (PK_CLIENTS)))
SET TERM ^ ;
create or alter procedure CALC_INC_OUTC
returns (
IS_COMMISSION smallint,
STATUS_COMISS smallint,
MACC_ID bigint,
TYPENAME varchar(256) character set WIN1251,
NUM bigint,
CHANGE_DATE timestamp,
MACC_NUM varchar(32) character set WIN1251,
CORD_ID bigint,
PACC_ID bigint,
SHORTNAME varchar(256) character set WIN1251,
ID_MACC bigint,
ID_CARR bigint,
CLIENT_ID bigint,
ID bigint,
NOTE varchar(256) character set WIN1251,
OUTCOMING double precision,
DEBET double precision,
KREDIT double precision,
INCOMING double precision,
DRAFT varchar(256) character set WIN1251,
BILLING varchar(256) character set WIN1251,
CORD_NUM varchar(32) character set WIN1251)
as
declare variable CLIENT bigint;
declare variable I bigint;
declare variable MACC_ID1 bigint;
declare variable INCOMING1 double precision;
begin
i=0;
for select tp.is_commission,tp.namerus typename,
cord.num,
(case
when billing_num is not null and billing_date is not null then billing_num || ' от ' || billing_date
when billing_num is not null and billing_date is null then billing_num
else null
end) billing,
(case
when draft_num is not null and draft_date is not null then draft_num || ' от ' || draft_date
when draft_num is not null and draft_date is null then draft_num
else null
end) draft,
(macc.amount - (select sum(dpower(-1, c1.direct)*c1.amount) from carries c1 where c1.macc_id=c.macc_id and c1.change_date>=c.change_date)) incoming,
(case c.direct when 2 then c.amount else 0 end) kredit,
(case c.direct when 1 then c.amount else 0 end) debet,
c.note, c.id,c1.id as client_id,c.id id_Carr,tp.id id_Macc,c1.shortname,macc.pacc_id, c.client_order_id cord_id, macc.num macc_num,
c.change_date, c.num,macc.id as macc_id,c.status_comiss
from money_accounts macc, type_payments tp,clients c1,personal_accounts p,carries c
left outer join clients_orders cord on (c.client_order_id=cord.id)
where macc.id = c.macc_id and tp.id = c.type_id and macc.pacc_id=p.id and p.client_id=c1.id
order by
c.macc_id
,c.change_date,c.id
into :is_commission,:typename,:cord_num,:billing,:draft,:incoming,:kredit,:debet,:note,:id,:client_id,:id_carr,:id_macc
,:shortname,:pacc_id,:cord_id,:macc_num,:change_date,:num,:macc_id,:status_comiss
do
begin
if ((i>0) and (client=:pacc_id) and (MACC_ID=:MACC_ID1)) then incoming=:outcoming;
outcoming=:incoming-:debet+:kredit;
client=:pacc_id;
MACC_ID1=:MACC_ID;
i=:i+1;
suspend;
end
end^
SET TERM ; ^
GRANT SELECT ON CARRIES TO PROCEDURE CALC_INC_OUTC;
GRANT SELECT ON MONEY_ACCOUNTS TO PROCEDURE CALC_INC_OUTC;
GRANT SELECT ON TYPE_PAYMENTS TO PROCEDURE CALC_INC_OUTC;
GRANT SELECT ON CLIENTS TO PROCEDURE CALC_INC_OUTC;
GRANT SELECT ON PERSONAL_ACCOUNTS TO PROCEDURE CALC_INC_OUTC;
GRANT SELECT ON CLIENTS_ORDERS TO PROCEDURE CALC_INC_OUTC;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO ADM;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO "ADMIN";
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO OPER;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO "PUBLIC";
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO ROMAN;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO ROOT;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO SYSDBA;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO TRADER;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO VLAD;
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO "WORK";
GRANT EXECUTE ON PROCEDURE CALC_INC_OUTC TO WORK1;
1. Количество записей money_accounts-8735, type_payments-35, clients-757, personal_accounts-662, carries-4548, clients_orders-436
2. Сейчас процедура выполняется
------ Performance info ------
Prepare time = 0ms
Execute time = 6s 141ms
Avg fetch time = 1,37 ms
Current memory = 1 046 920
Max memory = 1 080 440
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6 210 071