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
LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

Project Description: I want somebody who can help me with an idea that I have been working on for a few months now. The person will require extensive knowledge of warcraft 3 PvP

Dynamic SQL: The Most PL/SQL programs do a predictable, specific job. For illustration, a stored procedure may accept an employee number and salary increase, and then update t

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Data Abstraction The Data abstraction extracts the important properties of data while ignoring the not necessary details. Once you design a data structure, you can fail to reme

Use Serially Reusable Packages To help you to manage the use of memory, the PL/SQL gives the pragma SERIALLY_ REUSABLE that mark some packages as serially reusable . So mark

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

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th