Oracle — Как создать процедуру с ‘и’, для которой не требуются параметры

#sql #oracle #stored-procedures #plsql

#sql #Oracle #хранимые процедуры #plsql

Вопрос:

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

 CREATE OR REPLACE PROCEDURE NoNullRatingsForFuture(IN_GAME NUMBER) AS
BEGIN

  UPDATE GAMES
     SET rating_id = 10
   WHERE game_id = IN_GAME;

END NoNullRatingsForFuture;
  

Пример использования:

 EXECUTE NoNullRatingsForFuture(7);
  

Однако я хочу заставить процедуру сканировать всю таблицу и обновлять ее, вот так:

 CREATE OR REPLACE PROCEDURE NoNullRatingsForFuture AS
BEGIN

   UPDATE GAMES
      SET rating_id = 10
    WHERE game_id = game_id 
      AND rating_id = NULL 
      AND release_date > SYSDATE;

END NoNullRatingsForFuture;
  

Пример использования:

 EXECUTE NoNullRatingsForFuture;
  

По сути, если rating значение равно нулю, а дата выше текущей даты, измените rating_id значение на 10. Кроме того, release_date сохраняется в dd-month-yy формате, если это поможет.

Эта процедура отлично компилируется, и я могу нормально выполнить, но rating_id по-прежнему имеет значение null. Что я делаю не так?

Комментарии:

1. По какой-либо конкретной причине вы сохраняете дату выпуска в виде строки, а не в виде DATE ? Ваш вопрос показывает лишь одну из ряда тонких проблем, которые могут возникнуть, когда даты не сохраняются как даты.

Ответ №1:

Использовать:

 CREATE OR REPLACE PROCEDURE NoNullRatingsForFuture AS
BEGIN

  UPDATE GAMES
     SET rating_id = 10
   WHERE rating_id IS NULL 
     AND TO_DATE(release_date, 'DD-MONTH-YY') > SYSDATE;

END NoNullRatingsForFuture;
  

Пара моментов:

  1. NULL не является значением — это заполнитель для отсутствия такового. Вам нужно использовать IS NULL и IS NOT NULL , чтобы найти такие экземпляры, чтобы обращаться с ними соответствующим образом
  2. release_date Следует сохранить как ДАТУ, чтобы сравнить с SYSDATE. Тип данных Oracle DATE включает время. В противном случае вам нужно использовать TO_DATE для таких столбцов (которые не будут поддерживать индекс, если он существует в release_date ), чтобы преобразовать значение в ДАТУ.
  3. Сканирование таблицы неэффективно — возможно, WHERE game_id = game_id это будет оптимизировано вне уравнения, но я не рекомендую эту практику. Аналогично для WHERE 1 = 1 , если только не использовать ее в динамическом SQL для упрощения объединения дополнительных условий.

Комментарии:

1. Повторите пункт 3 — Oracle всегда будет оптимизировать «безоперационные» сравнения, подобные 1=1 , и game_id=game_id эквивалентно game_id IS NOT NULL и будет оптимизировано как таковое.