无参数的求解过程 [英] Solving Procedure with no parameters

问题描述

嘿，大家在这里看看你们是否可以帮助我解决我遇到的过程问题.长话短说，我做了一张新桌子，叫做

Hey guys just here to see if you guys can help me solve this Procedure problem I am running into. Long story short I made a new table called

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

我想通过运行主要从下表中读取的过程来填充/更新此表:

And I want to populate/update this table by running a procedure that reads primarily from the following table:

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);

我使用另外两个称为Orders和Clients的表进行了几次联接，但这些联接是使用主键/FK进行的简单联接.

I do a couple of joins with two more tables called Orders and Clients but those are trivial joins using the Primary Key's/FK.

因此，此过程的目标是，在我运行该过程时，我想遍历订单详细信息，并要计算客户购买的产品的不同数量，总产品和总购买金额，并且我想更新如果新记录不在ClientHistoricalPurchases表中，则该记录具有新值，如果不存在，我想为其添加新记录.所以这是我写的，但是给我错误:

So the goal of this procedure is that when I run it I want to loop through order details and I want to calculate the distinct amount of products bought by a Client, the total products and the total purchase amount and I want to update an existing record with the new values if its in the new ClientHistoricalPurchases table if not I want to add a new record for it. So this is what I wrote but its giving me errors:

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:遇到符号；"当期待一个 以下内容:循环用符号循环"代替;". 继续.
错误(33,7):PLS-00103:遇到符号"JOIN" 当期望以下之一时:，;对于有 相交的减号在连接处开始合并

Error(27,4): PLS-00103: Encountered the symbol ";" when expecting one of the following: loop The symbol "loop" was substituted for ";" to continue.
Error(33,7): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: , ; for group having intersect minus order start union where connect

任何帮助都是值得赞赏的家伙.谢谢！

Any help is appreciated guys. Thanks!

推荐答案

除了已经给您的注释和答案之外，我相信您已经使程序过于复杂.您正在非常有步骤地进行操作，而不是按照应有的方式进行思考.您还可以在三个基本相同的查询(例如，相同的表，联接条件和谓词)中获得汇总列-您可以将它们全部组合在一起，以在单个查询中获得三个结果.

In addition to the comments and answers you've already been given, I believe you have massively overcomplicated your procedure. You're doing things very procedurally, rather than thinking in sets as you should be. You are also getting the aggregated columns in three queries that are essentially identical (e.g. same tables, join conditions and predicates) - you could combine them all to get the three results in a single query.

如果该客户的行尚不存在，您似乎正在尝试将其插入到clienthistoricalpurchases表中，否则您将更新该行.那立即向我尖叫合并声明".

It looks like you're trying to insert into the clienthistoricalpurchases table if a row doesn't already exist for that client, otherwise you update the row. That immediately screams "MERGE statement" to me.

结合所有这些，我认为您当前的过程应该只包含一个合并语句:

Combining all that, I think your current procedure should contain just a single merge statement:

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;

但是，我对您当前的逻辑和/或数据模型有些担心.

However, I have some concerns over your current logic and/or data model.

您似乎希望每个clientid最多有一行显示在clienthistoricalpurchases中.如果一个clientid有两个或两个以上不同的订单怎么办?当前，您将覆盖任何现有行.

It seems like you're expecting at most one row per clientid to appear in clienthistoricalpurchases. What if a clientid has two or more different orders? Currently you would overwrite any existing row.

还，您是否真的想在每次运行订单时在所有订单上应用此逻辑?

Also, do you really want to apply this logic across all orders every single time it gets run?

这篇关于无参数的求解过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！