Добро пожаловать, Гость
Логин: Пароль: Запомнить меня
Теоретические аспекты и практические реализации создания, внедрения и использования баз данных, СУБД, хранилищ.
  • Страница:
  • 1

ТЕМА: Вызов хранимой функции в Oracle через select

Вызов хранимой функции в Oracle через select 18 июль 2011 06:57 #2414

  • Joker
  • Joker аватар Автор темы
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 46
  • Спасибо получено: 0
Помогите, кто знаком с такой проблемой....
Сервер Oracle 8i
имеется хранимая функция след. вида:
CREATE OR REPLACE FUNCTION INET.INET_NEWORDER(IDCLIENTX IN NUMBER, DATEFROMX IN DATE, ORDERNOX IN VARCHAR2) RETURN NUMBER

IS

   IDORDERSX NUMBER;

BEGIN

  SELECT COUNT(*) INTO IDORDERSX FROM INET.ORDERS WHERE (INET.ORDERS.ORDERNO=ORDERNOX);

  IF (IDORDERSX = 0) THEN

  BEGIN

    SELECT (NVL(MAX(IDORDERS),0)+1) INTO IDORDERSX FROM INET.ORDERS;

    INSERT INTO INET.ORDERS (IDORDERS, DATEFROM, IDCLIENT, ORDERNO) VALUES

       (IDORDERSX, DATEFROMX, IDCLIENTX, ORDERNOX);

    COMMIT;

    RETURN IDORDERSX;

    EXCEPTION

      WHEN OTHERS THEN

      BEGIN

        ROLLBACK;

         RETURN 0;

      END;

  END;

  ELSE

    RETURN -1;  

  END IF;

END INET_NEWORDER;
Она идеально запускается и работает следующим блоком:
begin

  dbms_output.put_line(TO_CHAR (inet_neworder(100, to_date(23112001,'dd.mm.yyyy'), '453543a54')));

end;
Однако ничего не возвращает (NULL) при использовании в операторе select:
select inet.inet_neworder(100, to_date(23112001,'dd.mm.yyyy'), '453543a54') id from dual;
хотя пустое значение логикой не предусмотрено (можете оценить код).

При этом, если в хранимой функции удалить строки INSERT....COMMIT, то и вызов с помощью select отрабатывает нормально.

Где я оступился?

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: Вызов хранимой функции в Oracle через select 18 июль 2011 07:06 #2415

Похоже тебе надо прописать в функции прагму:
что-то типа
PRAGMA RESTRICT_REFERENCES(...), где именно прописывают и какие параметры передавать не помню, посмотри в документации.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: Вызов хранимой функции в Oracle через select 18 июль 2011 07:07 #2416

  • Gubin
  • Gubin аватар
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 42
  • Спасибо получено: 0
Была у меня подобная ерунда.
Проблема в том, что в функции стояла конструкция SELECT ... INTO, которая не возвращала ничего. Естественно по всем канонам должен произойти эксепшн NO_DATA_FOUND. Он видать и происходил, но в клиентское приложение ошибка не возвращалась, а возвращался NULL (так, как будто функция вертула NULL). Но у тебя есть блок обработки исключений, тогда почему?

Далее, если это функция, которая предполагает вызовов SQL-ом (а не PL/SQL), то она не может содержать DML операций (в твоем случае INSERT, COMMIT, ROLLBACK). Такие функции могут быть валидными, если ты укажешь прагму PRAGMA AUTONOMOUS_TRANSACTION. То есть функция становится Автономной Транзакцией. (Одно из применений, которое мне очень понравилось - если необходимо НЕ завершать/продолжать транзакцию в которой есть DDL операции. Они в этом случае помещаются в автономную процедуру).
В догонку к сказанному: уверен что первая DML операция INSERT вызавает исключение типа: Не могу отработать DML опрацию, если меня вызвали СЕЛЕКТОМ. По идее далее прыгаем в блок исключений и возвращаем 0. А почемуже тогда NULL. Фак, не знаю! Может это такой хитрый эксепш, который не отлавливается? И оракл возвращает NULL.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: Вызов хранимой функции в Oracle через select 18 июль 2011 07:08 #2417

  • Dior
  • Dior аватар
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 41
  • Спасибо получено: 0
Есть такое понятие как уровень строгости функции, использующихся в SQL запросах. Обязательным уровнем строгости для всех функций является уровень WNDS, который гласит: Функция не имеет права модифицировать
таблицы базы данных(при помощи операторов DML).
DML, как известно это insert, update, delete.
Вот вам и причина.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: Вызов хранимой функции в Oracle через select 18 июль 2011 07:09 #2418

Неправда :-)
Внутри блоков PL/SQL поддерживаются команды SQL, такие как SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, SAVEPOINT. Если бы это было не так то и вызов функции через dbms_output.put_line приводил бы к ошибке. А вот внутри select'а использование commit и rollback недопустимо.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: Вызов хранимой функции в Oracle через select 18 июль 2011 07:10 #2419

  • Dior
  • Dior аватар
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 41
  • Спасибо получено: 0
Для тех кто не понял- обьясняю еще раз.

Функцию можно вызывать:

1) непосредственно в PL/SQL блоке
cnt_ord NUMBER(16);
begin
cnt_ord:= my_function(...)
end;

2) в SQL запросе - select my_function(...) from ....

На использование функий в SQL запросах(т.е во втором случае) накладываются дополнительные ограничения.
В частности уровень строгости WNPS(см выше)

Приведённые вами пример:"вызов функции через dbms_output.put_line" - абсолютно некорректный:

1) put_line - является процедурой
procedure put_line(a varchar2);
pragma restrict_references(put_line,WNDS,RNDS);
procedure put_line(a number);
pragma restrict_references(put_line,WNDS,RNDS);

и не может быть использована в SQL запросе
2)
Вызов dbms_output.put_line может производится только в блоке PL/SQL(анонимном или именованом).

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

  • Страница:
  • 1