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
Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

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

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

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value