-
Could I create on nested tables column ?
If yes then please write the syntex.
Thanx
-
Hi,
I send to you this document.
Oracle 8 or higer support Nested Tables.
Regerds.
Examples for Nested Tables
--------------------------
Example 1:
----------
The following example illustrates how a simple nested table is created.
a) First, define a Object type as follows:
SQL> CREATE TYPE ELEMENTS AS OBJECT (
2> ELEM_ID NUMBER(6),
3> PRICE NUMBER(7,2));
4> /
b) Next, create a table type ELEMENTS_TAB which stores ELEMENTS objects:
SQL> CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
2> /
c) Finally, create a database table STORAGE having type ELEMENTS_TAB as
one of its columns:
SQL> CREATE TABLE STORAGE (
2> SALESMAN NUMBER(4),
3) ELEM_ID NUMBER(6),
4) ORDERED DATE,
5) ITEMS ELEMENTS_TAB)
6) NESTED TABLE ITEMS STORE AS ITEMS_TAB;
Example 2:
----------
This example demonstrates how to populate the STORAGE table with a single
row:
SQL> INSERT INTO STORAGE
2> VALUES (100,123456,SYSDATE,
3> ELEMENTS_TAB(ELEMENTS(175692,120.12),
4> ELEMENTS(167295,130.45),
5> ELEMENTS(127569,99.99)));
Example 3:
----------
The following example demonstrates how to use the operator THE which is
used in a SELECT statement to identify a nested table:
SQL> INSERT INTO
2> THE
3> (SELECT ITEMS FROM STORAGE WHERE ELEM_ID = 123456)
4> VALUES (125762, 101.99);
Example 4:
----------
The following example shows how to update the STORAGE table row where
salesman column has value 100:
SQL> UPDATE STORAGE
2> SET ITEMS = ELEMENTS_TAB(ELEMENTS(192512, 199.99))
3> WHERE SALESMAN = 100;
-
Could I create on nested tables column ?
Sorry ,
My question is
Could I create INDEX on nested tables column ?
If yes then please write the syntex.
Thanx
-
why do u need a index on nested columns.
-
Yes,
In Example 1:
CREATE TYPE ELEMENTS AS OBJECT (
ELEM_ID NUMBER(6),
PRICE NUMBER(7,2));
CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
CREATE TABLE STORAGE (
SALESMAN NUMBER(4),
ELEM_ID NUMBER(6),
ORDERED DATE,
ITEMS ELEMENTS_TAB)
NESTED TABLE ITEMS STORE AS ITEMS_TAB;
INSERT INTO STORAGE
VALUES (100,123456,SYSDATE,
ELEMENTS_TAB(ELEMENTS(175692,120.12),
ELEMENTS(167295,130.45),
ELEMENTS(127569,99.99)));
*************
CREATE INDEX AA1 ON STORAGE (ELEM_ID);
****************
You can create index on ELEM_ID column.
-
In Example 1:
CREATE TYPE ELEMENTS AS OBJECT (
ELEM_ID NUMBER(6),
PRICE NUMBER(7,2));
CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
CREATE TABLE STORAGE (
SALESMAN NUMBER(4),
ELEM_ID NUMBER(6),
ORDERED DATE,
ITEMS ELEMENTS_TAB)
NESTED TABLE ITEMS STORE AS ITEMS_TAB;
INSERT INTO STORAGE
VALUES (100,123456,SYSDATE,
ELEMENTS_TAB(ELEMENTS(175692,120.12),
ELEMENTS(167295,130.45),
ELEMENTS(127569,99.99)));
*************
CREATE INDEX AA1 ON STORAGE (ELEM_ID);
****************
You can create index on ELEM_ID column.
Sorry,
You can create index ;
CREATE INDEX AA3 ON ITEMS_TAB (ELEM_ID);
or
CREATE INDEX AA4 ON ITEMS_TAB (PRICE);
or
CREATE INDEX AA5 ON ITEMS_TAB (ELEM_ID,PRICE);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|