Oracle PL/SQL多表联查游标代码报语法错误，该如何解决？

问题定位 #

• 游标声明语法错误：PL/SQL不支持同时声明多个游标对应多表的写法，关联查询仅需声明1个游标即可，且游标声明结束后需添加分号结尾
• JOIN关联语法错误：invoice coin_id缺少小数点，应为invoice.coin_id
• 变量匹配错误：声明的变量名（c_id/coin_id1/coin_price/delivery）和FETCH时使用的变量（customer_id/coin_id/price/delivery_date）完全不匹配，且使用了未声明的变量
• OPEN语法错误：不支持同时打开多个游标，仅需打开声明的单个游标即可
• FETCH语法错误：正确语法为FETCH 游标名 INTO 变量1, 变量2...，不能拆分多段写into
• 拼写错误：游标判断时的i_invloice为拼写错误，应为i_invoice；FETCH用到的c_customers为未定义对象
• 日期格式风险：直接使用日期字符串依赖数据库NLS日期格式配置，建议用TO_DATE显式转换避免环境不同导致报错

修复后的完整代码 #

DECLARE
   c_id customer.customer_name%type;
   coin_id1 invoice.coin_id%type;
   coin_price coin.price%type;
   delivery  invoice.delivery_date%type;
   -- 关联查询仅需声明一个游标，避免和表名重名
   CURSOR cur_invoice_info IS
      SELECT customer_name, coin_id, price, delivery_date
      FROM invoice
      INNER JOIN customer on invoice.customer_id = customer.customer_id
      INNER JOIN coin on invoice.coin_id = coin.coin_id
      WHERE delivery_date BETWEEN TO_DATE('18 MAY 2021', 'DD MON YYYY') AND TO_DATE('20 MAY 2021', 'DD MON YYYY');
BEGIN
   OPEN cur_invoice_info;
   LOOP
      -- 正确FETCH写法：游标名后直接跟所有接收变量
      FETCH cur_invoice_info INTO c_id, coin_id1, coin_price, delivery;
      EXIT WHEN cur_invoice_info%notfound;
      dbms_output.put_line(c_id || ' ' || coin_id1 || ' ' || coin_price || ' ' || delivery);
   END LOOP;
   CLOSE cur_invoice_info;
END;
/

内容的提问来源于stack exchange，提问作者taahir mia