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

Content

• 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:

DECLARE
   TYPE Calendar IS VARRAY(366) OF DATE;

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

DECLARE

  TYPE nested_type IS TABLE OF VARCHAR2(30);

  TYPE varray_type IS VARRAY(5) OF INTEGER;

  TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_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;

BEGIN

  -- 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

END;

/

1 2 3 4 5 6 Next Page