文档章节

ORACLE中RECORD、VARRAY、TABLE

易野
 易野
发布于 2017/07/26 10:33
字数 1643
阅读 13
收藏 0
点赞 0
评论 0

1     类型说明

1.1       RECORD

定义记录数据类型。它类似于C语言中的结构数据类型(STRUCTURE),PL/SQL提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法,即RECORD复合数据类型。在使用记录数据类型变量时,需要在声明部分先定义记录的组成、记录的变量,然后在执行部分引用该记录变量本身或其中的成员。
    定义记录数据类型的语法如下:
TYPE RECORD_NAME IS RECORD(
V1  DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],
V2  DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],
VN  DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);
1.2       VARRAY

数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标,它取决于成员在数组中的位置。在PL/SQL中,数组数据类型是VARRAY(variable array,即可变数组)。
定义VARRAY数据类型的语法如下:
TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];
其中,varray_name是VARRAY数据类型的名称,size是正整数,表示可以容纳的成员的最大数量,每个成员的数据类型是element_typeo默认时,成员可以取空值,否则需要使用NOT NULL加以限制。
 
1.3       TABLE

定义记录表(或索引表)数据类型。它与记录类型相似,但它是对记录类型的扩展。它可以处理多行记录,类似于C语言中的二维数组,使得可以在PL/SQL中模仿数据库中的表。
定义记录表类型的语法如下:
TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]
INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];
关键字INDEX BY表示创建一个主键索引,以便引用记录表变量中的特定行。
BINARY_INTEGER的说明
如语句:TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”后,NUMBERS类型的下标就是自增长,NUMBERS类型在插入元素时,不需要初始化,不需要每次EXTEND增加一个空间。
而如果没有这句话“INDEXBY BINARY_INTEGER”,那就得要显示对初始化,且每插入一个元素到NUMBERS类型的TABLE中时,都需要先EXTEND。
 
2     举例

2.1       创建表结构以及数据准备
--组织机构结构表  
CREATE TABLE SF_ORG  
(  
ORG_ID INT NOT NULL, --组织机构主键ID  
ORG_NAME VARCHAR2(50),--组织机构名称  
PARENT_ID INT--组织机构的父级  
)  
  
--一级组织机构  
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);  
  
--二级部门  
  
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);  
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);  
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1);  

2.2       RECORD的使用举例

先定义一个只与SF_ORG表中某几个列的数据类型相同的记录数据类型TYPE_ORG_RECORD,然后声明一个该数据类型的记录变量V_ORG_RECORD,最后用替换变量&ORG_ID接受输入的雇员编码,查询并显示该雇员的这几列中的信息。注意,在使用RECORD数据类型的变量时要用“.”运算符指定记录变量名限定词。
    一个记录类型的变量只能保存从数据库中查询出的一行记录,如果查询出了多行记录,就会出现错误。
 

DECLARE   
  TYPE TYPE_ORG_RECORD IS RECORD(  
  V_ORG_NAME SF_ORG.ORG_NAME%TYPE,  
  V_PARENT_ID SF_ORG.PARENT_ID%TYPE);  
  V_ORG_RECORD TYPE_ORG_RECORD;  
BEGIN  
  SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD  
  FROM SF_ORG SO  
  WHERE SO.ORG_ID=&ORG_ID;  
  DBMS_OUTPUT.PUT_LINE('部门名称:' || V_ORG_RECORD.V_ORG_NAME);  
  DBMS_OUTPUT.PUT_LINE('上级部门编码:' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID));  
END;  

2.3       VARRAY的使用举例

先定义一个能保存5个VARCHAR2(25)数据类型的成员的VARRAY数据类型ORG_VARRAY_TYPE,然后声明一个该数据类型的VARRAY变量V_ORG_VARRAY,最后用与ORG_VARRAY_TYPE数据类型同名的构造函数语法给V_ORG_VARRAY变量赋予初值并显示赋值结果。
注意,在引用数组中的成员时.需要在一对括号中使用顺序下标,下标从1开始而不是从0开始。

DECLARE   
  TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25);  
  V_ORG_VARRAY ORG_VARRAY_TYPE;  
BEGIN  
  V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');  
  DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4));  
  DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_VARRAY(5));  
  V_ORG_VARRAY(5) := '5001';  
  DBMS_OUTPUT.PUT_LINE('输出3:' || V_ORG_VARRAY(5));  
END;  

2.4       TABLE使用举例

2.4.1      存储单列多行

这个和VARRAY类似。但是赋值方式稍微有点不同,不能使用同名的构造函数进行赋值。具体的如下:
 

DECLARE   
  TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)  
  INDEX BY BINARY_INTEGER;  
  V_ORG_TABLE ORG_TABLE_TYPE;  
BEGIN  
  V_ORG_TABLE(1) := '1';  
  V_ORG_TABLE(2) := '2';  
  V_ORG_TABLE(3) := '3';  
  V_ORG_TABLE(4) := '4';  
  V_ORG_TABLE(5) := '5';  
  DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4));  
  DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_TABLE(5));  
END;  

2.4.2      存储多列多行和ROWTYPE结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。
 
 

DECLARE   
   TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;  
   V_TYPE  T_TYPE;  
 BEGIN  
    SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE  
    FROM SF_ORG  
    WHERE SF_ORG.ORG_ID <= 3;  
  
    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP  
        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);  
    END LOOP;  
 END;  

2.4.3      存储多列多行和RECORD结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。

DECLARE   
   TYPE TEST_EMP IS RECORD  
   (  
    C1 SF_ORG.ORG_NAME%TYPE,  
    C2 SF_ORG.PARENT_ID%TYPE  
   );     
   TYPE T_TYPE IS TABLE OF TEST_EMP;  
   V_TYPE  T_TYPE;  
 BEGIN  
    SELECT ORG_NAME,  PARENT_ID BULK COLLECT INTO V_TYPE  
    FROM SF_ORG  
    WHERE SF_ORG.ORG_ID <= 3;  
  
    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP  
        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);  
    END LOOP;  
 END;  

3     问题

VARRAY和TABLE集合不能直接对其进行查询。只能对其进行遍历。

 

在开发中,我们会遇到下面的情形,在PL/SQL程序中定义了type类型的集合,当引用的时候出现PLS-00642错误:

PLS-00642: local collection types not allowed in SQL statements

SQL> DECLARE  
    TYPE id IS TABLE OF NUMBER;  
    v_id id := id(649605799, 649605800, 649605801, 649605802);  
  BEGIN  
    FOR rec IN (SELECT column_value id FROM TABLE(v_id))  
    LOOP  
      dbms_output.put_line('ID:' || rec.id);  
    END LOOP;  
  
  END;  
  /  
  FOR rec IN (SELECT column_value id FROM TABLE(v_id))  
                                                *  
ERROR at line 5:  
ORA-06550: line 5, column 49:  
PLS-00642: local collection types not allowed in SQL statements  
ORA-06550: line 5, column 43:  

出现这种问题的原因是,SQL语句中使用了本地定义的Type类型,Type必须定义为Schema级别的才可以使用。

解决办法:

           1.在Schema级别定义Type,即使用CREATE TYPE语法定义,而不是在PL/SQL子程序中定义。

SQL> CREATE OR REPLACE TYPE id AS TABLE OF NUMBER;  
  2  /  
  
Type created.  
  
SQL> DECLARE  
    v_id id := id(649605799, 649605800, 649605801, 649605802);  
  BEGIN  
    FOR rec IN (SELECT column_value id FROM TABLE(v_id))  
    LOOP  
      dbms_output.put_line('ID:' || rec.id);  
    END LOOP;  
  
  END;  
  /  
  
PL/SQL procedure successfully completed.  
  
SQL>  

  2. 使用select bulk collect into

eg:    The following anonymous block compiles with PLS-00642 because select into can't be used with collections:

declare
  type dual_list_t is table of dual%rowtype;
  v_duals dual_list_t;
begin
  select *
    into v_duals
    from dual
  connect by level <= 2
  ;
end;
/

正确的写法应该是:

declare
  type dual_list_t is table of dual%rowtype;
  v_duals dual_list_t;
begin
  select *
    bulk collect into v_duals
    from dual
  connect by level <= 2
  ;
end;
/

 

© 著作权归作者所有

共有 人打赏支持
易野
粉丝 3
博文 144
码字总数 114303
作品 0
深圳
[顶]ORACLE PL/SQL编程详解之二:PL/SQL块结构和组成元素(为山九仞,岂一日之功)

2.1 PL/SQL块 PL/SQL程序由三个块组成,即声明部分、执行部分、异常处理部分。 PL/SQL块的结构如下: DECLARE --声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 ...

日久不生情
2017/11/08
0
0
Oracle PLSQL的集合类型

转载:http://www.cnblogs.com/gkl0818/archive/2009/02/25/1397769.html Oracle collections 一、Types of Collections 1、Associative arrays 数组 它是同种类型的一维、无边界的稀疏集合,......

晨曦之光
2012/04/25
1K
0
PL/SQL-2 复合数据类型

-------------------------------------------------------------------------- 第二部分:复合数据类型 -------------------------------------------------------------------------- ---......

晨曦之光
2012/04/19
123
0
Oracle Predefined PL/SQL Exceptions 预定义异常

Predefined PL/SQL Exceptions An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle er......

baser
2016/12/16
0
0
oracle pl/sql(二)

2014-04-14 1.标量变量:用于存放单行单列的数据 2.复合数据类型 1)PL/SQL记录:处理单行多列数据 2)PL/SQL集合:用于处理多行单列数据 AA:索引表(Associative arrays, also known as index...

蓝蝶飞扬
2014/04/15
0
0
《oracle pl/sql程序设计》学习笔记一

本篇主要是记录一些容易混淆或者以前理解不够清楚的知识点,只选择常用知识点。 一、case语句和case表达式 1、case语句和case表达式都有两种模式:简单型和搜索型。 简单型:case exp when r...

hulubo
2012/07/08
0
3
如何判断PLSQL中三种集合是否为空

用count判断联合数组(associative array),因为index-by table声明就初始化,用count判断不会引起异常。 用cardinality判断嵌套表(nested table),因为nested table声明时没有被初始化,...

晨曦之光
2012/04/25
875
0
jOOQ 2.0.4 发布,Java的ORM框架

jOOQ 2.0.4 发布,该版本引入很多源码生成的提升,同时对集成 Oracle 的 TABLE 和 VARRAY 类型还有大量的工作需要做。 jOOQ 高效的合并了复杂SQL、类型安全、源码生成、Active Records、存储...

红薯
2012/02/13
276
0
Oracle 中VARRAY的 NOT NULL之惑

如果在定义VARRAY的时候带上NOT NULL限制,那么这个VARRAY的元素就不能为NULL. 如下定义: CREATE OR REPLACE TYPE integer_varray AS VARRAY(5) OF INTEGER NOT NULL; / 然后有一个PLSQ...

晨曦之光
2012/04/25
71
0
PLSQL集合笔记

针对多行单列的数据处理,用之前的标量变量肯定不行,这里引入一个新的数据类型复合数据类型。 这个复合类型类似于语言的数组struct[i],包括索引表,嵌套表,边长数组三种类型, 记住记录是...

aklaus
2017/08/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Linux服务器下的HTTP抓包分析

说到抓包分析,最简单的办法莫过于在客户端直接安装一个Wireshark或者Fiddler了,但是有时候由于客户端开发人员(可能是第三方)知识欠缺或者其它一些原因,无法顺利的在客户端进行抓包分析,...

mylxsw
10分钟前
0
0
mybatis3-javaapi

sqlSessionFactoryBuilder->sqlSessionFactory->sqlSession<-rowbound<-resultHandler myBatis uses a Java enumeration wrapper for transaction isolation levels, called TransactionIsol......

writeademo
13分钟前
0
0
Java NIO:浅析I/O模型

也许很多朋友在学习NIO的时候都会感觉有点吃力,对里面的很多概念都感觉不是那么明朗。在进入Java NIO编程之前,我们今天先来讨论一些比较基础的知识:I/O模型。下面本文先从同步和异步的概念...

yzbty23
14分钟前
0
0
了解iOS消息推送一文就够:史上最全iOS Push技术详解

本文作者:陈裕发, 腾讯系统测试工程师,由腾讯WeTest整理发表。 1、引言 开发iOS系统中的Push推送,通常有以下3种情况: 1)在线Push:比如QQ、微信等IM界面处于前台时,聊天消息和指令都会...

JackJiang-
15分钟前
0
0
Mysql汉子转拼音

update t_app_city SET CITY_NAME_BEGIN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(CITY_NAME USING gbk),1)),16,10), 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 0xBFA......

尘叙缘
17分钟前
0
0
大数据构建智慧城市“新引擎”,加速推进新旧动能转换

——“大数据与智慧城市”技术交流分享会——济南站召开 7月13日,“大数据携手智慧城市,助力山东新旧动能转换”技术交流分享会——济南站在山东信息通信技术研究院会议室成功举办,此次会议...

左手的倒影
19分钟前
2
0
tomcat 学习笔记之 Session管理

1、Catalina 通过一个 Session 管理器的组件来管理建立的Session 对象 该组件由 org.apache.catalina.Manager 接口表示 Session 管理器必须与一个 Context 关联 Session 管理器负责,创建、更...

职业搬砖20年
20分钟前
0
0
jquery获取input框的几种方式

//如何用jquery获取<input id="test" name="test" type="text"/>中输入的值?$(" #test ").val()$(" input[ name='test' ] ").val()$(" input[ type='text' ] ").val()$(" input[ ......

gulf
23分钟前
0
0
gradle的环境变量的配置

gradle的环境变量的配置 1.首先下载jdk,并且配置jdk的环境变量. 2.找到自己AS安装gradle的目录 我自己的目录为:F:\Android Studio3.1.3\gradle\gradle-4.4 创建环境变量:GRADLE_PATH: F:\A...

android-key
29分钟前
0
0
saltstack配置apache

1.相关配置 #vim /etc/salt/master //打开如下内容的注释 file_roots: base: - /srv/salt #mkdir /srv/salt #vim /srv/salt/top.sls base: 'slaver.test.com': - apache 注意:若换成 '*',则......

硅谷课堂
29分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部