Scribd
Upload
57 views

PL SQL

A composite data type stores values that have internal components. PL / SQL lets you define two kinds of composite data types: collection and record. Internal components can be either scalar or composite.

Uploaded by

Venkat Nkb
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
57 views

PL SQL

A composite data type stores values that have internal components. PL / SQL lets you define two kinds of composite data types: collection and record. Internal components can be either scalar or composite.

Uploaded by

Venkat Nkb
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 4

VARRAY DESCRIPTION

Oracle PL/SQL supports all the usual sorts of datatypes, such as strings, numbers and dates, and then adds a number of its own more specialized or unique datatypes, including records and collections.

A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. PL/SQL lets you define two kinds of composite data types: collection and record. You can use composite components wherever you can use composite variables of the same type. In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE against a column in a relational table that is itself a collection.

Oracle offers a set of methods that both give you information about the contents of a collection and also can add or remove elements from a collection. Methods that return information about or modify the contents of a collection include:
DELETE - Deletes elements from collection. TRIM - Deletes elements from end of varray or nested table. EXTEND - Adds elements to end of varray or nested table. EXISTS - Returns TRUE if and only if specified element of the collection exists. FIRST - Returns first index in collection. LAST - Returns last index in collection. COUNT - Returns number of elements in collection. LIMIT - Returns maximum number of elements that collection can have. PRIOR - Returns index that precedes specified index. NEXT - Returns index that succeeds specified index.

You invoke a collection method using object-oriented syntax as in:


collection_variable.method_name

is a procedure that deletes elements from the end of a varray or nested table. This method has these forms:
TRIM

removes one element from the end of the collection, if the collection has at least one element; otherwise, it raises the predefined exception SUBSCRIPT_BEYOND_COUNT. TRIM(n) removes n elements from the end of the collection, if there are at least n elements at the end; otherwise, it raises the predefined exception SUBSCRIPT_BEYOND_COUNT.
TRIM

operates on the internal size of a collection. That is, if DELETE deletes an element but keeps a placeholder for it, then TRIM considers the element to exist. Therefore, TRIM can delete a deleted element.
TRIM

PL/SQL does not keep placeholders for trimmed elements. Therefore, trimmed elements are not included in the internal size of the collection, and you cannot restore a trimmed element by assigning a valid value to it.

Resources

Oracle Database PL/SQL Packages and Types Reference Oracle Database PL/SQL Language Reference (11.2) Data Types (Oracle Documentation) Collections and Records (Oracle Documentation) Collection Methods (Oracle Documentation) TRIM Collection Method (Oracle Documentation)

Resources

Verification Code
Back to Top of Page

/* PLS-00306: wrong number or types of arguments in call to 'DELETE' */ DECLARE TYPE suits_t IS VARRAY (4) OF VARCHAR2 (20); l_card_suits suits_t := suits_t ('Hearts' , 'Spades' , 'Diamonds' , 'Clubs');

BEGIN l_card_suits.delete (3, 4); DBMS_OUTPUT.put_line (l_card_suits.COUNT); END; / /* ORA-00903: invalid table name */ DECLARE TYPE suits_t IS VARRAY (4) OF VARCHAR2 (20); l_card_suits suits_t := suits_t ('Hearts' , 'Spades' , 'Diamonds' , 'Clubs');

BEGIN DELETE FROM TABLE (l_card_suits) WHERE COLUMN_VALUE IN ('Diamonds', 'Clubs'); DBMS_OUTPUT.put_line (l_card_suits.COUNT); END; / DECLARE

Verification Code
TYPE suits_t IS VARRAY (4) OF VARCHAR2 (20); l_card_suits suits_t := suits_t ('Hearts' , 'Spades' , 'Diamonds' , 'Clubs');

BEGIN l_card_suits.TRIM (2); DBMS_OUTPUT.put_line (l_card_suits.COUNT); END; / DECLARE TYPE suits_t IS VARRAY (4) OF VARCHAR2 (20); l_card_suits suits_t := suits_t ('Hearts' , 'Spades' , 'Diamonds' , 'Clubs');

BEGIN l_card_suits (3) := NULL; l_card_suits (4) := NULL; DBMS_OUTPUT.put_line (l_card_suits.COUNT); END; /

You might also like