ORACLE中RECORD、VARRAY、TABLE的使用详解——转载

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;

 

记录类型是面向单行记录的。而要操作多行记录就必须使用集合了。ORACLE10g有提供三种集合供我们使用。
  1.1  varray 有点类似于java当中的数组.
  语法:TYPE ... VARRAY OF type(size_limit)_emelement[not null]
例:
DECLARE
  TYPE integer_varray IS VARRAY(5) OF INTEGER;
  t_varray INTEGER_VARRAY := integer_varray();
  j INTEGER:=1;
BEGIN
  WHILE j <=5 LOOP
     DBMS_OUTPUT.PUT_LINE('J=' || j);
     --初始化
     t_varray.EXTEND;
     t_varray(j):=10+j;
     j := j+1;
  END LOOP;
  --varray是从1开始计数下标的,这一点不同于java中的0计数
  FOR i in 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE(t_varray(i));
  END LOOP;
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;

 

1.2 嵌套表 类似于JAVA中的ArrayList吧,动态数组.
  语法:TYPE ... IS TABLE OF tepe[not null]
例:
DECLARE
  TYPE t_table IS TABLE OF VARCHAR(10 char);
  t T_TABLE := t_table();
BEGIN
  FOR i in 1..5 LOOP
      t.extend;
      t(i) := 'testtestt' || +i;
  END LOOP;
 
  FOR i in 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('t=' || t(i));
  END LOOP;
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集合不能直接对其进行查询。只能对其进行遍历。