PL/SQL配列・ループテスト

PL/SQLの配列=コレクション型 に詳細がある。配列は1オリジン。配列のメソッドについては、 配列で使用可能なメソッド が詳しい。

/*
 * C:\> C:\ORACLE\PRODUCT\XX.Y.Z\CLIENT_1\bin\sqlplus.exe USER/PASSWORD@SCHEMA
 * SQL> SET SERVEROUTPUT ON
 */

DECLARE
  TYPE tCodeArray IS VARRAY(100) OF TABLE_A.ID%TYPE;
  vOperCodes tCodeArray := tCodeArray('select', 'insert', 'update', 'delete');
  
  PROCEDURE testProcedure (num IN NUMBER, kind IS VARCHAR2) IS
    vNumber VARCHAR(10);
    vId     TABLE_A.ID%TYPE;
    vName   TABLE_A.NAME%TYPE;
  BEGIN
    vNumber := TO_CHAR(num, 'FM0000');
    vId := 'TEST' || vNumber;
    vName := 'テスト' || TO_MULTI_BYTE(vNumber);
    IF (kind = 'insert') THEN
      DBMS.OUTPUT.PUT_LINE('Inserting to XXXX was succeeded. ID='
          || vId || ' NAME=' || vName);
    ELSIF (kind = 'delete') THEN
      DBMS.OUTPUT.PUT_LINE('Deleting from XXXX was succeeded. ID='
          || vId || ' NAME=' || vName);
    ELSE
      DBMS.OUTPUT.PUT_LINE('Not execute');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS.OUTPUT.PUT_LINE('Error occured. ID=' || vId);
  END;

BEGIN
  FOR i IN 1 .. vOperCodes.count LOOP
    IF (MOD(i, 2) = 0 OR i = 3) THEN
      testProcedure(i, vOperCodes(i));
    ELSE
      DBMS.OUTPUT.PUT_LINE('Not target=' || vOperCodes(i));
    END IF;
  END LOOP;
END;
/