Nested Tables : Nested Tables « Collections

Nested Tables : Nested Tables « Collections « Oracle PL/SQL Tutorial

1.	Introduction
2.	Query Select
3.	Set
4.	Insert Update Delete
5.	Sequences
6.	Table
7.	Table Joins
8.	View
9.	Index
10.	SQL Data Types
11.	Character String Functions
12.	Aggregate Functions
13.	Date Timestamp Functions
14.	Numerical Math Functions
15.	Conversion Functions
16.	Analytical Functions
17.	Miscellaneous Functions
18.	Regular Expressions Functions
19.	Statistical Functions
20.	Linear Regression Functions
21.	PL SQL Data Types
22.	PL SQL Statements
23.	PL SQL Operators
24.	PL SQL Programming
25.	Cursor
26.	Collections
27.	Function Procedure Packages
28.	Trigger
29.	SQL PLUS Session Environment
30.	System Tables Data Dictionary
31.	System Packages
32.	Object Oriented
33.	XML
34.	Large Objects
35.	Transaction
36.	User Privilege

Java

Java Tutorial

Java Source Code / Java Documentation

Illustrator Tutorials

GIMP Tutorials

C# / C Sharp

C# / CSharp Tutorial

C# / CSharp Open Source

SQL Server / T-SQL Tutorial

Flash / Flex / ActionScript

VBA / Excel / Access / Word

XML

XML Tutorial

Microsoft Office PowerPoint 2007 Tutorial

Microsoft Office Excel 2007 Tutorial

Microsoft Office Word 2007 Tutorial

Oracle PL/SQL Tutorial » Collections » Nested Tables

26. 8. 1. Nested Tables

A nested table is an unordered set of any number of elements, all of the same data type.
A nested table has a single column.
The type of that column may be a built-in database type or an object type.
If the column in a nested table is an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type.
You can insert, update, and delete individual elements in a nested table.


declare

  type <NestedTable> is table of <ElementType>;

...



create or replace type <NestedTable> is table of <ElementType>;


SQL>

SQL> CREATE OR REPLACE PACKAGE aa_types

  2  IS

  3     TYPE boolean_aat IS TABLE OF BOOLEAN

  4      INDEX BY BINARY_INTEGER;

  5

  6     TYPE date_aat IS TABLE OF DATE

  7      INDEX BY BINARY_INTEGER;

  8

  9     TYPE integer_aat IS TABLE OF INTEGER

 10      INDEX BY BINARY_INTEGER;

 11

 12     TYPE pls_integer_aat IS TABLE OF PLS_INTEGER

 13      INDEX BY BINARY_INTEGER;

 14

 15     TYPE binary_integer_aat IS TABLE OF BINARY_INTEGER

 16      INDEX BY BINARY_INTEGER;

 17

 18     TYPE natural_aat IS TABLE OF NATURAL

 19      INDEX BY BINARY_INTEGER;

 20

 21     TYPE positive_aat IS TABLE OF POSITIVE

 22      INDEX BY BINARY_INTEGER;

 23

 24     TYPE number_aat IS TABLE OF NUMBER

 25      INDEX BY BINARY_INTEGER;

 26

 27     TYPE maxdbvarchar2_aat IS TABLE OF VARCHAR2(4000)

 28      INDEX BY BINARY_INTEGER;

 29

 30     TYPE maxvarchar2_aat IS TABLE OF VARCHAR2(32767)

 31      INDEX BY BINARY_INTEGER;

 32

 33     TYPE identifier_aat IS TABLE OF VARCHAR2(30)

 34      INDEX BY BINARY_INTEGER;

 35

 36     TYPE utl_file_aat IS TABLE OF UTL_FILE.FILE_TYPE

 37      INDEX BY BINARY_INTEGER;

 38

 39     TYPE xml_aat IS TABLE OF XML_TYPE

 40      INDEX BY BINARY_INTEGER;

 41

 42     TYPE clob_aat IS TABLE OF CLOB

 43      INDEX BY BINARY_INTEGER;

 44

 45     TYPE blob_aat IS TABLE OF BLOB

 46      INDEX BY BINARY_INTEGER;

 47  END aa_types;

 48  /



Warning: Package created with compilation errors.



SQL>

SQL> show errors

Errors for PACKAGE AA_TYPES:



LINE/COL ERROR

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

36/4     PL/SQL: Declaration ignored

36/34    PLS-00201: identifier 'UTL_FILE' must be declared

39/4     PL/SQL: Declaration ignored

39/29    PLS-00201: identifier 'XML_TYPE' must be declared

SQL>

26. 8. Nested Tables
	26. 8. 1.	Nested Tables
	26. 8. 2.	Deleting internal elements from a collection
	26. 8. 3.	Creating a Nested Table Type
	26. 8. 4.	ANSI Support for Nested Tables
	26. 8. 5.	Nested type column
	26. 8. 6.	Insert value into table with nested type colunm
	26. 8. 7.	cardinality
	26. 8. 8.	Table with subquery
	26. 8. 9.	Type alias for user-defined type in select statement

www.java2java.com | Contact Us

All other trademarks are property of their respective owners.