题目

不理解oracle 的function是怎么用的,
CREATE OR REPLACE TYPE obj_NA_Issued_Exclude_NILNA
AS OBJECT
(
TOTAL_CASES VARCHAR2(500),
OBJECTED_CASES VARCHAR2(500),
NA_ISSUED_MONTH VARCHAR2(20),
PERCENTAGE VARCHAR2(12),
MET_TARGET VARCHAR2(10)
)
/
CREATE OR REPLACE TYPE tbl_NA_Issued_Exclude_NILNA
AS TABLE OF obj_NA_Issued_Exclude_NILNA
/
FUNCTION getNAIssuedExcludeNILNA(
NAIssuedDateFrom VARCHAR2,
NAIssuedDateTo VARCHAR2,
KPIObjectionRate VARCHAR2)
RETURN tbl_NA_Issued_Exclude_NILNA
IS
NA_Issued_Exclude_NILNA_TBL tbl_NA_Issued_Exclude_NILNA := tbl_NA_Issued_Exclude_NILNA();
CURSOR NA_Issued_Exclude_NILNA_CUR IS
SELECT * FROM DUAL;
BEGIN
FOR NA_Issued_Exclude_NILNA_REC IN NA_Issued_Exclude_NILNA_CUR
LOOP
NA_Issued_Exclude_NILNA_TBL.EXTEND;
NA_Issued_Exclude_NILNA_TBL(NA_Issued_Exclude_NILNA_TBL.LAST) := obj_NA_Issued_Exclude_NILNA(NA_Issued_Exclude_NILNA_REC.TOTAL_CASES,NA_Issued_Exclude_NILNA_RE

提问时间：2021-12-12

答案

这是个对象表的例子.
FUNCTION getNAIssuedExcludeNILNA(
NAIssuedDateFrom VARCHAR2,
NAIssuedDateTo VARCHAR2,
KPIObjectionRate VARCHAR2)
RETURN tbl_NA_Issued_Exclude_NILNA
声明函数getNAIssuedExcludeNILNA(),参数为...,返回值为tbl_NA_Issued_Exclude_NILNA类型（对象表）.
IS
NA_Issued_Exclude_NILNA_TBL tbl_NA_Issued_Exclude_NILNA := tbl_NA_Issued_Exclude_NILNA();
CURSOR NA_Issued_Exclude_NILNA_CUR IS
SELECT * FROM DUAL;
初始化参数：tbl_NA_Issued_Exclude_NILNA为对象表NA_Issued_Exclude_NILNA_TBL的实例；NA_Issued_Exclude_NILNA_CUR为一个游标.
BEGIN
FOR NA_Issued_Exclude_NILNA_REC IN NA_Issued_Exclude_NILNA_CUR
LOOP
NA_Issued_Exclude_NILNA_TBL.EXTEND;
NA_Issued_Exclude_NILNA_TBL(NA_Issued_Exclude_NILNA_TBL.LAST) := obj_NA_Issued_Exclude_NILNA(NA_Issued_Exclude_NILNA_REC.TOTAL_CASES, NA_Issued_Exclude_NILNA_REC.OBJECTED_CASES, NA_Issued_Exclude_NILNA_REC.NA_ISSUED_MONTH, NA_Issued_Exclude_NILNA_REC.PERCENTAGE, NA_Issued_Exclude_NILNA_REC.MET_TARGET);
END LOOP;
函数的主体：
循环：每次从NA_Issued_Exclude_NILNA_CUR中取出一条记录到NA_Issued_Exclude_NILNA_REC中.
循环内部：NA_Issued_Exclude_NILNA_TBL.EXTEND;将对象表的实例扩展一条.
NA_Issued_Exclude_NILNA_TBL(NA_Issued_Exclude_NILNA_TBL.LAST) := obj_NA_Issued_Exclude_NILNA(NA_Issued_Exclude_NILNA_REC.TOTAL_CASES, NA_Issued_Exclude_NILNA_REC.OBJECTED_CASES, NA_Issued_Exclude_NILNA_REC.NA_ISSUED_MONTH, NA_Issued_Exclude_NILNA_REC.PERCENTAGE, NA_Issued_Exclude_NILNA_REC.MET_TARGET);
为对象表实例的最后一条（刚刚扩展出来的）赋值.
RETURN NA_Issued_Exclude_NILNA_TBL;
返回对象表的实例NA_Issued_Exclude_NILNA_TBL.
函数体结束.