Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4005896
  • 博文数量: 536
  • 博客积分: 10470
  • 博客等级: 上将
  • 技术积分: 4825
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-26 14:08
文章分类

全部博文(536)

文章存档

2024年(3)

2021年(1)

2019年(1)

2017年(1)

2016年(2)

2013年(2)

2012年(10)

2011年(43)

2010年(10)

2009年(17)

2008年(121)

2007年(252)

2006年(73)

分类: 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: http://blog.chinaunix.net/u/19782/showart.php?id=254737
BLOB, CLOB insert initialize: http://blog.chinaunix.net/u/19782/showart.php?id=254048

阅读(1796) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

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;