What are Nested Tables? How will u delete 5 rows from Nested
Tables
Answers were Sorted based on User's Feedback
Answer / keshav
CREATE Or Replace TYPE AddressType AS OBJECT (
street VARCHAR2(15),
city VARCHAR2(15),
state CHAR(2),
zip VARCHAR2(5)
);
CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
addresses nested_table_AddressType
)
NESTED TABLE
addresses
STORE AS
nested_addresses;
INSERT INTO employee VALUES (
1, 'Steve', 'Brown',
nested_table_AddressType(
AddressType('2 Ave', 'City', 'MA', '12345'),
AddressType('4 Ave', 'City', 'CA', '54321')
)
);
DELETE FROM TABLE (
SELECT addresses FROM employee WHERE id = 1
) addr
WHERE
VALUE(addr) = AddressType(
'4 Ave', 'City', 'CA', '54321'
);
Is This Answer Correct ? | 16 Yes | 0 No |
Answer / subrat ray
Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
You can define equivalent SQL types,allowing nested tables to be stored in database tables and manipulated through SQL.
->it does not have fixed upper bound.
->the data storage is out of line.
DECLARE
TYPE COURSELIST IS TABLE OF VARCHAR2(10);
COURSES COURSELIST;
BEGIN
COURSES:=COURSELIST('A','B','C','D','E','F','G','H','I');
DBMS_OUTPUT.PUT_LINE('THE TOTAL NO OF ELEMENTS ARE:'||COURSES.COUNT);
COURSES.DELETE(1,5);
DBMS_OUTPUT.PUT_LINE('THE NO OF ELEMENTS ARE PRESENT IS:'||COURSES.COUNT);
END;
SQL> /
THE TOTAL NO OF ELEMENTS ARE:9
THE NO OF ELEMENTS ARE PRESENT IS:4
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / rahul k.
Excellent Keshav...Very nice...
Table within a table called nested table same as nested
loop in any procedural languages...
Thnanks
Is This Answer Correct ? | 1 Yes | 1 No |
What is the usage of sql functions?
Can we use more than one null value for unique key?
31 Answers A1 Technology, Wipro,
What is sql server and ase?
Can you join views in sql?
how are rank and dense rank being alloted for column with same values over a particular column
What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
Is sql a programming?
What is a schema sql?
what is data integrity
How do you explain an index number?
How to prepare for oracle pl sql certification?
select 1,col1,col2 from table1. output?