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
Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally