仅当数据库高于sysdate时才删除的过程 [英] Procedure to delete only if database higher then sysdate

问题描述

目前，我有一个程序可以删除客户，这是包装体内的一个相当简单的程序.这是运行和删除客户的过程的代码:

Ive currently a procedure that deletes a customer a fairly simple procedure that's inside a package body. Here is the code for the procedure that runs and deletes the customer:

PROCEDURE remove_customer (customer_id VARCHAR2) IS
BEGIN
DELETE FROM order_line
WHERE  order_line.FK1_order_id in
(SELECT order_id FROM placed_order
 WHERE placed_order.FK1_customer_id = remove_customer.customer_id
);
DELETE FROM placed_order
WHERE placed_order.FK1_customer_id = remove_customer.customer_id;
DELETE FROM customer
WHERE customer.customer_id = remove_customer.customer_id;
total_customers := total_customers - 1;
END;

我想要的是仅在交货日期过去之后才删除该客户? 所以在上面的过程中会有一个if语句，我不确定如何以及如何添加它.

What i want is to only delete that customer if the delivery date has past? so there would be a if statement in the above procedure I'm just unsure how where and how to add it.

它将与

CREATE PROCEDURE remove_customertest (customer_id VARCHAR2) IS
BEGIN
IF placed_order.delivery_date < SYSDATE
THEN
DELETE FROM order_line
WHERE  order_line.FK1_order_id in
(SELECT order_id FROM placed_order
 WHERE placed_order.FK1_customer_id = remove_customer.customer_id
);
DELETE FROM placed_order
WHERE placed_order.FK1_customer_id = remove_customer.customer_id;
DELETE FROM customer
WHERE customer.customer_id = remove_customer.customer_id;
ELSE
DBMS_OUTPUT.PUT_LINE ('Customer currently has a order been delivered and cant be removed.');
END IF;
END;

有人对此有任何建议吗?或者我对吗?

Does any one have any advice on this or if I'm on the right lines?

感谢我是PL/SQL的新手

Thanks for the help I'm fairly new to PL/SQL

推荐答案

您可以在过程的开头添加以下条件-

this condition you can add in the starting of the procedure as follow -

PROCEDURE Remove_Customer(Customer_Id VARCHAR2) IS
  l_Order_Date DATE;
BEGIN
  BEGIN
    SELECT MAX(Delivery_Date)
      INTO l_Order_Date
      FROM Placed_Order
     WHERE Placed_Order.Fk1_Customer_Id = Customer_Id;
  EXCEPTION
    WHEN OTHERS THEN
      l_Order_Date := SYSDATE - 1;
  END;
  IF l_Order_Date < SYSDATE THEN
    DELETE FROM Order_Line
     WHERE Order_Line.Fk1_Order_Id IN
           (SELECT Order_Id
              FROM Placed_Order
             WHERE Placed_Order.Fk1_Customer_Id = Customer_Id);
    DELETE FROM Placed_Order
     WHERE Placed_Order.Fk1_Customer_Id = Customer_Id;
    DELETE FROM Customer WHERE Customer.Customer_Id = Customer_Id;      
    --Total_Customers := Total_Customers - 1; -- is it a global variable in the package?
  ELSE
    Dbms_Output.Put_Line('Customer currently has a order been delivered');
  END IF;
END;

这篇关于仅当数据库高于sysdate时才删除的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！