在下面的例子中，我为每个pls_integer子类型写了一个to_str()函数和一个set()过程。功能和程序几乎相同，除了类型。
我如何可以消除在不放弃子类型提供的约束的情况下，为新的子类型写入另一个to_str()和set()的需要？
回到varchar2像
procedure set(list in varchar2,prefix in varchar2)
然后调用它
set(to_str(list),'foos:')
听起来不太好，我仍然需要为每个子类型提供to_str()。
我开放的各种不同的建议，因为我是Oracle新手，新的Oracle功能几乎每天都在我身上。
我正在运行11.2.0.1.0。
create table so1table (
id number,data varchar(20)
);
create or replace package so1 as
subtype foo_t is pls_integer range 0 .. 4 not null;
type foolist is table of foo_t;
procedure set(id_ in number,list in foolist default foolist(1));
subtype bar_t is pls_integer range 5 .. 10 not null;
type barlist is table of bar_t;
procedure set(id_ in number,list in barlist default barlist(5));
end;
/
show errors
create or replace package body so1 as
/* Do I have always to implement these very similar functions/procedures for
every single type ? */
function to_str(list in foolist) return varchar2 as
str varchar2(32767);
begin
for i in list.first .. list.last loop
str := str || ' ' || list(i);
end loop;
return str;
end;
function to_str(list in barlist) return varchar2 as
str varchar2(32767);
begin
for i in list.first .. list.last loop
str := str || ' ' || list(i);
end loop;
return str;
end;
procedure set(id_ in number,list in foolist default foolist(1)) as
values_ constant varchar2(32767) := 'foos:' || to_str(list);
begin
insert into so1table (id,data) values (id_,values_);
end;
procedure set(id_ in number,list in barlist default barlist(5)) as
values_ constant varchar2(32767) := 'bars:' || to_str(list);
begin
insert into so1table (id,values_);
end;
end;
/
show errors
begin
so1.set(1,so1.foolist(0,3));
so1.set(2,so1.barlist(5,7,10));
end;
/
sqlPLUS> select * from so1table;
ID DATA
---------- --------------------
1 foos: 0 3
2 bars: 5 7 10
create table so1table (
id number,data varchar(20)
);
create or replace type parent_type as object
(
v_number number,--Prefix probably belongs with a list,not an individual value.
--For simplicity,I'm not adding another level to the objects.
v_prefix varchar2(10)
) not instantiable not final;
/
create or replace type parentlist as table of parent_type;
/
create or replace type foo_type under parent_type
(
constructor function foo_type(v_number number) return self as result
);
/
--The data must be stored as a NUMBER,since ADTs don't support
--PL/sql specific data types. The type safety is enforced by the
--conversion in the constructor.
create or replace type body foo_type is
constructor function foo_type(v_number number) return self as result
as
subtype foo_subtype is pls_integer range 0 .. 4 not null;
new_number foo_subtype := v_number;
begin
self.v_number := new_number;
self.v_prefix := 'foos:';
return;
end;
end;
/
create or replace type foolist as table of foo_type;
/
create or replace type bar_type under parent_type
(
constructor function bar_type(v_number number) return self as result
);
/
create or replace type body bar_type is
constructor function bar_type(v_number number) return self as result
as
subtype bar_subtype is pls_integer range 5 .. 10 not null;
new_number bar_subtype := v_number;
begin
self.v_number := new_number;
self.v_prefix := 'bars:';
return;
end;
end;
/
create or replace type barlist as table of bar_type;
/
create or replace package so1 as
procedure set(id_ in number,list in parentlist);
end;
/
create or replace package body so1 as
function to_str(list in parentlist) return varchar2 as
v_value VARCHAR2(32767);
begin
for i in list.first .. list.last loop
if i = 1 then
v_value := list(i).v_prefix;
end if;
v_value := v_value || ' ' || list(i).v_number;
end loop;
return v_value;
end to_str;
procedure set(id_ in number,list in parentlist) as
values_ constant varchar2(32767) := to_str(list);
begin
insert into so1table (id,values_);
end set;
end so1;
/
begin
--You probably don't want to mix foos and bars,but it is allowed.
so1.set(1,parentlist(foo_type(0),foo_type(3)));
so1.set(2,parentlist(bar_type(5),bar_type(7),bar_type(10)));
--These would generate "ORA-06502: PL/sql: numeric or value error"
--so1.set(1,parentlist(foo_type(5)));
--so1.set(1,parentlist(bar_type(4)));
end;
/
select * from so1table;
