SQL:找不到记录。处理没有发现错误

By simon at 2018-02-07 • 0人收藏 • 23人看过

我创建了这个SQL代码,因为我需要对ISIN的特定列表进行排名。 因此,我一直在使用下面的代码来ge结果:

var r refcursor;

begin
   msa.exl_stifel_ms ('01/01/2017',
                      '21/01/2018',
                      'GBP',
                      'CB',
                      'JE00BYR8GK67',
                      'UBS',
                      :r);
end;
我找到了“没有记录”,这显然是错误的,并表明这一点 有什么问题的查询....任何意见?
create or replace procedure msa.exl_stifel_ms (
   tradedatestart   in     date,
   tradedateend     in     date,
   inccy            in     varchar,
   inbtype          in     varchar,
   invariable       in     varchar,
   inbroker         in     varchar,
   test                out sys_refcursor)
as
   inbrokerid   integer;
begin
   delete from tt_exl_out;

   delete from isins_tt;

   delete from tt_exl_indexdetail;

   delete from index_tt;

   select brokerid
     into inbrokerid
     from msa.client
    where clientname = inbroker;

   insert into isins_tt (isin)
      select distinct effectiveisin
        from msa.instrument inst
       where inst.isin = invariable; --will need to loop through all the ISINs here

   rp_calctrnbicdetailtotals (inccy,
                              inbtype,
                              tradedatestart,
                              tradedateend);


   insert into tt_exl_indexdetail (isin,
                                   brokerid,
                                   brokercode,
                                   brokertotal)
        select i.effectiveisin,
               ba.brokerid,
               (select br2.brokercode
                  from broker br2
                 where br2.brokerid = ba.brokerid),
               sum (t.total)
          from bicisintotal_tt t
               inner join bank ba
                  on t.bicid = ba.bicid and ba.includeinreport = 1
               inner join instrument i on t.isin = i.isin
      group by ba.brokerid, i.effectiveisin;

   update tt_exl_indexdetail tt1
      set percentage =
             (select case x.totalvalue
                        when 0 then 0
                        else (i.brokertotal / x.totalvalue)
                     end
                        as percentage
                from index_tt i,
                     (select sum (brokertotal) as totalvalue from index_tt) x
               where brokerid = tt1.brokerid);


   insert into tt_exl_out (yearno,
                           rank,
                           turnover,
                           marketshare)
      select id1.brokerid,
             dense_rank () over (order by id1.brokertotal desc),
             id1.brokertotal,
             id1.percentage
        from index_tt id1;

   open test for
      select rank,
             c.clientname,
             turnover,
             marketshare
        from tt_exl_out tt3
             inner join msa.client c on tt3.yearno = c.brokerid
       where c.clientname = inbroker;
end;

1 个回复 | 最后更新于 2018-02-07
2018-02-07   #1

除非我错了这个:

select brokerid
     into inbrokerid
     from msa.client
    where clientname = inbroker
是唯一可以返回NO-DATA-FOUND的SELECT(如果这就是你所说的 “没有找到记录“)。其他SELECT是INSERT或UPDATE语句的一部分,所以 他们根本不会做任何事情,但也不会提出错误。 哟你也叫rp_calctrnbicdetailtotals - 我不知道它是干什么的,但是 - 可能是 候选人也是一个错误。 尝试运行该代码我SQL * Plus会告诉你一个错误的确切位置 (PL / SQL对象名称,行号),以便您可以修复它。确保 哟ü删除所有WHEN OTHERS异常处理程序(如果有的话)。 * [编辑,看到@ kfinity的评论后] * 正如我所说,我认为OT。这是一个例子。第一个SELECT返回nothing ,EMPNO = -1的员工没有(正如你所说的那样,它会返回)n no- 数据实测值):
SQL> var pe refcursor
SQL> create or replace procedure p_test (test out sys_refcursor) is
  2  begin
  3    open test for
  4      select ename
  5        from emp
  6        where empno = -1;
  7  end;
  8  /

Procedure created.

SQL> exec p_test (:pe)

PL/SQL procedure successfully completed.

SQL> print pe

no rows selected
看到?没有错误。 但是,如果这是一个普通的SELECT ... INTO,那么是的 - 它会提高 它:
SQL> create or replace procedure p_test as
  2    l_ename varchar2(20);
  3  begin
  4    select ename into l_ename
  5      from emp
  6      where empno = -1;
  7  end;
  8  /

Procedure created.

SQL> exec p_test
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.P_TEST", line 4
ORA-06512: at line 1


SQL>
* [编辑#2] * 是的,就像亚历山德罗所说的那样,它应该由EXCEPTION部分来处理。

登录后方可回帖

Loading...