每次我运行declare语句时，它都会说它遇到了文件结束符号

Question

SET SERVEROUTPUT ON;
--VARIABLE DECLARATION
DECLARE
c_name VARCHAR(30); (Here it gives the end to file error)
p_purchaed VARCHAR2(30);
BEGIN
CURSOR( c_CustProd IS SELECT (C.cust_fName ||',' || C.cust_lName) AS "CUSTOMER", P.product_name (and then the select statement it says it was expecting a not null statment)
FROM Billing B
JOIN Product_Billing PB ON B.bill_ID = PB.bill_ID,
JOIN Customer C on B.cust_ID = C.cust_ID,
JOIN Product P on PB.product_ID = P.product_ID,
WHERE P.product_price > 10000
ORDER BY P.product_name DESC, P.product_price DESC);

OPEN c_CustProd;
LOOP
FETCH c_CustProd into c_name, p_purchaed
DBMS_OUTPUT.PUT_LINE('CUSTOMER:    '||c_name);
DBMS_OUTPUT.PUT_LINE('PRODUCT:     '||p_purchaed );
DBMS_OUTPUT.PUT_LINE('-------------------------------');

END LOOP
CLOSE c_CustProd;
END;

我对神谕还是个新手。但我只需要显示一个客户的名字和他们购买的东西。我已经在代码的前面插入了所有的变量，一切都正常，但这句话和我不知道所有的错误在哪里

Answer 1

相当多的错误，我将尝试全部提及：

• 使用varchar2，而不是varchar
• 游标应该在declare节中声明，而不是在begin之后
• 从光标中删除多余的括号
• 在Oracle中命名任何东西时不要使用双引号(这不是错误，但……也不是好处。更多的痛苦，稍后(
• 你从未退出循环
• 代码中的反斜杠在做什么
• 语句必须用分号;终止

将此代码与您的代码进行比较。

SQL> DECLARE
2    c_name VARCHAR2(30);
3    p_purchaed VARCHAR2(30);
4
5    CURSOR c_CustProd IS
6      SELECT C.cust_fName ||',' || C.cust_lName AS CUSTOMER,
7             P.product_name
8      FROM Billing B
9      JOIN Product_Billing PB ON B.bill_ID = PB.bill_ID
10      JOIN Customer C on B.cust_ID = C.cust_ID
11      JOIN Product P on PB.product_ID = P.product_ID
12      WHERE P.product_price > 10000
13      ORDER BY P.product_name DESC, P.product_price DESC;
14  BEGIN
15    OPEN c_CustProd;
16    LOOP
17      FETCH c_CustProd into c_name, p_purchaed;
18      EXIT when c_custprod%notfound;
19
20      DBMS_OUTPUT.PUT_LINE('CUSTOMER:    '||c_name);
21      DBMS_OUTPUT.PUT_LINE('PRODUCT:     '||p_purchaed );
22      DBMS_OUTPUT.PUT_LINE('-------------------------------');
23    END LOOP;
24    CLOSE c_CustProd;
25  END;
26  /
PL/SQL procedure successfully completed.
SQL>

Answer 2

大多数时候，您不需要显式命名的游标或变量，更紧凑的语法更可取：

begin
for r in (
select c.cust_fname || ',' || c.cust_lname as customer_name
, p.product_name as product_purchased
from   billing b
join product_billing pb on pb.bill_id = b.bill_id
join customer c on c.cust_id = b.cust_id
join product p on p.product_id = pb.product_id
where  p.product_price > 10000
order  by p.product_name  desc, p.product_price desc
)
loop
dbms_output.put_line('CUSTOMER:    ' || r.customer_name);
dbms_output.put_line('PRODUCT:     ' || r.product_purchased);
dbms_output.put_line('--------------------------------');
end loop;
end;