Блог

У меня есть набор таблиц с разными типами данных для столбцов, и мне нужно консолидировать способ извлечения данных. Я подумал, что использование функции было бы хорошей идеей, но я не знаю, как определить одну функцию, имеющую разные типы возвращаемых данных.

Например, как определить эту функцию, чтобы иметь возможность использовать разные определения для tabletype.

 CREATE OR REPLACE FUNCTION retrieve_info(field_id in integer)
RETURN pintegertypetable -- <-- how to change this to return a more generic record built dynamically in the code below?
AS
  r pintegertypetable := pintegertypetable ();
BEGIN
  r.extend;
  r(i) := pintegertypetable (someinteger);
  return r;
END;
  

Возможно ли это?. Есть ли лучший способ справиться с этой проблемой: разные столбцы, изначально сохраненные во множестве устаревших таблиц, и учитывая, что каждый столбец имеет разные типы данных, каким образом мы можем получить самую последнюю информацию, сохраняя исходные типы данных, без жесткого кодирования представлений, не сохраняя все в varchar2 и не приводя снова в клиентский код?

Вы можете реализовать это, используя слабо типизированный курсор Ref в качестве возвращаемого типа. Это особенно легко реализовать из клиентского интерфейса с использованием JDBC, поскольку возвращаемый тип курсора может быть пошаговым, как и любой результат запроса, а метаданные могут быть запрошены из ResultSet.getMetadata(). Вот пример:

 CREATE OR REPLACE PROCEDURE retrieve_info(field_id in integer, p_cursor in out sys_refcursor)
AS
BEGIN
  open p_cursor for 'select * from emp';
END;
  

Запрос в кавычках может быть любым, возвращающим любой тип для любого количества столбцов.

Используя ответ Datajam Ltd, это может быть полезно для кого-то еще:

 drop table xxx1;
drop table xxx2;
drop table xxx3;
drop table xxx4;

create table xxx1(val integer);
create table xxx2(val date);
create table xxx3(val number);
create table xxx4(val varchar2(100));

insert into xxx1 (val) select rownum from all_objects where rownum <= 1;
insert into xxx2 (val) select sysdate rownum from all_objects where rownum <= 2;
insert into xxx3 (val) select 12.345 rownum from all_objects where rownum <= 3;
insert into xxx4 (val) select 'test'||rownum from all_objects where rownum <= 4;

CREATE OR REPLACE PROCEDURE retrieve_info(p_cursor in out sys_refcursor, tabname in varchar2) 
AS 
BEGIN 
    open p_cursor for 'select val from ' || tabname; 
END; 
/



-- CLIENT CODE IN PLSQL:

drop table logtable;
create table logtable(x varchar2(1000));

declare
     v_rc sys_refcursor;
     b varchar2(1000);
begin
     retrieve_info(v_rc, 'xxx2'); -- here you can parameterize your call     
     loop
          fetch v_rc into b;
              exit when v_rc%notfound;
              dbms_output.put_line(b);
              insert into logtable(x) values(b);
      end loop;
end;
/

select * from logtable;
  

Если вы заинтересованы в преобразовании отдельных столбцов в строки, самый простой способ — написать пакет с перегруженными функциями. Каждая функция имеет одно и то же имя, меняется только сигнатура входного параметра (ов):

 SQL> create or replace package str_util
  2  as
  3      function s (p in varchar2) return varchar2;
  4      function s (p in number) return varchar2;
  5      function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2;
  6  end;
  7  /

Package created.

SQL>
  

Очевидно, что это упрощенная реализация. Для работы вам может потребоваться некоторая обработка ошибок, маски числового формата, дополнительные типы данных и т.д.

 SQL> create or replace package body str_util
  2  as
  3      function s (p in varchar2) return varchar2
  4      is
  5      begin
  6          return p;
  7      end s;
  8      function s (p in number) return varchar2
  9      is
 10      begin
 11          return to_char(p);
 12      end s;
 13      function s (p in date, fmt in varchar2 := 'dd-mon-yyyy') return varchar2
 14      is
 15      begin
 16          return to_char(p, fmt);
 17      end s;
 18  end;
 19  /

Package body created.

SQL>
  

В вводящем в заблуждение запросе я показываю типы данных трех столбцов таблицы:

 SQL> select dump(id) id_is_number
  2         , dump(col2) col2_is_varchar
  3         , dump(col3) col3_is_date
  4  from big_table
  5  where rownum = 1
  6  /

ID_IS_NUMBER
------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------
Typ=2 Len=3: 194,5,37
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=12 Len=7: 120,107,10,15,11,10,14


SQL>
  

В этой версии запроса я вызываю упакованную функцию и получаю обратно три строки:

 SQL> select dump(str_util.s(id)) id_is_number
  2         , dump(str_util.s(col2)) col2_is_varchar
  3         , dump(str_util.s(col3)) col3_is_date
  4  from big_table
  5  where rownum = 1
  6  /

ID_IS_NUMBER
------------------------------------------------------------------
COL2_IS_VARCHAR
------------------------------------------------------------------
COL3_IS_DATE
------------------------------------------------------------------
Typ=1 Len=3: 52,51,54
Typ=1 Len=11: 73,95,65,82,71,85,77,69,78,84,49
Typ=1 Len=11: 49,53,45,111,99,116,45,50,48,48,55


SQL>