Блог

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

Я не смог заставить что-либо работать, я думаю, это как-то связано с тем, что Oracle не нравится ALTER TABLE запускать изнутри процедуры, но я не знаю, и я не знаю достаточно, чтобы направлять свои попытки куда-либо еще.

Это моя попытка

 CREATE or REPLACE PROCEDURE ProductLineSale as
BEGIN
    DECLARE
       NewSalePrice NUMBER(6,2):=0; 
    EXECUTE IMMEDIATE 'alter table ' || Product || 'add or replace column' || 'SalePrice NUMBER(6,2);'
    FOR p in (SELECT ProductStandardPrice FROM Product
            group by ProductStandardPrice)
        LOOP
            CASE WHEN p.ProductStandardPrice>=400 THEN NewSalePrice:=.9*price
                 WHEN p.ProductStandardPrice<400 THEN NewSalePrice:=.85*price
            INSERT INTO Product(SalePrice)
            VALUES(NewSalePrice)
        END LOOP;
END ProductLineSale
  

Product — это буквальное имя таблицы Product в моей базе данных. SalePrice — это то, что я хотел бы, чтобы новый столбец был назван.

SQLDeveloper не будет компилировать процедуру. Ошибка, которую я получаю, также довольно загадочна:

Ошибка (2,10): PLS-00103: обнаружен символ «=» при ожидании одного из следующих: постоянная таблица исключений длинная двойная ссылка символ время интервал метки времени дата двоичный национальный символ nchar.

Существует множество ошибок… Те, которые выскакивают у меня при первом проходе.

• Требование не имеет смысла. Добавление столбца в процедуру не имеет смысла. Вы создаете процедуру, потому что хотите, чтобы код можно было использовать повторно. Добавление столбца может быть выполнено только один раз, следовательно, оно по определению не может быть повторно использовано.
• Процедура должна быть скомпилирована, прежде чем ее можно будет выполнить. Если есть ссылка на столбец, который не существует, процедура не сможет скомпилироваться. Таким образом, если вы хотите добавить столбец в таблицу с использованием динамического SQL, все последующие ссылки на столбец (т. Е. Ваш insert оператор) также должны использовать динамический SQL.
• Ваш оператор DDL неверен. Там нет add or replace предложения, это alter table product add SalePrice NUMBER(6,2) . Обратите внимание, что когда вы создаете свою строку, вы также должны убедиться, что между предложением add и именем столбца есть пробел SalesPrice — это потребуется для одной из двух строк, которые вы объединяете вместе.
• Не имеет смысла иметь declare where вы делаете. Вы можете объявлять переменные между as и begin одной строкой выше. Вам разрешено создавать вложенный блок PL / SQL с declare помощью, но тогда вам потребуется сопоставление begin , end которого у вас нет.
• Если вы собираетесь использовать case оператор в PL / SQL, вам понадобится end case . Вам также потребуется точка с запятой ; после каждого выражения.
• В вашем insert заявлении также отсутствует точка с запятой.
• По логике вещей, мне трудно представить, что вы действительно хотите иметь insert здесь. Не имеет логического смысла создавать кучу новых строк в таблице при добавлении нового столбца. Я бы предположил, что вы хотите получить update значение нового столбца в существующих строках. Что, по-видимому, требует, чтобы ваш курсор выбирал столбцы первичного ключа и потенциально изменял, группируете ли вы и по чему.
• Product и price используются как локальные переменные в execute immediate инструкции и в case инструкции, но не определены. Я предполагаю, что вы просто хотите жестко запрограммировать имя таблицы, которую вы изменяете, и это price должно ссылаться на имя столбца в таблице, который вам нужно выбрать курсором, но я не уверен.

Это case утверждение синтаксически допустимо (или было бы, если price бы разрешало что-то действительное). Многие другие исправления менее очевидны по причинам, которые я описал выше.

 case when p.ProductStandardPrice>=400 
     then NewSalePrice:=.9*price;
     when p.ProductStandardPrice<400 
     THEN NewSalePrice:=.85*price;
  end case;
  

Если бы я размышлял о том, чего вы на самом деле хотите (учитывая, что это домашнее задание с требованиями, которые на самом деле не имеют смысла), я бы предположил что-то вроде

 CREATE or REPLACE PROCEDURE ProductLineSale 
as
begin
  execute immediate 'alter table Product add SalePrice NUMBER(6,2)';
  execute immediate 'update product ' ||
                    '   set SalePrice = (case when ProductStandardPrice >= 400 ' ||
                    '                         then 0.9 * Price ' ||
                    '                         else 0.85 * Price ' ||
                    '                      end) ';
end ProductLineSale;
  

Если вы собираетесь использовать динамический SQL, почти всегда имеет смысл объявить локальную переменную, создать инструкцию SQL в этой переменной, а затем выполнить ее, чтобы вы могли отлаживать вещи, распечатав инструкцию, которую вы создали для ее отладки.