我是PLSQL的新手,对这个过程有意见。我不知道这个错误意味着什么,同时我确信表和数据是成功创建的。
程序应收到发票编号的开始和结束日期,以显示的详细信息
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:在";应用程序。发票明细";,第5行ORA-06512:在2号线01403.00000-";未找到数据";
如果你说你是新手,那么你做得很好。
让我们深入研究这个问题,
如果您正在学习,则将其放入TODO
列表中,这是PLSQL中关于exception
的下一个主题以及如何处理。
你得到的错误是ORA-01403 :no data found
,这是不言自明的,意味着我们正在搜索一些东西,而你写的任何代码都没有按预期找到它,这导致我们使用select
语句,
SELECT *
INTO INV_info
FROM invoicetable
WHERE InvoiceNum = INV_info.InvoiceNum
AND INV_info,InvoiceDate betwen Fromdate And Todate;
在上面如果你看到,
第一个小问题是语法,它是
INV_info,InvoiceDate
,应该是INV_info.InvoiceDate
(根据结果的预期,这无论如何都不正确,我将在下面澄清(第二个也是最重要的问题是,你试图将
InvoiceNum
的值与rowtype
变量(即InvoiceNum = INV_info.InvoiceNum
(进行比较,你必须理解INV_info.InvoiceNum
是一个变量,在这个时候没有任何值。因此,您应该将表值与您通过参数WHERE invoicetable.InvoiceNum = invoiceNum
提供的输入进行比较。左侧是表列,右侧是您传递的参数。类似地,条件
AND INV_info,InvoiceDate betwen Fromdate And Todate
应当改变为AND invoicetable.InvoiceDate betwen Fromdate And Todate
。
说了所有这些之后,您还需要考虑变量的naming convention
的相互作用以及表的alias
的使用。(可以看出我对下面的程序做了哪些更改(
累积所有点,程序可以进一步修改为,
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;
/
这里是db<gt;小提琴供你参考。我必须做一个小技巧,调用dbms.output
打印结果,同时调用在机器中尝试时不需要的过程
首先,您将输入参数声明为DATE,但当您调用该过程时,您将提供CHARACTER STRING。对您来说,输入看起来像日期并不意味着oracle将其解释为日期。DATE是一种内部二进制数据类型。输入值实际来源于哪里?根据您的示例,您需要将输入字符串转换为DATE:
create or replace PROCEDURE Invoicedetails (Fromdate IN VARCHAR2 , Todate IN VARCHAR2 , InvoiceNum NUMBER)
IS
v_fromdate date;
v_todate date;
INV_info invoicetable%ROWTYPE;
BEGIN
v_fromdate := to_date(fromdate,'dd-Mon-yyyy');
v_todoate := to_date(todate,'dd-Mon-yyyy');
然后,在代码的其余部分中,引用v_fromdate和v_todate,而不是输入的parms。
顺便说一句,您还应该养成在编码风格上保持一致的习惯。与其他一些rdbms产品不同,oracle确实不支持MixedCaseNames。(好吧,你可以跳过一些障碍来迫使它这样做,但这是违背初衷的,你真的不想去那里。(oracle标准不是MixedCaseNames,而是undercore_separated_names。
您需要:
- 不要将过程的参数命名为表中的列;调试起来很混乱,并且可能会混淆SQL解析器将列与自身进行比较,而不是将列与参数的参数进行比较
- 处理
NO_DATA_FOUND
异常 - 处理
TOO_MANY_ROWS
异常
使用类似的东西:
CREATE PROCEDURE InvoiceDetails (
p_FromDate IN InvoiceTable.InvoiceDate%TYPE, -- Use the types from the table
p_ToDate IN InvoiceTable.InvoiceDate%TYPE,
p_InvoiceNum IN InvoiceTable.InvoiceNum%TYPE
)
IS
inv_info invoicetable%ROWTYPE;
BEGIN
SELECT *
INTO INV_info
FROM invoicetable
WHERE InvoiceNum = p_InvoiceNum -- Don't have the same variable name as the
-- column name. One practice is to prefix the
-- parameter names with p_ to distinguish
-- that they were passed from outside the
-- procedure.
AND 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: ' || TO_CHAR( INV_info.InvoiceDate, 'YYYY-MM-DD' ) );
EXCEPTION
WHEN NO_DATA_FOUND THEN -- Handle the exception when no rows are found.
dbms_output.put_line('No Invoices exist.');
WHEN TOO_MANY_ROWS THEN -- Handle the exception when multiple rows are found.
dbms_output.put_line('Multiple invices exist.');
END InvoiceDetails;
/
所以,如果你有表格:
CREATE TABLE invoicetable (
invoicenum NUMBER(10,0),
invoiceamount NUMBER(17,2),
invoicedate DATE
);
然后执行您的匿名PL/SQL块:
BEGIN
Invoicedetails( DATE '2020-01-01',DATE '2020-01-05',200651);
END;
/
将没有要匹配的行,并且将引发NO_DATA_FOUND
异常,您将得到输出:
No Invoices exist.
如果您插入一行:
INSERT INTO invoicetable (invoicenum, invoiceamount, invoicedate )
VALUES ( 200651, 200, DATE '2020-01-04' );
并运行相同的匿名PL/SQL块,现在可以得到输出:
ID: 200651 Amount: 200 Date: 2020-01-04
,如果插入第二行:
INSERT INTO invoicetable (invoicenum, invoiceamount, invoicedate )
VALUES ( 200651, 300, DATE '2020-01-05' );
并再次运行匿名PL/SQL块,您将得到输出:
Multiple invices exist.
db<gt;小提琴这里