oracle - ¿Cómo eliminar la dependencia del subtipo?

plsql oracle11g

Question

En el siguiente ejemplo, he escrito una función to_str() y un procedimiento set() para cada subtipo pls_integer . Las funciones y procedimientos son casi idénticos excepto el tipo.

¿Cómo puedo eliminar la necesidad de escribir otro to_str() y set() para un nuevo subtipo sin renunciar a la restricción proporcionada por el subtipo?

Cayendo de nuevo a varchar2 como

procedure set(list in varchar2, prefix in varchar2)

y luego llamándolo como

set(to_str(list), ''foos:'')

no suena muy buena idea y todavía tengo que proporcionar to_str() para cada subtipo.

Estoy abierto a todo tipo de propuestas diferentes, ya que soy un novato en Oracle y las nuevas funciones de Oracle me sorprenden casi a diario.

Estoy corriendo 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, data) values (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

Answer 1

Es posible que esto no responda a su pregunta, pero ¿por qué no colocar los datos en una tabla regular, luego concatenarlos, como muestra, usando la función de agregación wm_concat ?

es decir,

> select * from myTable;
ID Category Value
--- --------- ------
1 foo 0
2 foo 3
3 bar 5
4 bar 7
5 bar 10
> select Category||''s: ''||replace(wm_concat(Value),'','','' '') Data
from myTable
group by Category;
Data
-------------
bars: 5 7 10
foos: 0 3

wm_concat es de tipo independiente, por lo que no es necesario que sobrecargue sus funciones. Además, hay otros methods que se pueden utilizar; El método de función analítica se ve bien, ¡pero no tengo 11g para probar!

( Editar de lo contrario, creo que puede lograr lo que está buscando con el modelo de objetos de Oracle; específicamente el polimorfismo. Sin embargo, esto está más allá de mí ... así que tal vez alguien más pueda hacerlo).

Answer 2

La siguiente respuesta es en realidad a cómo lo harías en postgresql (y plpgsql), y tampoco sé acerca de los subtipos de Oracle, pero asumo que son lo suficientemente similares como para que al menos te conduzcan a tu respuesta.

create function add (anynonarray,anynonarray) returning anynonarray
as ''begin return $1 + $2; end'';

Sé que arruiné la sintaxis, pero debería mostrar lo que quiero mostrar con ella, de todos modos.

La idea es que sustituirá "anynonarray" o cualquiera de las alternativas con el tipo de parámetro de la llamada. Una restricción es que todos los "anynonarray" en el ejemplo anterior serán del mismo tipo.

La documentación referida a esto como polimorfismo.

Answer 3

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, data) values (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;