EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL Array
Secondary Sidebar
Java NIO Scatter/Gather

Java 11

Java NIO File

Bootstrap 4 Datepicker

Java Project Maven

Java NIO Path

PLSQL Array

PLSQL Array

Introduction to PLSQL Array

PL/ SQL array is the provision to store multiple value of same type in a particular variable whose datatype is VARRAY. PL/ SQL provides us with a special datatype named VARRAY to implement the arrays. We can specify the fixed size of the VARRAY which will help us specify the number of elements that can be stored in that array. In this article, we will study about the syntax of using the VARRAY, the usage and internal working and also implementation along with the help of certain examples.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,130 ratings)

Syntax and Working of PLSQL Array

The arrays in PL/ SQL created by using VARRAY datatype reserves the sequential storage space in the memory which means that all the elements which we are going to store will be kept in memory in contagious format one after the other. The VARRAY datatype can store only one-dimensional arrays which are not sparse that means no gaps in between the two or more elements and are bounded. The syntax of declaring and using the VARRAT datatype for arrays in PL/ SQL programming is as shown below –

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

TYPE array name IS VARRAY(number of elements) OF type of element [NOT NULL];

In the above syntax,

  • Array name: array name is the name of the array which you want to create using which you will refer it further in the programming. Note that in oracle indexing starts from 1. Each element in array is associated with index which helps to access it inside that array. This means that any element in the array can be accessed by using its name and the index value.
  • Number of elements: This helps you to specify the size of the datatype VARRAY which will tell about the fixed number of elements that can be stored inside that particular variable whose datatype is VARRAY.
  • Type of element: This will tell us about what type of values are going to be stored inside the array which we are creating. That array can have string values, number values, Boolean or any other type. Note that a single array can contain only one type of element in it.
  • NOT NULL: This is the optional specification while declaring the array. If we specify the NOT NULL attribute then all the elements which will be stored in the array should not have null value in them.
  • Declare global array: We can create an array that can be accessed globally throughout the database and not only in PL/ SQL. For this we can make the use of the following syntax –

CREATE [OR REPLACE ] TYPE name of array AS | IS
VARRAY (number of elements) OF type of element [NOT NULL];

In the above syntax if we specify the OR REPLACE clause and there is any existence of the same-named variable of varray datatype then it is replaced but its granted privileges are still persisting.

  • Initializing the VARRAY instance: If creation and declaration of varray type is completed by using above procedure, we can initialize and create the instance of that type by using the following syntax –

Name of varray                 type of the varray              [:=type of varray(values….)];

In the above syntax,

  • Type of varray(): This is the constructor which ca n accept the values which we want to store inside our array. Note that the values specified as the arguments to this constructor should be comma separated. In case, if we have not initialized the VARRAY before using it, we will receive an error while using it in code or somewhere else which is as shown below – We can initialize the VARRAY to a blank array by not passing any values in the parenthesis of the type of array constructor.
  • Accessing the elements of array: Indexing is used for referencing to each and every individual element inside the array. Indexing starts from 1 and increments by one for each of the sequential element stored in the array. We can simply access a particular element at nth index by using the following syntax –
  • Name of varray (n): Where n is the index at which the element is located. If we have specified the value of n such that it does not belongs to range of the index values (1 to number of elements) then PL/ SQL throws an exception named SUBSCRIPT_BEYOND_COUNT.

Deleting particular element from varray: We can delete a particular value inside the array in PL/ SQL by using the following syntax –

Name of varray. DELETE

Which deletes all the elements present inside the array.

In case if you want to delete only one element from the end of the array, you can use the following syntax –

Name of varray. TRIM;

To remove a particular element at nth position with n index, we can make the use of following syntax –

Name of varray. TRIM(n);

Example of PLSQL Array

Let us understand the usage of arrays in PL/SQL with the help of example.

DECLARE
type employeeNames IS VARRAY(6) OF VARCHAR2(10);
type salary IS VARRAY(6) OF INTEGER;
names employeeNames;
salaries salary;
total integer;
BEGIN
names := employeeNames('Mayur', 'Ekta', 'Pari', 'Yashika', 'Payal');
salaries:= salary(9845, 9007, 7813, 8754, 9022);
total := names.count;
dbms_output.put_line('Total '|| total || ' Employees');
FOR i in 1 .. total LOOP
dbms_output.put_line('Employee: ' || names(i) || '
Salaries: ' || salaries(i));
END LOOP;
END;

The output of the above code is as shown in the below image displaying the information of all the employees and their individual salaries stored in our arrays in PL/ SQL  –

1

Conclusion

We can make the use of VARRAY datatype in PL/ SQL to implement the arrays in it. The arrays store the data in a sequential format in the memory. The values inside a particular array should be of same datatype. The indexing in PL/ SQL for arrays starts from 1. We need to priorly specify the maximum number of elements that will be stored in that particular element. To delete the elements inside the array we can make use of TRIM() or DELETE functions. Arrays are used for storing many elements with same data type in a single variable.

Recommended Articles

This is a guide to PLSQL Array. Here we also discuss the Syntax and Working of PLSQL Array along with example. You may also have a look at the following articles to learn more –

  1. PLSQL Interview Questions
  2. SQLite Show Tables
  3. SQLite connection string
  4. SQLite concat
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more