type t_cursor is ref cursor;
TYPE outrec_typ IS RECORD (
partsName VARCHAR2(50),
totalRepCount NUMBER(15),
totalRepAmt NUMBER(15,4),
pcntOfTolEstAmt NUMBER(15, 4),
accid VARCHAR2(50),
seq_index integer
);
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION accessory_res_funs(
startDate in varchar2,
endDate in varchar2,
appraiserIds in varchar2,
accessoryIds IN VARCHAR2,
displayCondition in NUMBER
)
RETURN outrecset PIPELINED;
---------------------------------
FUNCTION accessory_res_funs(
startDate in varchar2,
endDate in varchar2,
appraiserIds in varchar2,
accessoryIds IN VARCHAR2,
displayCondition in NUMBER
)
RETURN outrecset PIPELINED AS out_rec outrec_typ;
rec_index NUMBER := 1;
cursor results_cur IS with
。。。。。。。。
SELECT * FROM
(
SELECT rownum AS row_num, rt.*
FROM TABLE(SQUENCE_FUNC(accessoryIds,',')) ptt, result_tbl_tmp2 rt
WHERE ptt.seq_ref = rt.accid
ORDER BY ptt.seq_index
);
BEGIN
FOR rowD IN results_cur LOOP
EXIT WHEN results_cur%NOTFOUND;
out_rec.partsName := rowD.partsName;
out_rec.totalRepCount := rowD.totalRepCount;
out_rec.totalRepAmt := rowD.totalRepAmt;
out_rec.pcntOfTolEstAmt := rowD.pcntOfTolEstAmt;
out_rec.accid := rowD.accid;
out_rec.seq_index := rec_index;
rec_index := rec_index + 1;
PIPE ROW (out_rec);
END LOOP;
RETURN;
END;