Referencing records, PL-SQL Programming

Assignment Help:

Referencing Records

Unlike the elements in a collection, that are accessed using subscripts, the fields in a record are accessed by name. To reference an individual field, you can use the dot notation and the syntax which is as shown:

record_name.field_name

For illustration, you reference field hire_date in record emp_info as shown:

emp_info.hire_date ...

When calling a function which returns a user-defined record, use the syntax below to reference the fields in the record:

function_name(parameter_list).field_name

For illustration, the call to function nth_highest_sal references to the field salary in record emp_info:

DECLARE

TYPE EmpRec IS RECORD (

emp_id NUMBER(4),

job_title CHAR(14),

salary REAL(7,2));

middle_sal REAL;

FUNCTION nth_highest_sal (n INTEGER) RETURN EmpRec IS

emp_info EmpRec;

BEGIN

...

RETURN emp_info; -- return record

END;

BEGIN

middle_sal := nth_highest_sal(10).salary; -- call function

When calling a parameter less function, following syntax can be use:

function_name().field_name -- note empty parameter list

To reference the nested fields in a record returned by a function, use the extended dot notation. The syntax is as shown:

function_name(parameter_list).field_name.nested_field_name

For illustration, the following call to function item references to the nested field minutes in record item_info:

DECLARE

TYPE TimeRec IS RECORD (minutes SMALLINT, hours SMALLINT);

TYPE AgendaItem IS RECORD (

priority INTEGER,

subject VARCHAR2(100),

duration TimeRec);

FUNCTION item (n INTEGER) RETURN AgendaItem IS

item_info AgendaItem;

BEGIN

...

RETURN item_info; -- return record

END;

BEGIN

...

IF item(3).duration.minutes > 30 THEN ... -- call function

END;


Related Discussions:- Referencing records

Character types in pl/sql, Character Types The Character types allow yo...

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

Sequential control - pl/sql, Sequential Control Dissimilar to the IF and ...

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Advantages of packages, Advantages of Packages The benefits of the Pack...

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Query optimization, 1.( /5 marks) Suppose that a B+-tree index with the sea...

1.( /5 marks) Suppose that a B+-tree index with the search key (dept_name, building) is available on relation department. What would be the best way to handle the following selecti

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

Object type in pl/sql, Object Type: The object type is a user-define...

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

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 $

Creating and destroying base tables, Creating and Destroying Base Tables: ...

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Comparison operators- pl/sql, Comparison Operators The Comparison operat...

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

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