sql - 错误 PLS-00103 : Encountered the symbol "DECLARE"?

Question

我有一个程序会出现错误“PL/SQL:语句被忽略”。这条消息有点含糊，我不明白为什么我的程序不能编译。所有程序应该做的是检查客户的交货日期是否小于 SYSDATE，如果它被删除，如果不是则打印“客户不能被删除”。

程序代码在这里:

    CREATE PROCEDURE remove_customer (customer_id VARCHAR2) IS
declare
   ordersCount pls_integer;
BEGIN
   select count(*) into ordersCount 
   from placed_orders 
   where fk1_customer_id = remove_customer.customer_id
   and delivery_date < sysdate;
if ordersCount = 0 then
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;
total_customers := total_customers - 1;
ELSE
DBMS_OUTPUT.PUT_LINE 'Customer currently has a order been delivered';
END IF;
END;

错误消息指定 PLS-00103:遇到符号“DECLARE”

感谢您的任何建议。

Answer 1

这一行:

IF placed_order.delivery_date < SYSDATE

没有多大意义 - 您不能使用这样的列(想一想:placed_order 中的哪些行应该与 SYSDATE 进行比较？一个？全部？)。

如果你想检查这个客户是否有已经交付的 cargo ，你需要一个额外的 SELECT:

 CREATE PROCEDURE remove_customer (customer_id VARCHAR2) IS       
  ordersCount pls_integer;
 begin
   select count(*) into ordersCount 
   from placed_orders 
   where fk1_customer_id = remove_customer.customer_id
   and delivery_date < sysdate;

   if ordersCount = 0 then
    -- your code for deleting the customer here
   else
    -- raise error, show message, ...
   end if;