如何在Oracle SQL中修复此过程?

Question

CREATE OR REPLACE PROCEDURE ORDER_INFO (
I_ORDER_NUM in ORDERS.ORDER_NUM%TYPE)
AS
I_ORDER_DATE  ORDERS.ORDER_DATE%TYPE;
I_CUSTOMER_NUM ORDERS.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
BEGIN
SELECT ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME
INTO
I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME FROM ORDERS, CUSTOMER 
WHERE
ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM AND ORDERS.ORDER_NUM = I_ORDER_NUM;
DBMS_OUTPUT.PUT_LINE(I_ORDER_DATE);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NUM);
DBMS_OUTPUT.PUT_LINE(I_CUSTOMER_NAME);
END;
/

我的任务是:获取当前编号存储在I_ORDER_NUM中的订单的订单日期、客户编号和名称。将这些值放入变量中I_ORDER_DATE, I_CUSTOMER_NUM和I_CUSTOMER_NAME。输出I_ORDER_DATE、I_CUSTOMER_NUM和的内容I_CUSTOMER_NAME .

我使用了上面的代码，但我得到了以下内容:第8行错误:PL/SQL: SQL Statement ignored

6. I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;
7. select order_date, customer_num, customer_name
8. 到
9. I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME FROM ORDERS, CUSTOMER ">

应该只有1个输入，我应该得到输出，但我得到了那个错误，我不知道该怎么办。在写这篇文章的时候已经晚了，所以也许我在查询中遗漏了一些东西。如果有人能指点一下我的问题，我将不胜感激。

Answer 1

列CUSTOMER_NUM在SELECT语句中定义模糊，因为它属于两个表(orders和customer)，所以-没有指定需要哪个表- Oracle不知道该取哪个。

此外，我建议您使用表别名和显式JOIN表。

像这样:

SQL> CREATE OR REPLACE PROCEDURE order_info (i_order_num IN orders.order_num%TYPE)
2  AS
3     i_order_date     orders.order_date%TYPE;
4     i_customer_num   orders.customer_num%TYPE;
5     i_customer_name  customer.customer_name%TYPE;
6  BEGIN
7     SELECT o.order_date, o.customer_num, c.customer_name
8       INTO i_order_date, i_customer_num, i_customer_name
9       FROM orders o JOIN customer c ON o.customer_num = c.customer_num
10      WHERE o.order_num = i_order_num;
11
12     DBMS_OUTPUT.put_line (i_order_date);
13     DBMS_OUTPUT.put_line (i_customer_num);
14     DBMS_OUTPUT.put_line (i_customer_name);
15  END;
16  /
Procedure created.
SQL>