24th Example - VARRAY on PL/SQL

In the following example will be demonstrated using VARRAY data type. VARRAY is one of the collection types in Oracle Database. The principle works the same as an associative array that manages a set of data in an array that has a specified index. Here is an example of PL/SQL block that uses VARRAY:

DECLARE
     TYPE tipe_varray IS VARRAY(5) OF VARCHAR2(24);
     nama tipe_varray;
     jumlah NUMBER;
BEGIN
     nama := tipe_varray(&input_nama);-- enter 5 names, eg: 'one', 'two', 'three', 'four', 'five'

jumlah := nama.count;
     FOR i IN 1..jumlah LOOP
          DBMS_OUTPUT.PUT_LINE (i||' - '||nama(i));
     END LOOP;
END;
/

In the example above, there is a VARRAY type with name tipe_varray that can accommodate a maximum of 5 values of type VARCHAR with maximum length 24 characters. Variable nama that will hold the array values will be entered in the variable substitution &input_nama. Variable number will count the number of array index. Using FOR .. LOOP looping the array variable nama will be separated and displayed to the screen.

No comments

Powered by Blogger.