Oracle自定义类型

Oracle自定义类型可以通过type/create type来声明或者创建

一，四种创建方式

1.1，使用create type创建object类型

create or replace type obj_type as object(id number,name varchar2(50 byte),birthday date);

 1.2，使用create type创建table类型

create or replace type table_type as table of obj_type; 

 创建table类型要依赖于object类型，就是as table of后面接object类型。

1.3，使用type创建object类型

declare
  type obj_type is record(id number,name varchar2(50 byte),birthday date);
begin
  dbms_output.put_line('');
end;

 1.4，使用type创建table类型

declare
  type obj_type is record(id number,name varchar2(50 byte),birthday date);
  type table_type is table of obj_type;
begin
  dbms_output.put_line('');
end;

区别是create可以独立执行，create后面用as

type只能在语句块中执行，type后面用is

 

二，Oracle数组类型或者说list类型

oracle有这样的一种方式，可以动态的将object变量添加到一个table变量中

create or replace type obj_type as object(id number,name varchar2(50 byte),birthday date);
 
create or replace type table_type as table of obj_type;
 
declare
  v_table table_type:=table_type();
begin
  v_table.extend;
  v_table(1):=obj_type(1,'lipiao',to_date('19970807','yyyymmdd'));
  v_table.extend;
  v_table(2):=obj_type(2,'cc',to_date('19951018','yyyymmdd'));
  for item in (select * from table(v_table)) loop
    dbms_output.put_line('id:'||item.id||',name:'||item.name||',birthday:'||item.birthday);
  end loop;
end;

这种方式需要每次先extend方法扩展一个object，然后给object赋值。

这种方法可以将table变量转换成游标。

下面这种方式可以自动扩展object，在19c版本测试可以使用，11g不行

declare
type obj_type is record(student_id number,student_name varchar2(20 byte),student_birthday date);
type table_type is table of obj_type index by binary_integer;
student_table table_type:=table_type();
student obj_type;
i number;
begin
    student_table.student_id:=100;student_table.student_name:='lipiao';student_table.student_birthday:=to_date('19970807','yyyymmdd');
student_table(1):=student;
student_table.student_id:=200;student_table.student_name:='cc';student_table.student_birthday:=to_date('19951018','yyyymmdd');
student_table(2):=student;
 
i:=student_talbe.first;
loop
    exit when i is null;
    dmbs_output.put_line(student_table(i).student_name);
    i:=student_table.next(i);
end loop;
end;

 

自定义类型在函数中的使用示例

----创建type
create type row_type_a as object(A varchar2(255), AA varchar2(255), AAA varchar2(255));
create type table_type_a as table of row_type_a;

create type row_type_b as object(B varchar2(255), BB varchar2(255), BBB varchar2(255));
create type table_type_b as table of row_type_b;

--创建管道函数
create or replace function fun1 
return table_type_a pipelined
as
   v row_type_a;
begin
    for myrow in (select A, AA, AAA from TEST_A) loop
      v := row_type_a(myrow.A, myrow.AA, myrow.AAA);
      pipe row (v);
    end loop;
   return;
end;

--调用管道函数
select * from table(fun1);

-- 创建2
create or replace function fun1_1 
return table_type_a
as
     v_test table_type_a := table_type_a();
begin
    for myrow in (select A, AA, AAA from TEST_A) loop
          v_test.extend();
      v_test(v_test.count) := row_type_a(myrow.A, myrow.AA, myrow.AAA);
    end loop;
   return v_test;
end;

-- 
select * from table(fun1_1);