oracle 自定义类型

SQL 关于VARRAY类型在表中的应用

SQL 关于VARRAY类型在表中的应用

create or replace type emails_va as varray(2) of varchar2(30);
create table EMP
(
  EMPID   NUMBER not null,
  EMPNAME VARCHAR2(20),
  EMAILS  EMAILS_VA
)

insert into emp values       ( 1, 'tom', emails_va( 'x', 'y' ) );
insert into emp values       ( 2, 'lori', emails_va( 'a', 'b' ) );
select empid, empname, x.* from emp, table( emp.emails ) x

        1        tom          x
        1        tom          y
        2        lori        a
        2        lori        b
select empid,
       empname,
       max(decode(rn, 1, column_value, null)) email1,
       max(decode(rn, 2, column_value, null)) email2
  from (select empid,
               empname,
               x.*,
               row_number() over(partition by empid order by column_value) rn
          from emp, table(emp.emails) x)
group by empid, empname
        1        tom          x         y
        2        lori        a          b