--使用PL/SQL中记录
declare
     type emp_type is record(
     name emp.ename%type,
     sal  emp.sal%type,
     dno  emp.deptno%type);
     v_emp  emp_type;
begin
    select ename, sal, deptno
      into v_emp
      from emp
     where empno = 7369;
    dbms_output.put_line(v_emp.name ||',' || v_emp.sal ||',' || v_emp.dno);
end;

--使用记录表
declare
    type emp_type is table of emp%rowtype index by binary_integer;
    v_emp   emp_type;
begin
    select *
      into v_emp(1)
      from emp
     where empno =7369;
    
     dbms_output.put_line(v_emp(1).ename ||','|| v_emp(1).sal);
end;    
--使用索引表    
declare
   type emp_type is table of emp.ename%type index by binary_integer;
   v_emp   emp_type;
begin
    select ename
      into v_emp(1)
      from emp
     where empno =7369;
    
     dbms_output.put_line(v_emp(1));
end;

--使用嵌套表
declare
    type names is table of emp.ename%type;
    v_name   names;
begin
    v_name := names('MA1', 'MA2', 'MA3');
    select ename
      into v_name(1)
      from emp
     where empno = &eno;
    dbms_output.put_line('雇员名：'|| v_name(1));
end;
--表列中使用嵌套表
create type phone_type is table of varchar2(20);

create table employee(
    id    number(4),
    name  varchar2(10),
    sal   number(6,2),
    phone phone_type
)
nested table phone store as phone_table;

--嵌套表中插入数据
begin
    insert into employee values(1, 'scott', 800, phone_type('000000000', '111111111'));
    commit;
end;

--检索嵌套表中数据
declare
    v_phone  phone_type;
begin
    select phone
      into v_phone
      from employee
     where id = 1;
   
    for i in 1..v_phone.count loop
        dbms_output.put_line('电话号码：' || v_phone(i));
    end loop;
end;

--使用变长数组varray
declare
    type name_type is varray(20) of emp.ename%type;
    v_ename  name_type := name_type('may');
begin
    select ename
      into v_ename(1)
      from emp
     where empno = 7369;
   
    dbms_output.put_line('雇员名：' || v_ename(1));
end;

--索引表下标没有界限，且能为负数;嵌套表中下标也是没有界限，但下标不能为负数,从1开始
--变长数据varray下标是有界限的，下标从1，最大元素个数为9.

--使用多级varray
declare
    --定义一唯数组
    type var1 is varray(10) of int;
    --定义二唯数组
    type var2 is varray(10) of var1;
    --初始化二维数组
    v_arr  var2 := var2(var1(58,100, 102), var1(55,6,73),var1(2,4));
begin
    dbms_output.put_line('显示二维数组所有元素');
   
    for i in 1..v_arr.count loop
        for j in 1..v_arr(i).count loop
            dbms_output.put_line('v_arr(' || i || ',' || j || ')=' || v_arr(i)(j));
        end loop;
    end loop;
end;
   
--使用多级嵌套表
declare
    --定义一维嵌套表
    type var1 is table of int;
    --定义二维嵌套集合
    type var2 is table of var1;
    --初始化二维集合变量
    v_table  var2 := var2(var1(2,4),var1(5,8));
begin
    dbms_output.put_line('显示二维数组所有元素');
   
    for i in 1..v_table.count loop
        for j in 1..v_table(i).count loop
            dbms_output.put_line('v_table(' || i || ',' || j || ')=' || v_table(i)(j));
        end loop;
    end loop;
end;

--使用多级索引表
declare
    type var1 is table of int index by binary_integer;
    type var2 is table of var1 index by binary_integer;
    v_ind  var2;
begin
    v_ind(1)(1):= 10;
    v_ind(1)(2):= 5;
    v_ind(2)(1):= 100;
    v_ind(2)(2):= 50;
    dbms_output.put_line('显示二维数组所有元素');
    for i in 1..v_ind.count loop
       for j in 1..v_ind(i).count loop
           dbms_output.put_line('v_ind('|| i || ',' || j ||')=' || v_ind(i)(j));
       end loop;
    end loop;
end;