PL/SQL记录和表（oracle type(数组)）（最简单！文章下面标黄标题是精华，必看）

PL/SQL记录和表
1.使用%TYPE  (用于基本数据类型)
例1（用户并不知道Course_No的数据类型，只知道他是基于Student_No数据类型的，随着Student_No的类型变化而变化）：
DECLARE
...
Student_No INTEGER;
...
Course_No Student_No%TYPE;

例2（表示BrowsePath的数据类型是基于表BookInfo中的StoredPath列的数据类型）：
DECLARE
...
BrowsePath BookInfo.StoredPath%TYPE;
...

2.记录类型（注意，我自己实验，这里用IS RECORD会报错，要用create or replace type .. is object ！！）
语法：
TYPE 记录类型 IS RECORD
( 字段1 数据类型 [NOT NULL] {DEFAULT |:= } 表达式 ]
  [  ,  字段2  ……]
)

例子(全手打啊！！)

（下面一整个例子要一次性执行，语法是：
DECLARE
...
BEGIN
...
END;）

例：

SET SERVEROUTPUT ON
DECLARE
 TYPE StudentRecord IS RECORD (
 StudentNo INTEGER,
 Name VARCHAR2(10),
 Study VARCHAR2(10),
 Activity Study%TYPE
 );

 Student1 StudentRecord;

PROCEDURE PrintStudentRecord(StudentA IN StudentRecord) IS
BEGIN
 DBMS_OUTPUT.enable;
 DBMS_OUTPUT.put('学号：  '|| StudentA.StudentNo);    --DBMS_OUTPUT.put是不换行
 DBMS_OUTPUT.put('姓名：  '|| StudentA.Name);
 if StudentA.Study = 'Excellent' then
  DBMS_OUTPUT.put('学习成绩 优秀');
 elsif StudentA.Study = 'Good' then
  DBMS_OUTPUT.put('学习成绩 良好');
 end if;
 if StudentA.Activity = 'Excellent' then
  DBMS_OUTPUT.put('活动成绩 优秀');
 elsif StudentA.Activity = 'Good' then
  DBMS_OUTPUT.put('活动成绩 良好');
 end if;
 DBMS_OUTPUT.put_line('       一条学生信息打印完毕');
END PrintStudentRecord;

BEGIN
 Student1.StudentNo := 112;
 Student1.Name := '王飘飘';
 Student1.Study := 'Excellent';
 Student1.Activity := 'Good';

 PrintStudentRecord(Student1);
END;

3.使用%ROWTYPE （用于记录类型和表结构）
例(表明变量InputStudent是与表StudentInfo的字段一致的记录变量)：
DECLARE
...
InputStudent StudentInfo%ROWTYPE;
...

4.表
语法：
TYPE PL/SQL表名 IS TABLE OF 数据类型

例1（声明的StudentName表是由和StudentInfo表中Name列相同类型的信息组成的）
DECLARE
...
TYPE StudentName  IS TABLE OF StudentInfo.Name%TYPE;
...
例2（声明的StudentName表是由数据类型为CHAR(20)的列信息组成的）
DECLARE
...
TYPE StudentName  IS TABLE OF CHAR(20) INDEX BY BINARY_INTEGER;
...

 

我的提问（我要定义一个String数组，有2个字段，是个表（不止一行（record），有多行；Table只允许有一个字段，怎么办呢））：
type .. is table of(a varchar(20),b varchar(20))这样貌似不行 。
只能
type .. is record(a varchar(20),b varchar(20))

但是我现在需要用type做一个String数组的功能，应该是用table的，但table语法就是
 ..is table of数据类型，没法放2个字段，怎么办

解答（貌似有问题）：
type T_tbl is record(
            a     NUMBER(16),
            b     NUMBER(16));
type v_table is table of T_tbl;
v_arr v_table:=v_table();

我的实验：

1.创建type of object(用create or replace语法时，要用object；用declare ..begin ..end 时，可以直接用type.. is record 。不是record,它会报错)

创建法1：

create or replace type ty_mxklrownum is object(
            index_rum number(5,0),sendtype char(1));

（此创建法如果写成下面那样就会报错：

create or replace  type ty_mxklrownum isrecord(
            index_rum number(5,0),sendtype char(1));

若要正确，则用下面语法：

declare

    type ty_mxklrownum is record(

       index_rum number(5,0),sendtype char(1));

begin

  .....

end;

）

2.创建type is table of

create or replace type ty_table_mxklrownum as table of ty_mxklrownum; 

 

3.创建测试用存储过程（入参错了，应该是table，但是不知道里面怎么用所以直接忽略了）
create or replace PROCEDURE PrintStudentRecord(StudentA IN ty_mxklrownum) IS
BEGIN
 DBMS_OUTPUT.enable;
 DBMS_OUTPUT.put('行号：  '|| StudentA.index_rum);    --DBMS_OUTPUT.put是不换行
 DBMS_OUTPUT.put('type：  '|| StudentA.sendtype);
 DBMS_OUTPUT.put_line('       一条信息打印完毕');
END PrintStudentRecord;

 

4.命令窗口测试：
SET SERVEROUTPUT ON
declare  
    Student1   ty_table_mxklrownum;  
begin  
    Student1   :=   ty_table_mxklrownum();  
    Student1.extend(10);  
    Student1(1) := ty_mxklrownum(1,'0');
 PrintStudentRecord(Student1);
end;

 一点笔记：

TYPE TABLE_COL IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
column_select      TABLE_COL;
这两句是什么意思?
答：
TABLE 相当于是数组,这里定义了一个数组类型TABLE_COL
INDEX BY BINARY_INTEGER这里是定义数组下标是整数,因为ORACLE中下标可以是字符串.
 VARCHAR2(4)这是定义数组里面只能放字符串
column_select TABLE_COL; 定义了一个TABLE_COL类型的变量column_select
因为TABLE_COL被定义成了数组类型,所以column_select
就相当于是一个数组啦.

定义一个联合数组，
TABLE OF VARCHAR2 (4000), 是数组存在数据的类型.

还比如：
Type Syncdata Is Table Of Varchar2(4000) Index By Varchar2(64);
定义一个联合数组，
Table Of Varchar2(4000)是数组存在数据的类型，
Index By Varchar2(64)是数组下标的类型。

再比如还可以这样：
type TDictRecord is record
  (
  New_Code varchar2(256),
  New_Name varchar2(1024),
  Old_Name varchar2(1024)
  );

  --dict data
  type TDictData is table of TDictRecord index by varchar2(256);
定义一个联合数组，
table of TDictRecord 是数组存在数据的类型，
index by varchar2(256) 是数组下标的类型 指定为字符串型 （varchar）

 

 

下面是从itpub转载来的，精华！！下面必看！！（原地址：http://www.itpub.net/thread-610297-1-1.html）

--本文档可以直接拷贝运行。

/*
oracle复合数据类型
PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。
*/

/*
一、记录类型

类似C语言中的结构，有两种定义方式：显式定义和隐式定义。
*/

create table test
(
id varchar2(20),
mc varchar2(60)
);

insert into test values('111','11111');
insert into test values('222','22222');
insert into test values('333','33333');
insert into test values('444','44444');
insert into test values('555','55555');
insert into test values('666','66666');

commit;

/*

1.1、显示定义记录

*/

set serverout on
declare
type t_record is record
(
id test.id%type,
mc test.mc%type
);
var_record t_record;
counter number default 0;
begin
        for row_test in (select id,mc from test) loop
                counter := counter + 1;
                var_record.id := row_test.id;
                var_record.mc := row_test.mc;
                dbms_output.put_line('var_record:'||var_record.id||'---'||var_record.mc);
                dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc);
                dbms_output.put_line('================loop '||counter||' times.');
        end loop;
        exception when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end;
/

/*有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的ld和:new记录。*/

/*

1.2、隐式定义记录

*/

隐式定义记录中，我们不用描述记录的每一个域，在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录。

declare
t_record1 test%rowtype;
cursor cur_test(v_id in varchar2) is
select id,mc from test
where id <= v_id;
t_record2 cur_test%rowtype;
begin
        for row_test in cur_test('333') loop
                t_record1.id := row_test.id;
                t_record1.mc := row_test.mc;
                t_record2.id := row_test.id;
                t_record2.mc := row_test.id;
                dbms_output.put_line('t_record1:'||t_record1.id||'---'||t_record1.mc);
                dbms_output.put_line('t_record2:'||t_record2.id||'---'||t_record2.mc);
                dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc);
                dbms_output.put_line('================loop '||cur_test%rowcount||' times.');
        end loop;
        exception when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end;
/

/*

二、集合

类似C语言中的数组，在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引（INDEX_BY）表。
PL/SQL有三种类型的集合

a、Index_by表
b、嵌套表
c、VARRAY

三种类型的集合之间的差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都有不相同。

数据绑定：
        绑定涉及到集合中元素数量的限制，VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。
稀疏性(sparsity)：
        稀疏性描述了集合的下标是否有间隔，Index_by表和嵌套表可以是稀疏的，VARRAY类型的集合则是紧密的，它的下标之间没有间隔。
存储：
        Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。
       
Index_by表定义语法如下：
        TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;
        关键字是INDEX BY BINARY_INTEGER，没有这个关键字，那么集合将是一个嵌套表。由于不存储在数据库中，
        element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。

嵌套表定义语法如下：
        嵌套表非常类似于Index_by表，创建的语法也非常相似。只是没有INDEX BY BINARY_INTEGER子串
        TYPE type_name IS TABLE OF element_type [NOT NULL];
        存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块中，它们实际上被存放在第二个表中。
        从数据库中取回的嵌套表也不保证元素的顺序。集合数据是离线存储的，所以嵌套表适合大型集合。
       
VARRAY定义语法如下：
        TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF element_type [NOT NULL];
                max_size是一个整数，用于标示VARRAY集合拥有的最多元素数目。VARRAY集合的元素数量可以低于max_size,但不能超过max_size。
                element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段（与嵌套标相似）。
                VARRAY存储在数据库中时与表中的其他数据存放在同一个数据块中，元素的顺序保存在VARRAY中。
                集合是在线存储的，VARRAY很适合于小型集合。
               
嵌套表和VARRAY都能作为列存储在数据库表中，所以集合自身可以为NULL,当集合为NULL时，用户也不能引用集合中的元素。
用户可以使用IS NULL操作符检测集合是否为NULL。

1、index_by表：

*/

declare
cursor cur_test is select id,mc from test;
type t_test1 is table of varchar2(60) index by binary_integer;
type t_test2 is table of test%rowtype index by binary_integer;
var_test1 t_test1;
var_test2 t_test2;
var_new t_test2;
begin
SELECT id,mc INTO var_test2(0) FROM test WHERE id='111';
dbms_output.put_line('var_test2(0):'||var_test2(0).id||'---'||var_test2(0).mc);
SELECT id,mc INTO var_test2(8) FROM test WHERE id='333';
dbms_output.put_line('var_test2(8):'||var_test2(8).id||'---'||var_test2(8).mc);
var_new := var_test2;
dbms_output.put_line('=====  copy var_test2 to var_new  =====');
dbms_output.put_line('var_new(0):'||var_new(0).id||'---'||var_new(0).mc);
dbms_output.put_line('var_new(8):'||var_new(8).id||'---'||var_new(8).mc);
end;
/

/*
嵌套表和VARRAY由构造函数初始化

2、嵌套表和VARRAY：

*/

DECLARE
TYPE t_test1 IS TABLE OF test.id%TYPE;
TYPE t_test2 IS VARRAY (10) OF test.id%TYPE;
var_test1 t_test1;
var_test2 t_test2;
begin
        --var_test1(1) := ('test1.1'); --没有初始化不能赋值
        var_test1 := t_test1('test1.1','test1.2','test1.3');
        dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));
        var_test2 := t_test2('test2.1','test2.2','test2.3');
        dbms_output.put_line('var_test2: '||var_test2(1)||','||var_test2(2)||','||var_test2(3));
        var_test1(2) := 'test1.2_update';
        dbms_output.put_line('====  修改了var_test1(2)  ====');
        dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));
        dbms_output.put_line(var_test1.next(3));
        dbms_output.put_line('var_test2元素个数: '||var_test2.limit());
end;
/

/*
嵌套表的元素可以是集合,注意赋值的时候是varray_element.record_column := 的形式.
除了构造函数外,集合还有很多内建函数，按照面向对象编成的叫法称之为方法。
方法==========描述====================================================================使用限制
COUNT=========返回集合中元素的个数   
DELETE========删除集合中所有元素  
DELETE(x)=====删除元素下标为x的元素===================================================对VARRAY非法
DELETE(x,y)===删除元素下标从X到Y的元素================================================对VARRAY非法
EXIST(x)======如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE  
EXTEND========在集合末尾添加一个元素==================================================对Index_by非法
EXTEND(x)=====在集合末尾添加x个元素===================================================对Index_by非法
EXTEND(x,n)===在集合末尾添加元素n的x个副本============================================对Index_by非法
FIRST=========返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。  
LAST==========返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.   
LIMIT=========返回VARRY集合的最大的元素个数===========================================Index_by集合和嵌套表无用
NEXT(x)=======返回在第x个元素之后及紧挨着它的元素的值，如果x是最后一个元素，返回null.  
PRIOR(x)======返回在第x个元素之前紧挨着它的元素的值，如果x是第一个元素，则返回null。  
TRIM==========从集合末端开始删除一个元素==============================================对于index_by不合法
TRIM(x)=======从集合末端开始删除x个元素===============================================对index_by不合法

*/

/*

三、综合实例BULK COLLECT的用法

*/

set serverout on
DECLARE
TYPE t_record IS RECORD (
id number(18,0),
mc varchar2(50)
);
var_record t_record;
type t_test is table of t_record;
var_test t_test := t_test();
cursor cur_test is select id,mc from test;
begin
open cur_test;
fetch cur_test BULK COLLECT INTO var_test;
for i in 1..var_test.count() loop
        dbms_output.put_line(var_test(i).id||'---'||var_test(i).mc);
end loop;
end;
/

 

/*

一个oracle函数返回数组的例子 今天给一个朋友写了个函数返回数组的例子，顺便贴出来。 create or replace type t_ret_table is table of varchar2(20); create or replace function f_test(var_num in integer) return t_ret_table is var_out t_ret_table; begin         var_out := t_ret_table();         var_out.extend(var_num);         for i in 1..var_num loop                 var_out(i) := i;         end loop;         return var_out; end f_test; / set serverout on declare aa t_ret_table; begin aa := f_test(10); for i in 1..aa.count loop         dbms_output.put_line(aa(i)); end loop; end; /

*/