Defining records, PL-SQL Programming

Assignment Help:

Defining and Declaring Records

To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative part of any PL/SQL subprogram, block,  or package using the syntax as shown:

TYPE type_name IS RECORD (field_declaration[,field_declaration]...);

where field_declaration stands for

field_name field_type [[NOT NULL] {:= | DEFAULT} expression]

And where the type_name is a type specifier used later to declare the records, field_ type is any PL/SQL datatype except REF CURSOR, and expression yields a value of the similar type as the field_type.

Note:  Unlike the TABLE and VARRAY types, the RECORD types cannot be CREATED and stored in the database.

You can use %TYPE & %ROWTYPE to state the field types. In the illustration shown below, you define a RECORD type named DeptRec:

DECLARE

TYPE DeptRec IS RECORD (

dept_id dept.deptno%TYPE,

dept_name VARCHAR2(15),

dept_loc VARCHAR2(15));

Notice that the field declarations are just like the variable declarations. Each field has an exclusive name and specific datatype. Therefore, the value of a record is actually a collection of values, each of some of the simpler type.

As the illustration below shows, the PL/SQL defines records that contain collections, objects, and other records (known as the nested records). Though, the object types cannot have attributes of type RECORD.

DECLARE

TYPE TimeRec IS RECORD (

seconds SMALLINT,

minutes SMALLINT,

hours SMALLINT);

TYPE FlightRec IS RECORD (

flight_no INTEGER,

plane_id VARCHAR2(10),

captain Employee, -- declare object

passengers PassengerList, -- declare varray

depart_time TimeRec, -- declare nested record

airport_code VARCHAR2(10));

The illustration later shows that you can specify a RECORD type in the RETURN clause of a function specification. That permits the function to return a user-defined record of the same type.

DECLARE

TYPE EmpRec IS RECORD (

emp_id INTEGER

last_name VARCHAR2(15),

dept_num INTEGER(2),

job_title VARCHAR2(15),

salary REAL(7,2));

...

FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRec IS...


Related Discussions:- Defining records

Raise_application_error, Raise_application_error -  procedure of package D...

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Package body in pl/sql, Package Body: The package specification is imp...

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

Named notation, Named Notation The second procedure call uses the name...

Named Notation The second procedure call uses the named notation. An arrow (=>) serve as the relationship operator that associates the formal parameter to the left of the arro

Sql queries-oracle , 1- You can check attribute names from each table in D...

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Data types in sql - character, Data Types in SQL - Character CHARACTER...

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Parameter & keyword description - functions, Parameter & Keyword Descriptio...

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

Fetching from a cursor variable, Fetching from a Cursor Variable The F...

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

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

Collection methods in pl sql, Collection Methods:   The collection me...

Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Calling constructors in pl sql, Calling Constructors: The Calls to a c...

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

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