Referencing records, PL-SQL Programming

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;

Posted Date: 10/4/2012 3:38:07 AM | Location : United States







Related Discussions:- Referencing records, Assignment Help, Ask Question on Referencing records, Get Answer, Expert's Help, Referencing records Discussions

Write discussion on Referencing records
Your posts are moderated
Related Questions
PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In

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

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

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

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

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

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

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