Процедура решения без параметров

Question

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

Create Table ClientHistoricalPurchases(
ClientID varchar2(6) constraint clientidhistorical references Clients,
DistinctProducts number (9),
TotalProducts number(9), 
TotalCost number (9,2),
Primary Key (ClientID));

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

create table OrderDetails(
OrderID varchar2(6) CONSTRAINT orddetpk PRIMARY KEY,
ProductID varchar2(6) CONSTRAINT prdfk REFERENCES Products ,
UnitPrice number(10,2),
Quantity number(4),
Discount number(3),
ShippingDate date);

Я делаю пару объединений с еще двумя таблицами, называемыми Заказами и Клиентами, но это тривиальные объединения с использованием первичного ключа /FK.

Таким образом, цель этой процедуры заключается в том, что при ее запуске я хочу просмотреть детали заказа и рассчитать определенное количество продуктов, купленных Клиентом, общую сумму продуктов и общую сумму покупки, а также обновить существующую запись. с новыми значениями, если это в новой таблице ClientHistoricalPurchases, если нет, я хочу добавить новую запись для него. Это то, что я написал, но это дает мне ошибки:

Create or Replace Procedure Update_ClientHistPurch as

Cursor C1 is 
Select orderid, orders.clientid, productid, unitprice, quantity, discount
from orderdetails
Inner join orders on orderdetails.orderid = orders.clientid
for update of TotalCost;

PurchaseRow c1%RowType;
DistinctProducts orderdetails.quantity%type;
TotalProducts orderdetails.quantity%type;
ProposedNewBalance orderdetails.unitprice%type;

Begin
Begin
Begin
Begin
Open C1;
Fetch c1 into PurchaseRow;

While c1% Found Loop 
Select count(distinct productid)
into DistinctProducts
from orderdetails
Inner join orders on orderdetails.orderid = orders.orderid
Inner join clients on orders.clientid = clients.clientid
where clients.clientid = purchaserow.clientid;
end;

Select count(ProductID)
into TotalProducts
from orderdetails
Inner join orders on orderdetails.orderid = orders.orderid
Inner join clients on orders.clientid = clients.clientid
where clients.clientid = purchaserow.clientid;
end;

Select sum((unitprice * quantity) - discount)
into ProposedNewBalance
from orderdetails
Inner join orders on orderdetails.orderid = orders.orderid
Inner join clients on orders.clientid = clients.clientid
where clients.clientid = purchaserow.clientid;
end;

If purchaserow.clientid not in ClientHistoricalpurchases.clientid then
insert into ClientHistoricalPurchases values    (purchaserow.clientid,DistinctProducts, TotalProducts, ProposedNewBalance);
End if;

If purchaserow.clientid in ClientHistoricalPurchases.clientid then
Update Clienthistoricalpurchases
set clienthistoricalpurchases.distinctproducts = distinctproducts,    clienthistoricalpurchases.totalproducts = totalproducts,    clienthistoricalpurchases.totalcost = ProposedNewBalance
    where purchaserow.clientid = clienthistoricalpurchases.clientid;
end if;
end loop;
end;

Ошибки следующие:

Ошибка (27,4): PLS-00103: обнаружен символ ";" когда ожидается одно из следующего: loop Символ "loop" был заменен на ";" продолжать.
Ошибка (33,7): PLS-00103: Обнаружен символ "ПРИСОЕДИНЯЙТЕСЬ" при ожидании одного из следующего:,; для группы, имеющей пересечение минус порядок, начало объединения, где соединяются

Любая помощь приветствуется, ребята. Спасибо!

Answer 1

В дополнение к комментариям и ответам, которые вы уже дали, я полагаю, что вы значительно усложнили свою процедуру. Вы делаете что-то очень процедурно, а не думаете, как следует. Вы также получаете агрегированные столбцы в трех одинаковых запросах (например, одинаковые таблицы, условия соединения и предикаты) - вы можете объединить их все, чтобы получить три результата в одном запросе.

Похоже, вы пытаетесь вставить в таблицу clienthistoricalpurchases, если строка для этого клиента еще не существует, иначе вы обновите строку. Это сразу кричит мне "СЛОВО МЕРЖЕ".

Объединяя все это, я думаю, что ваша текущая процедура должна содержать только одно выражение слияния:

MERGE INTO clienthistoricalpurchases tgt
  USING (SELECT clients.client_id,
                COUNT(DISTINCT od.productid) distinct_products,
                COUNT(od.productid) total_products,
                SUM((od.unitprice * od.quantity) - od.discount) proposed_new_balance
         FROM   orderdetails od
         INNER  JOIN orders
         ON     orderdetails.orderid = orders.orderid
         INNER  JOIN clients
         ON     orders.clientid = clients.clientid
         GROUP BY clients.client_id) src
  ON (tgt.clientid = src.client_id)
WHEN NOT MATCHED THEN
  INSERT (tgt.clientid,
          tgt.distinctproducts,
          tgt.totalproducts,
          tgt.totalcost) 
  VALUES (src.clientid,
          src.distinct_products,
          src.total_products,
          src.proposed_new_balance)
WHEN MATCHED THEN
  UPDATE SET tgt.distinctproducts = src.distinct_products,
             tgt.totalproducts = src.total_products,
             tgt.totalcost = src.proposed_new_balance;

Тем не менее, у меня есть некоторые опасения по поводу вашей текущей логики и / или модели данных.

Похоже, вы ожидаете, что в клиентских покупках появится не более одного ряда на каждого клиента. Что если у клиента два или более разных заказа? В настоящее время вы перезаписали бы любую существующую строку.

Кроме того, вы действительно хотите применять эту логику ко всем заказам каждый раз, когда он запускается?

Answer 2

Строка 28 вашего кода, первая END что следует WHILE, должно быть END LOOP