oracle plsql总结

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;