Oracle11g ORA-01403使用Select Into没有找到数据

Question

我是Oracle的新手，我有一个问题我已经挣扎了几个小时了。

示例:

 Create Table Accounts (Id number(10),Balance number(16,3), Status Varchar2(50),Owner_Id number(10));
 Create Table Transactions (Id number(10),Amount number(16,3), Trxn_date date, Account_Id number(10));
 Create Table Owner (Id number(10), Firstname varchar2(50),Lastname varchar2(50));
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (1,1000,'OPEN',10);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (2,5000,'CLOSED',11);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (3,1000,'OPEN',12);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (4,5000,'CLOSED',13);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (5,1000,'OPEN',14);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (6,5000,'CLOSED',15);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (7,1000,'OPEN',16);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (8,5000,'CLOSED',17);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (9,1000,'OPEN',18);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (10,5000,'CLOSED',19);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (11,1000,'OPEN',20);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (12,5000,'CLOSED',21);
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST1');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST2');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST3');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST4');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST5');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST6');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST7');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST8');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST9');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST10');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST11');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST12');
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (1,10,'02-FEB-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (2,10,'02-APR-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (3,10,'02-JUN-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (4,10,'02-AUG-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (5,10,'02-FEB-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (6,10,'02-APR-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (7,10,'02-JUN-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (8,10,'02-AUG-2015',2);     

数据检查:

   Select Unique(Account_Id) From Accounts A
 Inner Join Owner B on B.ID=A.OWNER_ID 
 Inner Join Transactions I on I.ACCOUNT_ID=A.Id
   Where I.Trxn_date Between '01-FEB-2015' and '01-JUL-2015' 
 And A.Status='CLOSED'
 and A.Balance=5000;/*1 Row Returned*/

循环必须在第一个Id返回时退出

   Declare
   l_NewDate date:='01-FEB-2015';
   l_OldDate date:='01-JUL-2015';
   l_pID number(10);
 Begin
   For I in (Select Account_Id From Transactions 
   Where Trxn_date Between l_NewDate and l_OldDate)
   Loop
   Select Id Into l_pID From 
   (Select B.Id From Accounts A 
   Inner Join Owner B on A.Owner_Id = B.Id
   Where A.Status = 'CLOSED' And A.Balance = 5000 And A.Id=I.Account_Id)
   Where rownum < 2;
   dbms_output.put_line(l_pID);
   Exit;
   End Loop;
   End;
 ORA-01403: No data found
 ORA-06512: at line 12

我不明白为什么没有数据，而上面的数据检查清楚地表明不是这样。

的问候奥尔森j .

Answer 1

就像你说的，你的数据检查查询:

Select Unique(Account_Id)
  From Accounts A
 Inner Join Owner B on B.ID=A.OWNER_ID 
 Inner Join Transactions I on I.ACCOUNT_ID=A.Id
 Where I.Trxn_date Between '01-FEB-2015' and '01-JUL-2015' 
   And A.Status='CLOSED'
   and A.Balance=5000;

…返回单个Account_Id值为2的单行。

但是，你的PL/SQL代码基本上将逻辑拆分为2个查询。循环执行的查询是:

Select Account_Id
  From Transactions 
 Where Trxn_date Between '01-FEB-2015' and '01-JUL-2015'

当我运行它时，它返回:

5
5
5
2
2
2

现在上面的订单是不保证的，因为你没有ORDER BY条款。但是，如果您以与我相同的顺序获得结果，那么您的第一个循环迭代将使用5作为输入执行下一个查询:

Select *
  From Accounts A 
 Inner Join Owner B on A.Owner_Id = B.Id
 Where A.Status = 'CLOSED'
   And A.Balance = 5000
   And A.Id = 5

…它不返回任何数据，这就是你得到错误的原因。

如果你足够幸运，从2:

开始

Select *
  From Accounts A 
 Inner Join Owner B on A.Owner_Id = B.Id
 Where A.Status = 'CLOSED'
   And A.Balance = 5000
   And A.Id = 2

…它会像预期的那样工作。

我希望我能推荐一个合适的解决方案，但我真的不明白你想做什么。但它确实让人感觉你不应该需要PL/SQL循环来做你想做的事情。一个简单的查询应该就足够了。你的数据检查查询似乎是一个好的开始。

编辑

为了它的价值，我认为这是一种更直接的方式来做你想做的事情(没有循环):

Declare
   l_NewDate date:='01-FEB-2015';
   l_OldDate date:='01-JUL-2015';
   l_pID number(10);
 Begin
   select o.id into l_pID
    from transactions t
    join accounts a
      on a.id = t.account_id
     and a.status = 'CLOSED'
     and a.balance = 5000
    join owner o
      on o.id = a.owner_id
   where t.trxn_date between l_NewDate and l_OldDate
     and rownum < 2;
  dbms_output.put_line(l_pID);
End;