Referencing records, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Referencing records

Example of cast operator - sql, Example of Cast Operator So long as C...

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

Initializing records, Initializing Records The illustration below show...

Initializing Records The illustration below shows that you can initialize a record in its type definition. Whenever you declare a record of the type TimeRec, its 3 fields supp

Using raise_application_error - user-defined exceptions, Using raise_applic...

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Keyword & parameter description - exceptions, Keyword & Parameter Descripti...

Keyword & Parameter Description: WHEN: This keyword introduces the exception handler. You can have many exceptions execute the similar sequence of the statements by follo

Remote operations in pl sql, Remote Operations: As the illustration sh...

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

Advantages of subprograms, Advantages of Subprograms The Subprograms g...

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

%notfound - explicit cursor attributes, %NOTFOUND The %NOTFOUND is log...

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Scoping-naming conventions, Scoping Within the similar scope, all the de...

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.

Operators on tables and rows, Operators on Tables and Rows Row Extrac...

Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Dynamic ranges- iterative control, Dynamic Ranges The PL/SQL lets you det...

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd