Using operator deref - manipulating objects in pl sql, PL-SQL Programming

Assignment Help:

Using Operator DEREF:

You cannot navigate through refs within the PL/SQL procedural statements. Rather than, you should use the operator DEREF in the SQL statement. The DEREF take as its argument that reference to an object, and then returns the value of that object. If the ref is dangling, the DEREF returns a null object.

In the illustration below, you dereference the ref to a Person object. Note that you select the ref from dummy table dual. You do not require specifying an object table and search criteria as each object stored in an object table has an immutable, exclusive object identifier that is a part of every ref to that object.

DECLARE

p1 Person;

p_ref REF Person;

name VARCHAR2(15);

BEGIN

...

/* Suppose that p_ref holds a valid reference

to an object stored in an object table. */

SELECT DEREF(p_ref) INTO p1 FROM dual;

name := p1.last_name;

You can use DEREF in successive SQL statements to dereference refs, as the

following example shows:

CREATE TYPE PersonRef AS OBJECT (p_ref REF Person)

/

DECLARE

name VARCHAR2(15);

pr_ref REF PersonRef;

pr PersonRef;

p Person;

BEGIN

...

/* Assume pr_ref holds a valid reference. */

SELECT DEREF(pr_ref) INTO pr FROM dual;

SELECT DEREF(pr.p_ref) INTO p FROM dual;

name := p.last_name;

...

END

/

The later illustration shows that you cannot use the operator DEREF within procedural statements:

BEGIN

...

p1 := DEREF(p_ref); -- illegal

Within the SQL statements, you can use the dot notation to navigate throughout the object columns to ref attributes and through one ref attribute to the other. You can also navigate through the ref columns to attributes if you use a table alias. For illustration, the syntax below is valid:

table_alias.object_column.ref_attribute

table_alias.object_column.ref_attribute.attribute

table_alias.ref_column.attribute

Suppose that you have to run the SQL*Plus script below that creates object types Address and Person and object table persons:

CREATE TYPE Address AS OBJECT (

street VARCHAR2(35),

city VARCHAR2(15),

state CHAR(2),

zip_code INTEGER)

/

CREATE TYPE Person AS OBJECT (

first_name VARCHAR2(15),

last_name VARCHAR2(15),

birthday DATE,

home_address REF Address, -- shared with other Person objects

phone_number VARCHAR2(15))

/

CREATE TABLE persons OF Person

/

The Ref attribute home_address corresponds to a column in the object table persons that holds refs to the Address objects stored in some another table. After populating the tables, you can select a particular address by de-referencing its ref, as shown:

DECLARE

addr1 Address,

addr2 Address,

...

BEGIN

SELECT DEREF(home_address) INTO addr1 FROM persons p

WHERE p.last_name = 'Derringer';

In the illustration below, you navigate through ref column home_address to attribute the street. In this situation, the table alias is needed.

DECLARE

my_street VARCHAR2(25),

...

BEGIN

SELECT p.home_address.street INTO my_street FROM persons p

WHERE p.last_name = 'Lucas';


Related Discussions:- Using operator deref - manipulating objects in pl sql

Enrolment was split - sql, Enrolment was split - SQL Example shows ho...

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

Information hiding in pl/sql, Information Hiding   With the informatio...

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

Example of null operator - nino rule, Example of Null operator - NiNo Rule ...

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Best practices/Data Warhousing, What are 3 good practices of modeling and/o...

What are 3 good practices of modeling and/or implementing data warehouses?

Insert command in sql, INSERT Command in SQL Loosely speaking, INSERT...

INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

Row operators - sql operators, Row Operators The Row operators return ...

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Currval and nextval - sql pseudocolumns, CURRVAL and NEXTVAL The serie...

CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

Relational operators and logical operators, Relational Operators and Logica...

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Overloading, Overloading The PL/SQL overloads the subprogram names. T...

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Pl sql code to declare cursors with parameter, Write a pl/sql block that de...

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

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