PL/SQL Collection : VARRAY and it's collection Methods Usage

PL/SQL Collection : VARRAY and it's collection Methods Usage

Collection Methods are used to manipulate Varray's

Collection Methods on VARRAY

1. count
2. limit
3. trim
4. next
5. prior
6. delete
7. extends
8. first
9. last

Here is a procedure which uses all collection methods

create or replace procedure xx_varray as -- Declare a user defined datatype Varray TYPE VarTab IS VARRAY(10) of NUMBER; -- Declare a variable of varray type v1 VarTab; i number; j number; BEGIN /* Initialize the varray using constructor VarTab() is constructor which takes same name as collection which is mandatory! */ v1 := VarTab(); --Limit gives maximum size of collection dbms_output.put_line('Max size of Varray : '||v1.limit); dbms_output.put_line(' '); --Count gives no of elements present in a collection dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --Assigning values to varray by extending till it's limit for i in 1..10 loop v1.extend(); v1(i):=i+100; end loop; -- to traverse from 1st element to last element use -- first and last methods dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --v1.delete; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM method'); v1.trim; --trims last element in the varray dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM(n) Method'); v1.trim(2);--trims or deletes last 2 elements dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --printing varray elements after trimming for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --Using Next and Prior Methods, these returns index values j:=3; dbms_output.put_line('next(3) : '||v1.next(3)); dbms_output.put_line(' '); dbms_output.put_line('prior(3) : '||v1.prior(3)); --Using method EXISTS(n), returns TRUE if element exists at index - n --else returns FALSE if (v1.exists(6)) then dbms_output.put_line('Element exists at index 6'); else dbms_output.put_line('Element doesnt exist at index 6'); end if; if (v1.exists(9)) then dbms_output.put_line('Element exists at index 9'); else dbms_output.put_line('Element doesnt exist at index 9'); end if; --Using DELETE method, deletes complete collection v1.delete; dbms_output.put_line(' '); dbms_output.put_line('No of elements in varray after DELETE Method : '||v1.count); dbms_output.put_line(' '); dbms_output.put_line('Again if we need to add an element use extend method'); v1.extend(); v1(1):=101; dbms_output.put_line(v1(1)); --Using EXTENDS(n,i) appends n copies of the ith element to a collection. v1.extend(3,1); dbms_output.put_line('Check the Varray Now: '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --We can traverse Varray using next Collection method for printing/accessing collection elements j:=1; WHILE j is NOT NULL LOOP dbms_output.put_line(v1(j)); j:=v1.next(j); end loop; EXCEPTION When OTHERS then dbms_output.put_line(sqlerrm); END; /