• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

We have already covered the simple data types in our earlier sections. In this section, you are going to learn how to create and manage complex data types in PL/SQL.

Complex data types are those that include record type and collections which are used to handle data in record format or in an array format.




In this tutorial, you will learn-

Record Type

Record type is the complex data type which allows the programmer to create a new data type with the desirable column structure.

Following are some of the attributes of the record type.

  • It groups one or more column together to form a new data type
  • These columns will have its own name and data type
  • Record type can accept the data
    • As a single record that consists of many column OR
    • It can accept the value for one particular column of a record
  • Record type simply means a new data type. Once the record type is created it will be stored as a new data type in the database and the same shall be used to declare a variable in programs.
  • It will use the keyword 'TYPE' to instruct the compiler that it is creating the new data type.
  • It can be created at "database level" which can be stored as database objects, used all-over the database or it can be created at the "subprogram levels", which is visible only inside the subprograms.
  • The database level record type can also be declared for the table columns so that single column can hold the complex data.
  • The data in these data type can be accessed by referring to their variable_name followed by period operator (.) followed by column_name i.e. '<record_type_variable_name>.<column_name>'

Syntax for declaration at database level:

Syntax Explanation:

Complex Data Types in PL/SQL

  • In the first syntax, we can see the keyword 'CREATE TYPE' this instructs the compiler to create the record type named "type_name_db" with the specified column as a database object.
  • This is given as an individual statement and not inside any block.

Complex Data Types in PL/SQL

Syntax Explanation:

  • In the syntax, we are creating the record type named "type_name" only inside the subprogram.

In both declaration method, the way of defining the column and data type is similar.

Example 1: RECORD Type as Database Object

In this program, we are going to see how to create "Record type" as a database object. We are going to create record type 'emp_det' with 4 columns. The columns and their data type are as follows:

  • EMP_NO (NUMBER)
  • EMP_NAME (VARCHAR2 (150))
  • MANAGER (NUMBER)
  • SALARY (NUMBER)

Complex Data Types in PL/SQL

Code Explanation:

  • The above code will create type emp_det as a database object.
  • It will have 4 column emp_no, emp_name, manager and salary as defined.
  • Now 'emp_det' is a similar to other data type (like NUMBER, VARCHAR@, etc.) And it is visible in the entire database, hence this can be used in the entire database to declare the variable of this type.

Output:

Created the type 'emp_det' as record type at the database level.

Example 2: Record Type at Subprogram level- Column level access

In this example, we are going to see how to create a record type at subprogram level and how to populate and fetch the values from it by column level.

We are going to create 'emp_det' record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Complex Data Types in PL/SQL

Complex Data Types in PL/SQL

Code Explanation:

  • Code line 2-8: Record type 'emp_det' is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Code line 9: guru99_emp_rec variable is declared as 'emp_det' data type. Now this variable can hold the value that contains all the above 4 fields/columns.
  • Code line 11: Populating the 'emp_no' field of 'guru99_emp_rec' with value 1001.
  • Code line 12: Populating the 'emp_name' field of 'guru99_emp_rec' with value XXX.
  • Code line 13: Populating the 'manager' field of 'guru99_emp_rec' with value 1000.
  • Code line 14: Populating the 'salary' field of 'guru99_emp_rec' with value 10000.
  • Code line 15-19: Displaying the value of the 'guru99_emp_rec' in output.

Example 3: Record Type at Subprogram level-Row level access

In this example, we are going to see how to create a record type at subprogram level and how to populate it as a row level. We are going to create 'emp_det' record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Complex Data Types in PL/SQL

Complex Data Types in PL/SQL

Code Explanation:

  • Code line 2-8: Record type 'emp_det' is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Code line 9: guru99_emp_rec variable is declared as 'emp_det' data type. Now this variable can hold the value that contains all the above 4 fields/columns.
  • Code line 11: Populating the table emp with data 1002 as emp_no, YYY as emp_name, 15000 as salary and 1000 as manager number.
  • Code line 12: Committing the above insert transaction.
  • Code line 13: Populating the 'guru99_emp_rec' variable as a row level data from the select query for employee number 1002.
  • Code line 15-19: Displaying the value of the 'guru99_emp_rec' in output.

Output: As you can see in the above screenshot when the above code is executed you will get the following output

  • Employee Detail
  • Employee Number: 1002
  • Employee Name: YYY
  • Employee Salary: 1000
  • Employee Manager Number: 15000

Note: The record type can be accessed only in column level while redirecting its value to any output mode.

Collection

The collection is nothing but an ordered group of elements of particular data types. It can be a collection of simple data type or complex data type (like user defined or record types).

In the collection, each element is identified by a term called "subscript." Each item in the collection is assigned with a unique subscript. The data in that collection can be manipulated or fetched by referring to that unique subscript.

Collections are most useful things when a large data of the same type needs to be processed or manipulated. Collections can be populated and manipulated as whole using 'BULK' option in Oracle.

Collections are classified based on the structure, subscript and storage as shown below.

  • Index-by-tables (also known as Associative Array)
  • Nested tables
  • Varrays

At any point, data in the collection can be referred by three terms Collection name, Subscript, Field/Column name as "<collection_name>(<subscript>).<column_name>". You are going to learn about these above mentioned collection categories further in the below section.

Varrays

Varray is a collection method in which the size of the array is fixed. The array size cannot be exceeded than its fixed value. The subscript of the Varray is of a numeric value. Following are the attributes of Varrays.

  • Upper limit size is fixed
  • Populated sequentially starting with the subscript '1'
  • This collection type is always dense, i.e. we cannot delete any array elements. Varray can be deleted as a whole, or it can be trimmed from the end.
  • Since it always be dense in nature, it has very less flexibility.
  • It is more appropriate to use when the array size is known and to perform a similar activities on all the array elements.
  • The subscript and sequence always remain stable, i.e. the subscript and count of the collection is always same.
  • They needs to be initialized before using them in programs. Any operation (except EXISTS operation) on uninitialized collection will throw an error.
  • It can be created as a database object, which is visible throughout the database or inside the subprogram, which can be used only in that subprogram.

The below figure will explain the memory allocation of Varray (dense) diagrammatically.

Subscript 1 2 3 4 5 6 7
Value Xyz Dfv Sde Cxs Vbc Nhu Qwe

Complex Data Types in PL/SQL

Syntax Explanation:

  • In the above syntax, type_name is declared as VARRAY of the type 'DATA_TYPE' for the given size limit. The data type can be either simple or complex type.

Nested Tables

Nested table is a collection in which the size of the array is not fixed. It has the numeric subscript type. Below are more descriptions about nested table type.

  • The Nested table has no upper size limit.
  • Since the upper size limit is not fixed, the collection memory needs to be extended each time before we actually use it. We can extend the collection using 'EXTEND' keyword.
  • Populated sequentially starting with the subscript '1'.
  • This collection type can be of both dense and sparse, i.e. we can create the collection as a dense, and we can also delete the individual array element randomly, which make it as sparse.
  • It gives more flexibility in terms of deleting the array element.
  • It is stored in the system generated database table and can be used in the select query to fetch the values.
  • The subscript and sequence are not stable, i.e. the subscript and the count of the array element can vary.
  • They need to be initialized before using them in programs. Any operation (except EXISTS operation) on the uninitialized collection will throw an error.
  • It can be created as a database object, which is visible throughout the database or inside the subprogram, which can be used only in that subprogram.

The below figure will explain the memory allocation of Nested Table (dense and sparse) diagrammatically. The black colored element space denotes the empty element in a collection i.e. sparse

Subscript 1 2 3 4 5 6 7
Value (dense) Xyz Dfv Sde Cxs Vbc Nhu Qwe
Value(sparse) Qwe Asd Afg Asd Wer

Complex Data Types in PL/SQL

Syntax Explanation:

  • In the above syntax, type_name is declared as Nested table collection of the type 'DATA_TYPE'. The data type can be either simple or complex type.

Index-by-table

Index-by-table is a collection in which the array size is not fixed. Unlike the other collection types, in the index-by-table collection the subscript can be defined by the user. Following are the attributes of index-by-table.

  • The subscript can of integer or strings. At the time of creating the collection, the subscript type should be mentioned.
  • These collections are not stored sequentially.
  • They are always sparse in nature.
  • The array size is not fixed.
  • They cannot be stored in the database column. They shall be created and used in any program in that particular session.
  • They give more flexibility in terms of maintaining subscript.
  • The subscripts can be of negative subscript sequence also.
  • They are more appropriate to use for relatively smaller collective values in which the collection can be initialized and used within the same subprograms.
  • They need not to be initialized before start using them.
  • It cannot be created as a database object. It can only be created inside the subprogram, which can be used only in that subprogram.
  • BULK COLLECT cannot be used in this collection type as the subscript should be given explicitly for each record in the collection.

The below figure will explain the memory allocation of Nested Table (sparse) diagrammatically. The black colored element space denotes the empty element in a collection i.e. sparse

Subscript (varchar) FIRST SECOND THIRD FOURTH FIFTH SIXTH SEVENTH
Value(sparse) Qwe Asd Afg Asd Wer

Complex Data Types in PL/SQL

Syntax Explanation:

  • In the above syntax, type_name is declared as an index-by-table collection of the type 'DATA_TYPE'. The data type can be either simple or complex type. The subsciprt/index variable is given as VARCHAR2 type with maximum size as 10.

Constructor and Initialization Concept in Collections

Constructors are the in-built function provided by the oracle that has the same name as of the object or collections. They are executed first whenever object or collections are getting referred for the first time in a session. Below are the important details of constructor in collection context:

  • For collections, these constructors should be called explicitly to initialize it.
  • Both Varray and Nested tables need to be initialized through these constructors before getting referred into the program.
  • Constructor implicitly extends the memory allocation for a collection (except Varray), hence constructor can also assign the variables to the collections.
  • Assigning values to the collection through constructors will never make the collection sparse.

Collection Methods

Oracle provide many functions to manipulate and to work with the collections. These functions are very much useful in the program to determine and to modify the different attribute of the collections. Following table will give the different functions and their description.

Method Description SYNTAX
EXISTS (n) This method will return Boolean results. It will return 'TRUE' if the nth element exists in that collection, else it will return FALSE. Only EXISTS functions can be used in uninitialized collection <collection_name>.EXISTS(element_position)
COUNT Gives the total count of the elements present in a collection <collection_name>.COUNT
LIMIT It returns the maximum size of the collection. For Varray, it will return the fixed size that has been defined. For Nested table and Index-by-table, it gives NULL <collection_name>.LIMIT
FIRST Returns the value of the first index variable(subscript) of the collections <collection_name>.FIRST
LAST Returns the value of the last index variable(subscript) of the collections <collection_name>.LAST
PRIOR (n) Returns precedes index variable in a collection of the nth element. If there is no precedes index value NULL is returned <collection_name>.PRIOR(n)
NEXT (n) Returns succeeds index variable in a collection of the nth element. If there is no succeeds index value NULL is returned <collection_name>.NEXT(n)
EXTEND Extends one element in a collection at the end <collection_name>.EXTEND
EXTEND (n) Extends n elements at the end of a collection <collection_name>.EXTEND(n)
EXTEND (n,i) Extends n copies of the ith element at the end of the collection <collection_name>.EXTEND(n,i)
TRIM Removes one element from the end of the collection <collection_name>.TRIM
TRIM (n) Removes n elements from the end of collection <collection_name>.TRIM (n)
DELETE Deletes all the elements from the collection. Makes the collection empty <collection_name>.DELETE
DELETE (n) Deletes the nth element from the collection. If the nth element is NULL, then this will do nothing <collection_name>.DELETE(n)
DELETE (m,n) Deletes the element in the range mth to nth in the collection <collection_name>.DELETE(m,n)

Example1: Record Type at Subprogram level

In this example, we are going to see how to populate the collection using 'BULK COLLECT' and how to refer the collection data.

Complex Data Types in PL/SQL

Complex Data Types in PL/SQL

Code Explanation:

  • Code line 2-8: Record type 'emp_det' is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.
  • Code line 9: Creating the collection 'emp_det_tbl' of record type element 'emp_det'
  • Code line 10: Declaring the variable 'guru99_emp_rec' as 'emp_det_tbl' type and initialized with null constructor.
  • Code line 12-15: Inserting the sample data into the 'emp' table.
  • Code line 16: Committing the insert transaction.
  • Code line 17: Fetching the records from 'emp' table and populating the collection variable as a bulk using command "BULK COLLECT". Now the variable 'guru99_emp_rec' contains all the record that are present in the table 'emp'.
  • Code line 19-26: Setting the 'FOR' loop using to print all the records in the collection one-by-one. The collection method FIRST and LAST is used as lower and higher limit of the loop.

Output: As you can see in the above screenshot when the above code is executed you will get the following output

Employee Detail

Employee Number: 1000

Employee Name: AAA

Employee Salary: 25000

Employee Manager Number: 1000

----------------------------------------------

Employee Number: 1001

Employee Name: XXX

Employee Salary: 10000

Employee Manager Number: 1000

----------------------------------------------

Employee Number: 1002

Employee Name: YYY

Employee Salary: 15000

Employee Manager Number: 1000

----------------------------------------------

Employee Number: 1003

Employee Name: ZZZ

Employee Salary: 7500

Employee Manager Number: 1000

----------------------------------------------

Summary

In this chapter, you have learnt about the complex data types in PL/SQL, Records, and Collections. You have also learnt how to create, initialize and use them in the program. Standard built-in collection methods provided by oracle are also covered in this chapter.

 

YOU MIGHT LIKE:
PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...