CREATE OR REPLACE FUNCTION F_O_GET_CONTACT_ADDR(M_CUST_ORDER_ID VARCHAR2,
M_ORDER_ITEM_ID VARCHAR2,
M_REL_TYPE VARCHAR2)
RETURN VARCHAR2 IS
RESULT VARCHAR2(200);
P_LOCATION_ID CUST.LOCATION_ID%TYPE;
P_CUST_ADDRESS CUST.CUST_ADDRESS%TYPE;
LN POLITICAL_LOCATION.LOCATION_NAME%TYPE;
TYPE LN_CURSOR_TYPE IS REF CURSOR;
LN_CURSOR LN_CURSOR_TYPE;
TYPE LN_ARRAY_TYPE IS VARRAY(10) OF VARCHAR2(30);
LN_ARRAY LN_ARRAY_TYPE := LN_ARRAY_TYPE();
I NUMBER(1) := 1;
BEGIN
BEGIN
SELECT C.CUST_ADDRESS, C.LOCATION_ID
INTO P_CUST_ADDRESS, P_LOCATION_ID
FROM CUST C
WHERE C.CUST_ID =
(SELECT CO.CUST_ID
FROM CUSTOMER_ORDER CO
WHERE CO.CUST_ORDER_ID = M_CUST_ORDER_ID);
OPEN LN_CURSOR FOR 'SELECT PL.LOCATION_NAME
FROM POLITICAL_LOCATION PL
START WITH PL.LOCATION_ID = ' || P_LOCATION_ID || 'CONNECT BY PRIOR PL.UP_LOCATION_ID = PL.LOCATION_ID';
LOOP
FETCH LN_CURSOR
INTO LN;
EXIT WHEN LN_CURSOR%NOTFOUND;
LN_ARRAY.EXTEND(1);
LN_ARRAY(I) := LN;
I := I + 1;
END LOOP;
CLOSE LN_CURSOR;
FOR I IN REVERSE LN_ARRAY.FIRST .. LN_ARRAY.LAST LOOP
SELECT NVL2(RESULT, RESULT || ',' || LN_ARRAY(I), LN_ARRAY(I))
INTO RESULT
FROM DUAL;
END LOOP;
RESULT := RESULT || ',' || P_CUST_ADDRESS;
EXCEPTION
WHEN OTHERS THEN
RESULT := NULL;
END;
RETURN(RESULT);
END F_O_GET_CONTACT_ADDR;
用count判断联合数组（associative array），因为index-by table声明就初始化，用count判断不会引起异常。
用cardinality判断嵌套表（nested table），因为nested table声明时如果没有被初始化，用count判断会引起异常，而nested table的最小下标不一定是1（经过delete后），所以不能用exists(1)判断 。只能用cardinality。
用exists(1)判断varray，因为varray声明时如果没有被初始化，用count判断会引起异常，没有cardinality函数，而varray的最小下标是1，所以可以用exists(1)判断 。
示例代码：
DECLARE
TYPE test_varray IS varray(2) OF varchar2(10);
test_id_tab1 test_varray;
TYPE test_tab IS table OF varchar2(10);
test_id_tab2 test_tab;
TYPE test_itab IS table OF varchar2(10)index by binary_integer;
test_id_tab3 test_itab;
cnt integer;
ext boolean;
BEGIN
ext:= test_id_tab1.exists(1);
if ext then
DBMS_OUTPUT.PUT_LINE ('exist' );
else
DBMS_OUTPUT.PUT_LINE ('not exist' );
end if;
cnt:= cardinality(test_id_tab2);
DBMS_OUTPUT.PUT_LINE ('count' || cnt);
cnt:= test_id_tab3.count;
DBMS_OUTPUT.PUT_LINE ('count' || cnt);
END;