Using operator ref - manipulating objects pl sql, PL-SQL Programming

Using Operator REF:

You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one or more refs to the Person objects, then insert the refs into the table person_refs:

BEGIN

INSERT INTO person_refs

SELECT REF(p) FROM persons p

WHERE p.last_name LIKE '%Smith';

In the later illustration, you retrieve a ref and attribute at similar time:

DECLARE

p_ref REF Person;

taxpayer_id VARCHAR2(9);

BEGIN

SELECT REF(p), p.ss_number INTO p_ref, taxpayer_id

FROM persons p

WHERE p.last_name = 'Parker'; -- must return one row

...

END;

In the final illustration, you update the attributes of a Person object:

DECLARE

p_ref REF Person;

my_last_name VARCHAR2(15);

...

BEGIN

...

SELECT REF(p) INTO p_ref FROM persons p

WHERE p.last_name = my_last_name;

UPDATE persons p

SET p = Person('Jill', 'Anders', '11-NOV-67', ...)

WHERE REF(p) = p_ref;

END;

Posted Date: 10/6/2012 8:20:37 AM | Location : United States







Related Discussions:- Using operator ref - manipulating objects pl sql, Assignment Help, Ask Question on Using operator ref - manipulating objects pl sql, Get Answer, Expert's Help, Using operator ref - manipulating objects pl sql Discussions

Write discussion on Using operator ref - manipulating objects pl sql
Your posts are moderated
Related Questions
TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

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.

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Usefulness of Data Type In SQL, as in most computer languages, a type can be used for constraining the values that are permitted to be used for some purpose. In particular, i

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o