发布日期:2015-11-06 13:16 来源: 标签: 数据库 oracle教程 oracle函数 oracle嵌套表
本章我们主要学习Oracle中如何创建嵌套表及嵌套表的返回类型?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。

1   基础知识

嵌套表是表中之表。一个嵌套表是某些行的集合,它在主表中表示为其中的一列。对主表中的每一条记录,嵌套表可以包含多个行。在某种意义上,它是在一个表中存储一对多关系的一种方法。

嵌套表不需要执行联合操作,就可以通过相应的嵌套表信息列。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下,也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中,父子两个表的联系需要通过外部关键字(外键)关系才能实现,即NESTED_TABLE_ID外键,默认情况下,每个嵌套表列都产生一个额外的RAW(16)隐藏列,并在其上创建了唯一约束,用以指向嵌套表。而嵌套表中有两隐藏列:SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列;另一个NESTED_TABLE_ID的外键回指向父表。

一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制,确保嵌套表是IOT,以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销.

 嵌套表中的子表不能单独查询,但是如果确实需要,是可以的。采用hint指令。

SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID, SYS_NC_ROWINFO$ FROM  嵌套子表;

 

2   应用

之前的函数只返回一个总分值,现在需要获得多个信息。选择方法有创建多个其他函数各种返回1个值,这些函数的流程几乎一摸一样。需要执行多次,后续若增加其他需求,就得创建更多函数了。执行多个函数后,性能肯定会下降。

而采用嵌套表可以解决这个问题,只执行一次获得很多信息,但是基于嵌套表的访问性能有所下降,因此需要平衡。

2.1    创建嵌套表类型

CREATE OR REPLACE TYPE OBJ_TYPE_XX AS OBJECT (

info1 VARCHAR2(15),

info2 VARCHAR2(15),

info3 NUMBER(10,0),

info4 NUMBER(10,0),

info5 NUMBER(10,0)

--后续增加其他字段

);

 

CREATE OR REPLACE TYPE TBL_TYPE_XX AS TABLE OF OBJ_TYPE_XX;

 

2.2    创建返回嵌套表类型的函数

 

CREATE OR REPLACE FUNCTION  MyFunc(

  v_args2 VARCHAR2,

  v_args1 VARCHAR2)

RETURN tbl_type_userscore3

AS

   Result tbl_type_XX;

   VAR11 NUMBER;

   VAR22 NUMBER;

BEGIN

  Result:=tbl_type_XX(obj_type_XX(v_args2 ,v_args1 VAR11 , VAR11 ,VAR22));

  return(Result);

END;

 

2.3    表的创建

2.3.1   版本1

CREATE TABLE T_XXX(

    COL1 VARCHAR2(15) NOT NULL,

    COL2 VARCHAR2(15) NOT NULL,

    ....

    COLN TBL_TYPE_XX 

)

NESTED TABLE COLN STORE AS EMBED_Table_XX tablespace orcl;

2.3.2   使用嵌套表

创建测试视图

CREATE OR REPLACE VIEW V_VIEW AS

select  T1.*,T2.* from  -- 不能用t1.*,有2个重复列

T_XXX t1,TABLE(t1.COLN ) t2;

select *from V_VIEW where 选择从句

查询需要2秒,查询计划如下:

 

为嵌套表增加索引

create unique index  IDX_1111 on EMBED_Table_XX (nested_table_id) TABLESPACE oacl

增加unique index 后, 选择行数下降很多,cost也下降很多。

select *from V_VIEW where 选择从句

查询需要0.1秒,查询计划如下:

 2.3.3   Version2

上个版本增加索引会额外的空间开销,采用如下IOT方式避免。

CREATE TABLE T_XXX(

    COL1 VARCHAR2(15) NOT NULL,

    COL2 VARCHAR2(15) NOT NULL,

    ....

    COLN TBL_TYPE_XX 

) tablespace orcl

NESTED TABLE COLN  STORE AS EMBEDED_Table_3

  ((

PRIMARY KEY(nested_table_id)));

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。