example
Code:DECLARE type ref_cursor is ref cursor; c1 ref_cursor; cursor ch_cursor is select co_id, ch_seqno, ch_status, userlastmod, entdate from contract_history ch1 where rownum = 1; r_contract_histroy ch_cursor%rowtype; fr_entdate CONSTANT DATE := trunc(sysdate - 7); to_entdate CONSTANT DATE := trunc(sysdate - 1); BEGIN OPEN c1 for 'select co_id, ch_seqno, ch_status, userlastmod, entdate from (select co_id, ch_seqno, ch_status, userlastmod, entdate, dense_rank() over (partition by co_id order by ch_seqno desc) rango from contract_history where co_id < 11 and trunc(entdate) <= :to_entdate) iv_ch where rango = 1 or (ch_seqno = 1 and trunc(entdate) <= :to_entdate) order by co_id, ch_seqno' using to_entdate, to_entdate; LOOP FETCH c1 into r_contract_histroy; EXIT when c1%notfound; dbms_output.put_line(r_contract_histroy.co_id || r_contract_histroy.ch_seqno || r_contract_histroy.ch_status || r_contract_histroy.userlastmod || r_contract_histroy.entdate); END LOOP; CLOSE c1; END; /




Reply With Quote