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
Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

What Are Cursor Variables  ? The Cursor variables are like C or Pascal pointers that hold the memory location (address) of some item rather of the item itself. Therefore, decl

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s