使用复合数据类型

使用复合数据类型

（一）PL/SQL 记录

标量变量只能处理单行单列的数据，而PL/SQL记录用于处理单行多列的数据。

①自定义记录类型和记录变量

• 自定义记录类型

语法：

TYPE type_name IS RECORD(

field datatype [[not null] <default | :=> <expression>]

[,......] );

• 定义记录变量

语法：

identifier type_name;

 

②使用%ROWRYPE属性直接定义记录变量

语法：|-identifier table_name%ROWTYPE;

         |-identifier view_name%ROWTYPE;

 

注：当使用%ROWTYPE属性定义记录变量时，记录成员个数、名称、类型与表或视图列的个数、名称、类型完全相同。

说明：当引用记录成员时，必须在成员名之前加记录变量名作为前缀。从ORACLE9i开始，用户可以在SELECT...INTO... 、INSERT、UPDATE语句中使用记录变量或记录成员。但用户只能在DELETE的WHERE子句中使用记录成员。

 

例1：在SELECT...INTO...语句中使用记录变量

declare
    type v_record is record(
    v_no dept.dname%type,
    v_name dept.dname%type,
    v_loc dept.loc%type);
    v v_record;
begin
    select *  into  v  from dept where deptno=10;
    dbms_output.put_line(v.v_name);
end;

输出：ACCOUNTING

例2：在SELECT...INTO...语句中使用记录成员

declare
    type v_record is record(
    v_no dept.dname%type,
    v_name dept.dname%type,
    v_loc dept.loc%type);
    v v_record;
begin
    select *  into v. v_no,v. v_name,v.v_loc from dept where deptno=10;
    dbms_output.put_line(v.v_loc);
end;

输出：NEW YORK

例3：在VALUES子句中使用记录变量

declare
    v v_dept%ROWTYPE;
begin
    select * into v from dept where deptno=10;
    insert into v_dept values v;
end;

例4：在VALUES子句中使用记录成员

declare
    v v_dept%ROWTYPE;
begin
    select loc,dname,deptno into v.loc,v.dname,v.deptno from dept where deptno=20;
    insert into v_dept values (v.deptno,v.dname,v.loc);
end;  

 

例5：在UPDATE语句中使用记录变量

declare
    v v_dept%rowtype;
begin
    select * into v from v_dept where deptno=10; 
    update v_dept set row=v where deptno=20;
end;

 

例6：在UPDATE语句中使用记录成员

declare
    v dept%rowtype;
begin
    v.deptno:=40;
    update v_dept set deptno=v.deptno where deptno=20;
end;

 

例7：在DELETE语句中使用记录成员

declare
    v dept%rowtype;
begin
    v.deptno:=40;
    delete from v_dept where deptno=v.deptno;
end;

(二) PL/SQL集合

标量变量，处理单行单列数据。PL/SQL记录，处理单行多列数据。PL/SQL集合，处理单列多行数据。

①索引表

特点：索引表的下标不仅可以为负值，而且其元素个数没有限制。注：索引表类型不能作为表列的数据类型使用。

•  定义索引表的语法：

TYPE  type_name  IS  TABLE OF element_type [not null]  INDEX BY key_type;

其中：

①element_type ：用于指定索引表元素的数据类型。

②key_type：用于指定索引表元素下标的数据类型（BINARY_INTEGER、PLS_INTEGER、VARCHAR2）。

③NOT NUL：表示不允许引用NULL元素，即是否允许索引表元素值为空。

• 定义索引表变量

identifier type_name;

 

例1：

declare
     type a_index is table of varchar2(20)
         index by binary_integer;
     a a_index;
     cursor b_cursor is select dname from dept;
begin
    open  b_cursor;
    for i in -1..2
       loop
          fetch b_cursor into a(i);
    end loop;
    close b_cursor;
    for i in reverse -1..2
       loop
       dbms_output.put_line(a(i));
   end loop;
   dbms_output.put_line(a.first);
end;    

 输出结果：

OPERATIONS
SALES
RESEARCH
ACCOUNTING
-1

 例2：  使用FIRST、LAST方法

declare
    type b_index is table of varchar2(20) index by varchar2(20);
    b b_index;
begin
    b('beijing'):='北京';
    b('aaa'):='阿富汗';
    b('shanghai'):='上海';
    b('guangzhou'):='广州';
    dbms_output.put_line(b.first);
    dbms_output.put_line(b.last);
end;

输出：

aaa
shanghai

②嵌套表

特点：嵌套表的元素下标从1开始，并且元素个数没有限制。注：嵌套表类型可以作为表列的数据类型使用。

• 定义嵌套表的语法

TYEP tyep_name IS TABLE OF element_tyep;

• 定义嵌套表变量

identifier type_name;

其中：element_type:用于指定嵌套表元素的数据类型

 

注：当使用嵌套表元素时，必须首先使用其构造方法初始化嵌套表。目的是指定嵌套表变量的元素个数和元素默认值。

例1：在PL/SQL块中使用嵌套表

declare
    type a_table is table of varchar2(20);
    a a_table;
begin
    a:=a_table('a','b','c');
    select  dname into a(2) from dept where deptno=&n;
    DBMS_OUTPUT.put_line(A(1)||A(2)||A(3));
END;
输出：aACCOUNTINGc

 

 

 

 

 

 

③

④

⑤

⑥ 集合方法

1、FIRST、LAST：用于返回集合变量第一个、最后一个元素的下标。