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

3cx pbx sync windows service, 3CX PBX sync Windows Service Project Descr...

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Packages, Packages The package is a schema object which groups logicall...

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

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

Package utl file in pl/sql, UTL_FILE: The Package UTL_FILE permits you...

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Pragma restrict_references in pl sql, Using Pragma RESTRICT_REFERENCES: ...

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

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

Sql pseudocolumns, SQL Pseudocolumns The PL/SQL recognizes the followin...

SQL Pseudocolumns The PL/SQL recognizes the following SQL pseudocolumns, that returns the specific data items: LEVEL, NEXTVAL, CURRVAL, ROWID, & ROWNUM. The Pseudocolumns are n

Update stock levels, At times, customers make mistakes in submitting their ...

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean's wants to create a trigger that automatically updates the stock level of all pr

What is a record, What Is a Record  ? A record is a group of related...

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

Relational schema, query to Find the account numbers of all customers whose...

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

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