在PL/SQL中没有发现数据错误

Question

create or replace PROCEDURE Invoicedetails (Fromdate IN DATE , Todate IN DATE , InvoiceNum NUMBER)
IS 
INV_info invoicetable%ROWTYPE;
BEGIN
SELECT * 
INTO INV_info
FROM invoicetable
WHERE InvoiceNum = INV_info.InvoiceNum AND INV_info,InvoiceDate betwen Fromdate And Todate;
dbms_output.put_line('ID:'|| INV_info.InvoiceNum);
dbms_output.put_line('Amount:'|| INV_info.Invoiceamount);
dbms_output.put_line('Date:'|| INV_info.InvoiceDate);
END Invoicedetails;

当我像这样调用过程时

BEGIN
Invoicedetails('01-JAN-2020','05-JAN-2020',200651)
END;

错误报告ORA-01403:找不到数据ORA-06512:at“apps.invoicedetails”，第5行ORA-06512:at第2行014 03。00000-“未找到数据”

Answer 1

如果你说你是新来的，那么你已经做得很好了。

让我们深入研究这个问题，

如果您正在学习，请将下一个关于PLSQL中的Exception以及如何处理的主题放入Todo列表。

SELECT * 
  INTO INV_info
  FROM invoicetable
 WHERE InvoiceNum = INV_info.InvoiceNum 
   AND INV_info,InvoiceDate betwen Fromdate And Todate;

类似地，条件和INV_info、InvoiceDate betwen Fromdate和todate应该更改为和invoicetable.InvoiceDate betwen Fromdate和todate。

说了所有这些之后，还需要考虑命名约定变量的interms以及表的别名的用法。（可以看到我对下面的过程做了什么更改）

累积所有点数过程可以进一步修改为，

create or replace procedure invoicedetails 
 ( 
   p_fromdate in date 
 , p_todate in date 
 , p_invoicenum number)
is 
  inv_info invoicetable%rowtype;
begin
  select * 
    into inv_info
    from invoicetable i
   where i.invoicenum = p_invoicenum 
     and i.invoiceDate between p_fromdate and p_todate;
  dbms_output.put_line('ID:'|| inv_info.invoicenum);
  dbms_output.put_line('Amount:'|| inv_info.invoiceamount);
  dbms_output.put_line('Date:'|| inv_info.invoicedate);
end invoicedetails;
/