Inserting objects in pl sql, PL-SQL Programming

Inserting Objects:

You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

BEGIN

INSERT INTO persons

VALUES ('Jenifer', 'Lapidus', ...);

Or else, you can use the constructor for the object type Person to insert an object into the object table persons:

BEGIN

INSERT INTO persons

VALUES (Person('Albert', 'Brooker', ...));

In the later illustration, you use the RETURNING clause to store the Person refs in local variables. Note that how the clause mimics a SELECT statement. The RETURNING clause can also use in the UPDATE and DELETE statements.

DECLARE

p1_ref REF Person;

p2_ref REF Person;

...

BEGIN

INSERT INTO persons p

VALUES (Person('Paul', 'Chang', ...))

RETURNING REF(p) INTO p1_ref;

INSERT INTO persons p

VALUES (Person('Ana', 'Thorne', ...))

RETURNING REF(p) INTO p2_ref;

To insert the objects into an object table, you can use a sub query which returns objects of the similar type. The illustration is as shown below:

BEGIN

INSERT INTO persons2

SELECT VALUE(p) FROM persons p

WHERE p.last_name LIKE '%Jones';

The rows copied to another object table persons2 are given the new object identifiers. The object identifiers are not copied from the object table persons. The scripts below create a relational table named department that has a column of the type Person, and then inserts a row into the table. Note that how constructor Person () gives a value for the column manager.

CREATE TABLE department (

dept_name VARCHAR2(20),

manager Person,

location VARCHAR2(20))

/

INSERT INTO department

VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')

/

Posted Date: 10/6/2012 8:23:59 AM | Location : United States







Related Discussions:- Inserting objects in pl sql, Assignment Help, Ask Question on Inserting objects in pl sql, Get Answer, Expert's Help, Inserting objects in pl sql Discussions

Write discussion on Inserting objects in pl sql
Your posts are moderated
Related Questions
BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

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

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t