文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2007-03-20 10:34:29
eg:
create type people_typ as object (
last_name varchar2(25),
department_id number(4),
salary number(8,2));
/
create type people_tab_typ as table of people_typ;
/
create table hr_info (
department_id number(4),
location_id number(4),
manager_id number(6),
people people_tab_typ)
nested table people store as people_stor_tab;
insert into hr_info values (280, 1800, 999, people_tab_typ());
insert into table(select h.people from hr_info h
where h.department_id = 280)
values ('smith', 280, 1750);
update table(select h.people from hr_info h
where h.department_id = 280) p
set p.salary = p.salary 100;
delete table(select h.people from hr_info h
where h.department_id = 280) p
where p.salary > 1700;
select t1.department_id, t2.* from hr_info t1, table(t1.people) t2
where t2.department_id = t1.department_id;
select t1.department_id, t2.*
from hr_info t1, table(cast(multiset(
select t3.last_name, t3.department_id, t3.salary
from people t3
where t3.department_id = t1.department_id)
as people_tab_typ)) t2;
if you insert like this:
insert into hr_info(department_id, location_id, manager_id)
values (280, 1800, 999);
not include people_tab_typ() then ora-22908 errors.
ora-22908: reference to null table value
this like blob, clob, bfile, before insert record you must initialize it.
you can see my blog about blob, clob, bfile insert initialize.
bfile insert initialize:
blob, clob insert initialize:
create type people_typ as object (
last_name varchar2(25),
department_id number(4),
salary number(8,2));
/
create type people_tab_typ as table of people_typ;
/
create table hr_info (
department_id number(4),
location_id number(4),
manager_id number(6),
people people_tab_typ)
nested table people store as people_stor_tab;
insert into hr_info values (280, 1800, 999, people_tab_typ());
insert into table(select h.people from hr_info h
where h.department_id = 280)
values ('smith', 280, 1750);
update table(select h.people from hr_info h
where h.department_id = 280) p
set p.salary = p.salary 100;
delete table(select h.people from hr_info h
where h.department_id = 280) p
where p.salary > 1700;
select t1.department_id, t2.* from hr_info t1, table(t1.people) t2
where t2.department_id = t1.department_id;
select t1.department_id, t2.*
from hr_info t1, table(cast(multiset(
select t3.last_name, t3.department_id, t3.salary
from people t3
where t3.department_id = t1.department_id)
as people_tab_typ)) t2;
if you insert like this:
insert into hr_info(department_id, location_id, manager_id)
values (280, 1800, 999);
not include people_tab_typ() then ora-22908 errors.
ora-22908: reference to null table value
this like blob, clob, bfile, before insert record you must initialize it.
you can see my blog about blob, clob, bfile insert initialize.
bfile insert initialize:
blob, clob insert initialize:
给主人留下些什么吧！~~
chinaunix网友2011-03-30 22:14:25
select t1.department_id, t2.* from hr_info t1, table(cast(multiset( select t3.last_name, t3.department_id, t3.salary from table(people) t3 where t3.department_id = t1.department_id) as people_tab_typ)) t2;
|