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
What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

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.

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

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

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM