Oracle中RECORD、VARRAY、TABLE的使用详解

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_NAME IS 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集合不能直接对其进行查询。只能对其进行遍历。

---

Oracle系列：记录Record

一，什么是记录（Record）？

由单行多列的标量构成的复合结构。可以看做是一种用户自定义数据类型。组成类似于多维数组。
将一个或多个标量封装成一个对象进行操作。是一种临时复合对象类型。

记录可以直接赋值。RECORD1 :=RECORD2；
记录不可以整体比较.
记录不可以整体判断为空。

二，%ROWTYPE和记录（Record）？

请区别%ROWTYPE和记录（Record）类型。%ROWTYPE可以说是Record的升级简化版。
区别在与前者结构为表结构，后者为自定义结构。二者在使用上没有很大区别。前者方便，后者灵活。在实际中根据情况来具体决定使用。
Record + PL/SQL表可以进行数据的多行多列存储。

三，如何创建和使用记录？

①创建记录类型
语法：

  TYPE  记录名  IS RECORD 
 ( 
   filed1 type1 [NOT NULL] [：=eXPr1],
　　     ....... ,
　 filedN typen [NOT NULL] [：=exprn] 
  ）

其中，filed1是标量的名字。

②声明记录类型变量：
记录类型变量名 记录类型

③填充记录。

④访问记录成员

   记录类型变量名.filed1
   .........
   记录类型变量名.filedN

注意：
表字段类型修改后，还需要修改记录字段类型，有时候可能会忘记，从而出现错误。

对于记录内每个字段（filed1.。。。），可以指定也可以使用%TYPE和%ROWTYPE动态指定记录字段类型。

好处是表字段发生变化，记录字段自动改变。但是，由于每次执行前，遇到%TYPR或%ROWTYPE，数据库系统都会去查看对应表字段类型，会造成一定的数据库开销，如果系统中大量使用记录类型，则对性能会有一定影响。

另外如果删除了某一字段，而自定义记录中使用了该字段，也会有可能忘记删除该字段。

对数据库负荷偏低的系统，性能问题一般可以不重点关注，但是对于高负荷数据库服务器，

各个环节都要考虑性能问题，每处节省一点出来，性能整体就有很大提高。

语法：

  TYPE  记录名  IS RECORD 
　　( 
   filed1 table.Filed%Type [NOT NULL] [：=eXPr1] ,
   filed2 table.Filed%Type [NOT NULL] [：=eXPr1] ,
　　     ....... ,
　　  filedn table.Filed%Type [NOT NULL] [：=exprn] 
  ）;

例子：记录可以整体赋值

 /*conn scott/tiger
 Create Table  empa  As  Select * From emp;
 */
 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:='SMITH';
   EmpRec1.Job:='CLERK';
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   EmpRec2 := EmpRec1;
   DBMS_output.put_line(EmpRec2.empno);
 End;

例子：记录不可以整体比较，只可以比较记录字段

 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec1  EmpType;
   EmpRec2  EmpType;
 Begin
   EmpRec1.Empno:=7369;
   EmpRec1.Ename:='SMITH';
   EmpRec1.Job:='CLERK';
   EmpRec1.Sal:=800;
   EmpRec1.Deptno:=10;
   if EmpRec1.sal < EmpRec2.sal then
     DBMS_output.put_line('Xiao Xiao Xiao');
   end if;
 End;

例子：记录不可以整体判断为空，只可以判断记录字段。

 Declare
     Type EmpType is Record(
        EMPNO number(4),
        ENAME  varchar2(10),
        JOB varchar2(15),
        SAL number(7,2),
        DEPTNO number(2)
     );
   EmpRec  EmpType;
 Begin
   if EmpRec.ename is null then
     DBMS_output.put_line('Kong Kong Kong');  
   end if;
 End;

例子：使用%TYPE和%ROWTYPE动态指定记录字段。

 /*conn scott/tiger
 Create Table  empa  As  Select * From emp;
 */
 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   EMPA.ENAME%Type,
   RJOB   EMPA.JOB%Type
  );
  EmpRec  MyRecType;
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  If  EmpRec.RJOB  = 'CLERK'  Then
   DBMS_OUTPUT.PUT_LINE('Name: '||EmpRec.RENAME);
  End If;
 End;

例子：数据集中的记录和记录类型中的数据关系。

 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   EMPA.ENAME%Type,
   RJOB   EMPA.JOB%Type
  );
  EmpRec  MyRecType;
  vJob  EMPA.JOB%Type;
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  DBMS_OUTPUT.PUT_LINE('MyRecType.RJOB: '||EmpRec.RJOB);
  EmpRec.RJOB  := '修改值后'  ;
  DBMS_OUTPUT.PUT_LINE('MyRecType.RJOB: '||EmpRec.RJOB);
  Select JOB  InTo vJob  from empa  Where empa.EMPNO = EmpRec.RENO;
  DBMS_OUTPUT.PUT_LINE('EMPA.JOB: '||vJob);
 End;

四，使用记录向表中插入数据？

根据表结构合理安排记录字段。比如主外键。
如果用记录(RECORD)插入数据，那么只能使用记录成员；
如果用%ROWTYPE插入数据,可以直接使用%ROWTYPE。

例子：使用记录成员向表中插入数据

 DECLARE
  Type MyRecType Is  Record
  (
   RENO  EMPA.EMPNO%Type,
   RENAME   VARCHAR2(10),
   RJOB   EMPA.JOB%Type
  );
  EmpRec MyRecType; 
 Begin
  Select   EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '7369';
  DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||'  '||EmpRec.RENAME||'  '||EmpRec.RJOB);

  EmpRec.RENO  := 1001;
  EmpRec.RENAME := '杰克';
  EmpRec.RJOB  := '办事员';

  Insert InTo  empa(EMPNO,ENAME,JOB)  Values(EmpRec.RENO, EmpRec.RENAME,EmpRec.RJOB);

  Select  EMPNO, ENAME, JOB  InTo  EmpRec  From empa Where empa.EMPNO = '1001';
  DBMS_OUTPUT.PUT_LINE(EmpRec.RENO||'  '||EmpRec.RENAME||'  '||EmpRec.RJOB);
 End;

五，使用记录更新数据？

如果用记录(RECORD)更新数据，那么只能使用记录成员；
如果用%ROWTYPE更新数据,可以直接使用%ROWTYPE。

例子：使用%ROWTYPE向表中插入数据

DECLARE
  vEmp empa%RowType;
 Begin
  Select  *  InTo  vEmp  From empa Where empa.EMPNO = '7369';
  UpDate empa Set ROW = vEmp Where EMPNO = 1001;
 End;

六，使用记录删除数据？

删除记录时，只能在delete语句的where子句中使用记录成员。