Oracle 11g Release 1 (11.1) PL/SQL _ understand the Collection type

Source: Internet
Author: User


  • Define Collection type
  • Declare Collection variables
  • Initialize and reference Collection
  • Reference Collection Element
  • Assign a value to Collection
  • Multi-dimensional Collection
  • Compare Collection
  • Collection Method
  • Collection exception

The significance of understanding Oracle collections is that when we develop applications using programming languages, we use a lot of built-in Collection types, such as arrays and linked lists, however, when the business logic becomes complex, if you want to insert data to multiple tables at the same time and insert multiple data records to one table, you may need transaction control. In this case, it seems appropriate to use anonymous subprograms. Therefore, it is inevitable to use the set type in anonymous subprograms.

In addition, if a function written in a programming language involves set operations and wants to rewrite it to an Oracle function for some reason, it is necessary to understand Oracle set operations.

Define Collection type

First defineCollectionType, and then declare the variable of this type.

You can defineCollectionType.

Created at the Mode LevelCollectionType isStandalone stored type. UseCREATE TYPEStatement creation. It is stored in the database until it is usedDROP TYPEStatement to delete this type.

Created in the packageCollectionType isPackaged type. It is stored in the database until it is usedDROP PACKAGEStatement to delete a package.

Created in the PL/SQL BlockCollectionThe type is only available in the block. Only the block is embedded in standalone or packaged subprogram and stored in the database.

CollectionTypes follow the same scopes and instantiation rules as other types and variables. When you enter a block or subroutine,CollectionIt is instantiated and destroyed when exiting. In a package,CollectionIt is instantiated and destroyed when the database session ends.


You can useTYPETo defineTABLEAndVARRAYType.

ForNested tableAndVarray, The table or varray element type can be,REF CURSORAny other PL/SQL data type.

When definingVARRAYType, you must use a positive integer to specify the maximum size. Define a date that can store a maximum of 366 dates as follows:VARRAY:


Associative arraysYou can use any key value to insert an element. The key does not need to be continuous. The data type of the key can bePLS_INTEGER,VARCHAR2, OrVARCHAR2Subtype:VARCHAR,STRING, OrLONG.

Must be specified based onVARCHAR2Key Length,LONGBecause it is equivalent to declaringVARCHAR2 (32760)Type key. TypeRAW,LONG RAW,ROWID,CHARAndCHARACTERNot allowed asAssociative array.LONGAndLONG RAWOnly for backward compatibility.

Initialization clauses are not allowed.Associative arraysNo construction tag. When usingVARCHAR2Key to referenceAssociative arraysYou can useDATEOrTIMESTAMPAnd other types, as long as this type can be usedTO_CHARConvert functionsVARCHAR2.

Declare Collection variables

DefineCollectionDeclare the variable with this type. Use the new type name in the declaration, which is the same as the predefined type, as shown in figureNUMBER.

Example 1: demonstrate the declaration of nested table, varray, and associative array

  TYPE nested_type IS TABLE OF VARCHAR2(30);
  TYPE varray_type IS VARRAY(5) OF INTEGER;
  TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
  TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
  v1 nested_type;
  v2 varray_type;
  v3 assoc_array_num_type;
  v4 assoc_array_str_type;
  v5 assoc_array_str_type2;
  -- an arbitrary number of strings can be inserted v1
  v1 := nested_type('Shipping', 'Sales', 'Finance', 'Payroll');
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
  v3(99) := 10; -- Just start assigning to elements
  v3(7) := 100; -- Subscripts can be any integer values
  v4(42) := 'Smith'; -- Just start assigning to elements
  v4(54) := 'Jones'; -- Subscripts can be any integer values
  v5('Canada') := 'North America';
  -- Just start assigning to elements
  v5('Greece') := 'Europe';
  -- Subscripts can be string values
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.