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

Advantages of wrapping, Advantages of Wrapping   The PL/SQL Wrapper co...

Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure

In mode - parameter modes, IN Mode An IN parameter pass the values to ...

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

Create a procedure that update the status, Create a procedure named STATUS_...

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Relational shema.., Find the account numbers of all customers whose balance...

Find the account numbers of all customers whose balance is more than 10,000 $

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Third step at defining type sid in sql, Third Step at defining type SID in ...

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE

Procedures in pl/sql, Procedures   The procedure is a subprogram which...

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Overloading, Overloading The PL/SQL overloads the subprogram names. T...

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Case sensitivity-naming conventions, Case Sensitivity Similar to all the...

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

Need fullcalendar modifications, Project Description: I am looking to ch...

Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra

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