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
Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE