PL学习-Collection

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER IS
  TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  FUNCTION Init_My_AA RETURN My_AA;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types IS
  FUNCTION Init_My_AA RETURN My_AA IS
    Ret My_AA;
  BEGIN
    Ret(-10) := ‘-ten‘;
    Ret(0) := ‘zero‘;
    Ret(1) := ‘one‘;
    Ret(2) := ‘two‘;
    Ret(3) := ‘three‘;
    Ret(4) := ‘four‘;
    Ret(9) := ‘nine‘;
    RETURN Ret;
  END Init_My_AA;
END My_Types;
/
DECLARE
  v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();
BEGIN
  DECLARE
    Idx PLS_INTEGER := v.FIRST();
  BEGIN
    WHILE Idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, ‘999‘)||LPAD(v(Idx), 7));
      Idx := v.NEXT(Idx);
    END LOOP;
  END;
END;
/

collection_type ( [ value [, value ]... ] )

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
  team Foursome := Foursome();  -- initialize to empty
 
  PROCEDURE print_team (heading VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
 
    IF team.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE(‘Empty‘);
    ELSE 
      FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE(i || ‘.‘ || team(i));
      END LOOP;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE(‘---‘); 
  END;
 
BEGIN
  print_team(‘Team:‘);
  team := Foursome(‘John‘, ‘Mary‘, ‘Alberto‘, ‘Juanita‘);
  print_team(‘Team:‘);
END;
/