SQL & PL/SQL

Dear All,

When I found that conventional queries used to create procedures are getting slower, I tried an approach known as Collection. the example is :

create or replace procedure zlapaccrued01_iq
(
branch IN varchar2,
dates IN varchar2
)

AS


dtTodaysDate DATE;
szBranchID VARCHAR2(30);
type RC_Contract_Master is record(
arec_br_id CHAR(4),
arec_cont_no VARCHAR2(12),
arec_cont_ver NUMBER,
arec_prin NUMBER(18,5),
arec_intr NUMBER(18,5),
arec_corr_date DATE,
arec_cont_status VARCHAR2(2),
arec_intr_type VARCHAR2(2),
arec_intr_eff NUMBER(18,5),
arec_cust_no CHAR(12),
arec_first_inst_date DATE,
AREC_MIN_dtNextDay DATE DEFAULT TO_DATE('01-JAN-2050','DD-MON-RRRR'),
AREC_MAX_dtNextDay DATE DEFAULT TO_DATE('01-JAN-1910','DD-MON-RRRR'),
AREC_MIN_MAX_INST_DATE DATE,
AREC_MIN_MAX_INTR NUMBER ,
MAX_INST_PAID_SCHD DATE,
arec_MIN_max_inst NUMBER,
MAX_INST_PAID_HISTORY DATE,
MAX_INST_COLL_HISTORY DATE,
arec_max_date DATE,
arec_max_inst_no NUMBER(4) DEFAULT -1,
arec_ttl_prin NUMBER(18),
arec_ttl_intr NUMBER(18),
appl_objt_code char(3),
appl_ppd_date DATE,
appl_int_type CHAR(3),
appl_top_seasonal NUMBER,
inst_month NUMBER(5),
unpaid_prin NUMBER(18),
unpaid_intr NUMBER(18),
os_prin NUMBER(18),
os_intr NUMBER(18),
due_month NUMBER(4),
due_day NUMBER(18),
min_unpaid_date DATE,
min_unpaid_no NUMBER(4),
accrue_amt NUMBER(18),
nod_amt NUMBER(18),
accrue_status CHAR(1));

type TB_Contract_Master is table of RC_Contract_Master index by VARCHAR2(16);

ptb_contract_master TB_Contract_Master;

type RC_History is record
(
arec_br_id varchar2(4),
arec_cont_no number ,
arec_instl_no number,
arec_tran_prin NUMBER,
arec_tran_INT NUMBER,
AREC_INSTL_TRAN_SEQ NUMBER,
AREC_TRAN_DATETIME DATE,
AREC_REF_DATETIME DATE
) ;


type TB_History is table of RC_History index by VARCHAR2(26);
ptb_history TB_History;

lst_idx VARCHAR2(16);
dtNextDay DATE;
lst_hidx VARCHAR2(26);



begin

dtTodaysDate := TO_DATE(dates,'DD-MON-RRRR');
szBranchID := branch;

BEGIN
delete TEMP_ACCT_AREC_ACCRUED_new
where ACCT_BR_ID = szBranchID
and ACCT_ACCR_PERIOD = dtTodaysDate;
COMMIT ;
END ;

ptb_contract_master.delete;
ptb_history.delete;
dtNextDay := TRUNC(dtTodaysDate) + 1;

FOR rec_i IN
(
select a.cred_br_id,
a.cred_cont_no,
b.appl_objt_code,
a.cred_ppd_date,
b.appl_int_type
--b.appl_top_seasonal,
--DECODE(b.appl_int_type,'04',12 / b.appl_top_seasonal,1) InstMonth
from cred_ppd_cont a, cred_appl b
where a.cred_br_id = szBranchID
and a.cred_ppd_date < dtNextDay
and a.cred_deleted = 0
and a.cred_br_id = b.appl_br_id(+)
and a.cred_cont_no = b.appl_contract_no(+)
)
loop
lst_idx := rec_i.CRED_BR_ID || LPAD(rec_i.CRED_CONT_NO, 12, '0');

ptb_contract_master(lst_idx).arec_br_id := rec_i.CRED_BR_ID;
ptb_contract_master(lst_idx).arec_cont_no := rec_i.CRED_CONT_NO;

ptb_contract_master(lst_idx).accrue_amt := 0;
ptb_contract_master(lst_idx).nod_amt := 0;
ptb_contract_master(lst_idx).due_day := -1;

ptb_contract_master(lst_idx).arec_max_inst_no := 0;
ptb_contract_master(lst_idx).min_unpaid_no := 1000;
ptb_contract_master(lst_idx).min_unpaid_date := dtNextDay + 36500;
ptb_contract_master(lst_idx).arec_max_date := dtNextDay - 10000;

ptb_Contract_Master(lst_idx).appl_objt_code := rec_i.appl_objt_code;
ptb_Contract_Master(lst_idx).appl_ppd_date := rec_i.cred_ppd_date;
ptb_Contract_Master(lst_idx).appl_int_type := rec_i.appl_int_type;
--ptb_Contract_Master(lst_idx).appl_top_seasonal := rec_i.appl_top_seasonal;
--ptb_Contract_Master(lst_idx).inst_month := rec_i.InstMonth;

end loop;

I found that this method is faster than conventional query ( using Join, sub-queries, etc )

my question are :

1. What are advantages/disadvantages of this approach compared to conventional queries, or using temporary tables ?

2. For the query example below, the command : ptb_contract_master.DELETE(lst_idx). Is this command really deleted one record from memory or this record is only marked for deleted and wil be deleted after this looping process is completed?
Thank you all for the answers

lst_idx := ptb_contract_master.FIRST;
while lst_idx is not null loop
IF ptb_contract_master(lst_idx).arec_cont_status IN ('00','01') then
-- execute statements
ELSE
ptb_contract_master.DELETE(lst_idx) ;-- delete RECORD
END IF ;
lst_idx := ptb_contract_master.NEXT(lst_idx);
end loop;