Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
Добрый день, уважаемые гуры.
Есть некая база. По ней частенько ищут разные программы, но каждая со своими алгоритмами. Сейчас пишу единую функцию по поиску по ней, но есть заковырка - как-нить можно в нее передать не 3-4 параметра, а, скажем, переменную типа ROWTYPE из той таблицы из которой хочу получить результат, предварительно заполнив имеющиеся у меня данные.
Идеальный вариант чтобы возвращать не одно поле одной записи, а таблицу строк. Использование временных таблиц нежелательно т.к. функция может вызываться как из оракла, так и Билдера и php.

Например, надо что-то типа
CREATE OR REPLACE FUNCTION Find_Policy (PolicyRec policy%rowtype)
       RETURN CodeListType IS
type CodeListType is table of policy%rowtype;
CodeList CodeListType :=CodeListType();
i number;
BEGIN
  i:=1;
  for FindRec in (select code from policy a
                   where a.statecode in (2,5) 
                       and a.family = PolicyRec.Family
                       and a.name = PolicyRec.name          
                       and a.otch = PolicyRec.otch         
                       and a.birthday = PolicyRec.birthday) loop
    CodeList.EXTEND;
    CodeList(CodeList.count):=FindRec.code;
  end loop;
  return cCode;
END;

Знаю, что это не работает, но нужно что-то типа этого.
Заранее спасибо.
29 апр 08, 10:36    [5606815]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
возвращение нескольких строк нужно для обнаружения факта дублирования записей (такое тоже возможно)
29 апр 08, 10:40    [5606853]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
Soft Wind
Member

Откуда:
Сообщений: 170
Передай VC2 с XML-ем. Проверка - парсинг на соответствие xsd (ну или без проверки).
Там можно как угодно с атрибутами извращаться.
Не самый шустрый способ, но работает и для интерактивного приложения в самый раз.
29 апр 08, 10:49    [5606917]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
dragonx
Member

Откуда: Vladivostok - Москва - ...
Сообщений: 564
refcursor?
29 апр 08, 10:51    [5606923]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
badmintonist
Member

Откуда:
Сообщений: 330
VoDiK
Знаю, что это не работает, но нужно что-то типа этого.
Тип создайте в базе (CREATE TYPE) и можете использовать более модную конструкцию BULK COLLECT вместо цикла и все должно получиться
29 апр 08, 11:04    [5607010]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
dragonx
refcursor?

оч сильно похоже на то. Попробую покурить в эту сторону
29 апр 08, 11:46    [5607368]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
badmintonist
Тип создайте в базе (CREATE TYPE) и можете использовать более модную конструкцию BULK COLLECT вместо цикла и все должно получиться

В том тои дело:
create or replace type "PolicyType" is table of policy%rowtype;
не комплится:( Ругается на ошибки!
29 апр 08, 11:48    [5607380]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
TiG
Member

Откуда:
Сообщений: 780
До десятки чтобы не возиться с типами уровня схемы можно действительно использовать refcursor-ы
В десятке типы уровня схемы объявлять необязательно, так что можно сделать так:
SQL> create table test_tbl (
  2    n number,
  3    s varchar2(30)
  4  )
  5  /

Table created.

SQL> create or replace package test_pkg is
  2  
  3  subtype tr_test_tbl is test_tbl%rowtype;
  4  
  5  type tt_test_tbl is table of tr_test_tbl; 
  6  
  7  function test_pipe(
  8    i_cnt number
  9  ) return tt_test_tbl pipelined;
 10  
 11  end test_pkg;
 12  /

Package created.

SQL> create or replace package body test_pkg is
  2  
  3  function test_pipe(
  4    i_cnt number
  5  ) return tt_test_tbl pipelined
  6  is
  7    l_row tr_test_tbl;
  8  begin
  9    for i in 1..nvl(i_cnt, 0) loop
 10      l_row.n := i;
 11      l_row.s := 'row '||i;
 12      pipe row (l_row);
 13    end loop;
 14  end test_pipe;
 15  
 16  end test_pkg;
 17  /

Package body created.

SQL> select * from table(test_pkg.test_pipe(3))
  2  /

         N S
---------- ------------------------------
         1 row 1
         2 row 2
         3 row 3

SQL> alter table test_tbl add d date
  2  /

Table altered.

SQL> select * from table(test_pkg.test_pipe(3))
  2  /

         N S                              D
---------- ------------------------------ ---------
         1 row 1
         2 row 2
         3 row 3

SQL> drop package test_pkg
  2  /

Package dropped.

SQL> drop table test_tbl purge
  2  /

Table dropped.
Очень удобно - остаются все плюсы использования конструкции %rowtype, если создавать типы уровня схемы пришлось бы их потом ручками изменять какждый раз при изменении структуры базовой таблицы.
29 апр 08, 11:54    [5607431]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
TiG
Member

Откуда:
Сообщений: 780
Если и в процедуре использовать подход (фетчим строки в переменные типа %rowtype), то в принципе можно добиться того, что после изменения структуры таблицы даже ничего доделывать не надо будет - все будет работать. В идеале. Если все аккуратненько сделать ;-)
29 апр 08, 11:59    [5607477]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
TiG
Если и в процедуре использовать подход (фетчим строки в переменные типа %rowtype), то в принципе можно добиться того, что после изменения структуры таблицы даже ничего доделывать не надо будет - все будет работать. В идеале. Если все аккуратненько сделать ;-)

Именно такая идея:) Надо чтобы по каким-то данным нашлась строка и, если все удастся, то потом и по ключу не надо будет стучаться в основную таблицу - все записи будут в результате работы функции:):):):):)
29 апр 08, 12:17    [5607637]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
TiG

SQL> create table test_tbl (
  2    n number,
  3    s varchar2(30)
  4  )
  5  /

Table created.

SQL> create or replace package test_pkg is
  2  
  3  subtype tr_test_tbl is test_tbl%rowtype;
  4  
  5  type tt_test_tbl is table of tr_test_tbl; 
  6  
  7  function test_pipe(
  8    i_cnt number
  9  ) return tt_test_tbl pipelined;
 10  
 11  end test_pkg;
 12  /

Package created.

SQL> create or replace package body test_pkg is
  2  
  3  function test_pipe(
  4    i_cnt number
  5  ) return tt_test_tbl pipelined
  6  is
  7    l_row tr_test_tbl;
  8  begin
  9    for i in 1..nvl(i_cnt, 0) loop
 10      l_row.n := i;
 11      l_row.s := 'row '||i;
 12      pipe row (l_row);
 13    end loop;
 14  end test_pipe;
 15  
 16  end test_pkg;
 17  /

Package body created.
Очень удобно - остаются все плюсы использования конструкции %rowtype, если создавать типы уровня схемы пришлось бы их потом ручками изменять какждый раз при изменении структуры базовой таблицы.

Все отлично. Именно то, что нужно, но есть мальнькое "но":
написал необходимую мне логику:
create or replace package body Policy_PKG is
  function FindInPolicy( PolRec policy%rowtype) return PolicyType pipelined;
end Policy_PKG;
Пытаюсь ее вызвать из PL/SQL:
declare 
 i integer;  
t policy%rowtype;
begin
  select count(*) into i from table(owner.policy_pkg.FindInPolicy (t));
end;
НО НЕ РАБОТАЕТ! Говорит, что неверный тип данных!
Пытался в пакете объявить subtype и в процедуре объявить переменную типа вот и вызвать уже так процу
declare 
 i integer;  
 t Policy_PKG.PolicyRowType;
begin
  select count(*) into i from table(policy_pkg.FindInPolicy (t));
end;
В итоге результат то же.
Что я делаю не так? Типы данных на мой взгляд те же.
30 апр 08, 13:46    [5613858]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
TiG
Member

Откуда:
Сообщений: 780
VoDiK
Все отлично. Именно то, что нужно, но есть мальнькое "но":
написал необходимую мне логику:
create or replace package body Policy_PKG is
  function FindInPolicy( PolRec policy%rowtype) return PolicyType pipelined;
end Policy_PKG;
Пытаюсь ее вызвать из PL/SQL:
declare 
 i integer;  
t policy%rowtype;
begin
  select count(*) into i from table(owner.policy_pkg.FindInPolicy (t));
end;
НО НЕ РАБОТАЕТ! Говорит, что неверный тип данных!
Пытался в пакете объявить subtype и в процедуре объявить переменную типа вот и вызвать уже так процу
declare 
 i integer;  
 t Policy_PKG.PolicyRowType;
begin
  select count(*) into i from table(policy_pkg.FindInPolicy (t));
end;
В итоге результат то же.
Что я делаю не так? Типы данных на мой взгляд те же.

Нее так нельзя, вы же пытаетесь в запросе использовать pl/sql-ный тип-запись. SQL-движок только с SQL-типами работает (объектные типы объявленные на уровне схемы к таковым относятся).
На самом деле даже при использовании этой 10ной фишки с созданием типов в пакете
subtype tr_test_tbl is test_tbl%rowtype;
type tt_test_tbl is table of tr_test_tbl;
оракл сам прозрачно для вас создает такие типы, эквивалентные вашим пакетным, и именует их SYS_PLSQL_%. И реально используются именно объектные типы, как и раньше.
Эти авто-создаваемые типы тоже можно использовать в коде, если использовать их реальные SYS_PLSQL_% имена. Но как сами понимаете не в этом же пакете ;)
30 апр 08, 14:00    [5613974]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
TiG

declare 
 i integer;  
t policy%rowtype;
begin
  select count(*) into i from table(owner.policy_pkg.FindInPolicy (t));
end;
declare 
 i integer;  
 t Policy_PKG.PolicyRowType;
begin
  select count(*) into i from table(policy_pkg.FindInPolicy (t));
end;
Нее так нельзя, вы же пытаетесь в запросе использовать pl/sql-ный тип-запись. SQL-движок только с SQL-типами работает (объектные типы объявленные на уровне схемы к таковым относятся).
На самом деле даже при использовании этой 10ной фишки с созданием типов в пакете
subtype tr_test_tbl is test_tbl%rowtype;
type tt_test_tbl is table of tr_test_tbl;
оракл сам прозрачно для вас создает такие типы, эквивалентные вашим пакетным, и именует их SYS_PLSQL_%. И реально используются именно объектные типы, как и раньше.
Эти авто-создаваемые типы тоже можно использовать в коде, если использовать их реальные SYS_PLSQL_% имена. Но как сами понимаете не в этом же пакете ;)


То есть, если я правильно понимаю, ничего такого мне сделать не получится? Межет это можно как-нить обойти?
30 апр 08, 15:10    [5614512]     Ответить | Цитировать Сообщить модератору
 Re: Использование ROWTYPE при вызове функции  [new]
VoDiK
Member

Откуда: Тула
Сообщений: 97
[url=http://]https://www.sql.ru/forum/actualthread.aspx?bid=3&tid=96888&hl=rowtype[/url]
эту ветку уже видел.
Но не уж-то все настолько запущено?!:(
30 апр 08, 15:19    [5614576]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить