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; /