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
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

Cause of Indeterminacy in SQL One root cause of indeterminacy in SQL lies in its implementation of comparison for equality. For certain system-defined types it is possible for

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

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

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Operator Precedence The operations within an expression are completed in a particular order depending on their precedence (priority). The table shows the default order of the op