Collections in pl sql, PL-SQL Programming

Assignment Help:

Collections:

 The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subscript which determines its position in the collection. The PL/SQL offers 3 kinds of collections: the nested tables, index-by tables, and varrays . The Nested tables extend the functionality of the index-by tables (formerly known as the "PL/SQL tables").

The Collections works like the arrays found in mainly the third-generation programming languages. Though, collections can have only one dimension and should be indexed by integers. (In several languages like Ada and Pascal, arrays can have the multiple dimensions and can be indexed by inventory types.)

The Nested tables and varrays can store instances of an object type and, on the other hand, can be attributes of an object type. The collections can also be passed as the parameters. Therefore, you can use them to move the columns of data into and out of database tables or between the client-side applications and stored subprograms.

Syntax:

1390_collaction.png

1179_collaction1.png

Keyword and Parameter Description

 

type_name:

These identify a user-defined type specifier that is used in the subsequent declarations of collections.

element_type:

This is any PL/SQL datatype except the BOOLEAN, BINARY_INTEGER, LONG, LONG RAW, NATURAL, NATURALN, NCLOB, NCHAR, NVARCHAR2, object types with TABLE or VARRAY attributes, PLS_INTEGER, POSITIVE, SIGNTYPE, POSITIVEN, REF CURSOR, STRING, TABLE, or VARRAY. Also, with the varrays, the element_type cannot be CLOB, BLOB, or an object type with BLOB or CLOB attributes. If the element_type is a record type, every field in the record should be a scalar type or an object type.

INDEX BY BINARY_INTEGER:

This optional clause defines the Version 2 PL/SQL tables that are called index-by tables in Version 8.

size_limit:

This is a positive integer literal which specifies the maximum size of a varray that is the maximum number of elements that the varray can contain.


Related Discussions:- Collections in pl sql

Sql database, SQL Database: So, an SQL database is one whose symbols a...

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

How bulk bind helps improvement in performance?, How Bulk Binds Improve Per...

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Manipulating local collections - pl/sql, Manipulating Local Collections ...

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Write an anonymous block that contains a pl/sql function, a. Write an anon...

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Select into statement - syntax, SELECT INTO Statement   The SELECT INT...

SELECT INTO Statement   The SELECT INTO statement retrieve data from one or more database tables, and then assigns the selected values to the variables or fields. Syntax:

Exception handling, set serveroutput on declare a number(5); b n...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Oracle development , Write a stored procedure that accepts the post code in...

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Short-circuit evaluation-pl/sql expressions , Short-Circuit Evaluation ...

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Subprograms, What Are Subprograms? The Subprograms are named PL/SQL blo...

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd