SQL Record-plsql array

PL/SQL arrays

The PL/SQL programming language provides a data structure called Varray, which stores a constant-sized contiguous set of elements of the same type. Varray is used to store an ordered collection of data, but it is often more useful to think of an array as a set of variables of the same type.

All mutable arrays are stored by contiguous locations. The lowest address corresponds to the first element and the last element of the highest address.

An array is part of the data for all collection types, and it represents a variable-sized array. We will look at the other collection types in the later "PL/SQL Collections" section.

In a Varray each element has an index associated with it. It also has the ability to dynamically change a maximum size.

Create Varray Type

A varray type is created with the CREATE TYPE statement. You must specify a maximum length and store the type of the Varray element.

Basic syntax for creating a Vrray type at the schema level:

CREATEORis varray(n)of<element_type>   

Over here

• Varray_type_name is a valid property name.

• n is the number of Varray elements (maximum value)

• Element_type is the data type of the elements of the array.

The maximum length of the Varray can be changed by using the ALTER TYPE statement.

For example

CREATEOras varray(3)of VARCHAR2(ten);  /Type created.             

PL/SQL Blocks create the basic syntax for the Vrray type:

is varray(n)of<element_type>       

Example

is varray(5)of VARCHAR2(ten);  is varray(5)of INTEGER;          

Example 1

The following program illustrates the use of mutable arrays:

DECLAREType NamesarrayIsVarray(5) OfVARCHAR2(10);Type gradesIsVarray(5) OfINTEGER;Names Namesarray;Marks grades;Total integer;BEGINNames:=Namesarray(' Kavita ', ' Pritam ', ' Ayan ', ' Rishav ', ' Aziz ');Marks:=Grades(98, 97, 78, 87, 92);Total:=Names.Count;Dbms_output.Put_Line(' Total '||Total|| ' Students '); ForIin 1 .  Total LOOP dbms_output. Put_line ( ' Student: '  | |  Names (i| |   ' Marks: '  | |  Marks (iend Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

Student:kavita  marks:98student:pritam  marks:97student:ayan  marks:78student:rishav  Marks: 87student:aziz  Marks:92pl/sql procedure successfully completed.

Please note:

• In an Oracle environment, the starting index of a variable group is always 1

• You can initialize the Varray element by using the Varray type, which has the same name as the variable-length array

• A variable group is a one-dimensional array

• A varray when it is declared automatically as a null value, it must be initialized before its elements can be referenced

Example 2

The Varray element can also be any field%type any database table or%rowtype database table. The following example illustrates this concept:

We will use the Customers table stored in the database:

Select * FromCustomers;+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+| 1 |Ramesh| 32 |Ahmedabad| 2000.00 || 2 |Khilan| 25 |Delhi| 1500.00 || 3 |Kaushik| 23 |Kota| 2000.00 || 4 |Chaitali| 25 |Mumbai| 6500.00 || 5 || 27 || 8500.00 ||  6 || 22 ||4500.00 |+----+----------+-----+-----------+----------+       

The following is an example of using a tour indicator:

DECLARE   CURSORC_customersIs SELECTNameFromCustomers;Type C_listIsVarray(6) OfCustomers.Name%Type;Name_list c_list:=C_list();Counter integer:=0;BEGIN ForNInchC_customers LOOP counter:=Counter+ 1;Name_list.extend; Name_list ( Counter  := N.name; Dbms_output. ( "Customer" | | counter | | | | name_list (counterend Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

Customer (1): Ramesh customer (2): Khilan customer (3): Kaushik    Customer (4): Chaitali customer (5): Hardik Customer (6): Komalpl/sql procedure successfully completed.

SQL Record-plsql array