PL SQL - 类似于 (!a?0:a) 功能

Question

我在游标中有一个简单的查询

Cursor some_cursor IS
    select 
       sum(some_field) 
    from some_table table_1 
    where 
        table_1.TYPE =1                         
        AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
        AND table_1.f2 = 962
        AND table_1.f3 = 41813; 

然后我做

   fetch some_cursor into some_var;--some_var is of type Number, cursor is open

当我运行此查询时， some_var 有可能为 NULL。在这种情况下，id喜欢取0值；

类似

    --C like pseudocode of what I want
   (some_cursor!=Null?(fetch some_cursor into some_var):(some_var:=0))

有没有办法做到这一点？我正在考虑将上面的查询重写为

Cursor some_cursor IS
    select 
       sum(some_field),count(*) 
    from some_table table_1 
    where 
        table_1.TYPE =1                         
        AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
        AND table_1.f2 = 962
        AND table_1.f3 = 41813; 

然后写入，

   fetch some_cursor into some_var,some_counter;

   if (some_counter = 0) then
   begin
       some_var :=0;
   end

但这意味着重写 10 个游标（是的，不是那么多）。也许 plsql 有更干净的方法。

提前致谢

Answer 1

尝试：

SELECT NVL(SUM(some_field),0)
  from some_table table_1 
 where table_1.TYPE =1                                                 
   AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905'
   AND table_1.f2 = 962
   AND table_1.f3 = 41813;

Answer 2

您可能会在那里更正其他几个问题：

• 使用隐式游标而不是显式游标
• 如果可以避免，请勿将函数应用于表列。

因此：

Declare
   some_var Number;
Begin
   select Coalesce(sum(some_field),0)
   into   some_var
   from   some_table table_1   
   where  table_1.TYPE =  1                                                   
    AND   table_1.date >= date '2009-09-05' 
    AND   table_1.date <  date '2009-09-06' 
    AND   table_1.f2   =  962  
    AND   table_1.f3   =  41813;
End;