Как сделать селект с table type Oracle
Объявление типа:
TYPE tp_loan IS RECORD(
id mc_account.id%TYPE,
colvir_id mc_account.colvir_id%TYPE,
iban mc_account.iban%TYPE,
nearest_pay_date DATE,
nearest_pay_amount NUMBER(38));
TYPE tp_loan_list IS TABLE OF tp_loan INDEX BY PLS_INTEGER;
Заполнение type: t_loan_list tp_loan_list;
select ac.id,
ac.colvir_id,
ac.iban,
pkg_mb_model_credit.get_nearest_pay_date(ac.colvir_id),
pkg_mb_model_credit.get_nearest_pay_amount(ac.colvir_id)
bulk collect into t_loan_list
from mc_account ac
where ac.iban = r_account.repay_account
AND ac.smodule <> '01'
AND ac.mc_agreement_id = r_agreement.id
AND ac.status = 1
AND ac.access_level >= n_access_level;
Как сделать селект с этого типа?:
select sum(t.nearest_pay_amount)
from TABLE(t_loan_list) t
where (t.nearest_pay_date < r_account.next_pay_date
OR (t.nearest_pay_date = r_account.next_pay_date
AND t.id < i.id));