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
At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean's wants to create a trigger that automatically updates the stock level of all pr

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

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

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

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.

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope