Oracle自定义类型-蒲公英云

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);