Oracle數組基礎應用

1.CREATE OR REPLACE TYPE T_ARRAY AS VARRAY(1000) OF DATE;

 

2.  CREATE OR REPLACE FUNCTION F_RTNARRY
  (
     V_STR  VARCHAR2
  )
  RETURN T_ARRAY IS
     ARRY         T_ARRAY;
     V_BEGIN      DATE;
     V_END        DATE;
  Begin
     ARRY         := T_ARRAY();
     SELECT TRUNC(TO_DATE(V_STR,'YYYYMM'),'month') INTO V_BEGIN FROM DUAL;
     SELECT LAST_DAY(TO_DATE(V_STR,'YYYYMM')) INTO V_END FROM DUAL;
     ARRY.EXTEND(1);
     ARRY(1) := V_BEGIN;
     ARRY.EXTEND(1);
     ARRY(2) := V_END; 
     RETURN ARRY;
  End F_RTNARRY;

3.  PROCEDURE P
  (
    V_STR VARCHAR2,
    O_CUR OUT MYTYPE
  )
  AS
    ARRY T_ARRAY;
    V_BEGIN DATE;
    V_END   DATE;
  BEGIN
    SELECT F_SPLITSTR(V_STR) INTO ARRY FROM DUAL;
    V_BEGIN := ARRY(1);
    V_END   := ARRY(2);
    OPEN O_CUR FOR
      SELECT * FROM TableName WHERE *** BETWEEN V_BEGIN AND V_END;
  END;