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
Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

%NOTFOUND The %NOTFOUND is the logical opposite of the %FOUND. The %NOTFOUND yields TRUE when an INSERT, UPDATE, or DELETE statement affected no rows, or the SELECT INTO state

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

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

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.