现在需要些一个存储过程,描述如下:
  
     有a, b ,c 三个参数,如果a有值,则在sql语句的where部分加上条件a,如果a没有值,则在sql语句的where部分不加在
where部分。b, c也是同样的处理。     我写了一个存储过程如下:
create or replace package body INVOICE_PACKAGE is
function FINDSUBINVOICE(a varchar2,
                        b date,
                        c date,
                        ) return SYS_REFCURSOR is
         outCursor SYS_REFCURSOR;
         str varchar2(256);
    begin
    open outCursor for
         select
                INVOICE_ID,
                ACCOUNT_ID,
                STATUS,
                INVOICE_NUMBER,
                INVOICE_DT,
                ENTERED_DT,
                DUE_DT,
                INVOICED_AMOUNT,
                CURRENCY_BASE,
                AGREED_AMOUNT,
                DISPUTE_AMOUNT,
                DISPUTE_ID,
                TRAFFIC_START_DT,
                TRAFFIC_END_DT,
                TOTAL_CALLS,
                TOTAL_MINS,
                UPDATE_USER,
                UPDATE_DT,
                GLOBAL_SITE_NAME
           from invoice_header
           where  
                IF a IS not NULL
                then 
                   str := 'ACCOUNT_ID = a';
                end IF;
                IF b IS NOT NULL 
                then
                   str := 'and TRAFFIC_START_DT >= b and TRAFFIC_END_DT <= c';
                end IF;
                0=0 AND := str;
    return outCursor;
end FINDSUBINVOICE;
end INVOICE_PACKAGE;上面的写法有错,不知道该怎样改,请帮忙。

解决方案 »

  1.   

    你用show error看看错在哪里了。如果解决不了,我也不太懂了。现在我也弄不明白这些呢
      

  2.   

    下面是编译出现的错误,请参考
    Compilation errors for PACKAGE BODY TEST01.INVOICE_PACKAGEError: PL/SQL: ORA-00920: 无效的关系运算符
    Line: 121
    Text: IF accountId_ is not nullError: PL/SQL: SQL Statement ignored
    Line: 98
    Text: selectError: PLS-00103: 出现符号 "IF"在需要下列之一时:
            ; <an identifier>
              <a double-quoted delimited-identifier> delete exists prior
              <a single-quoted SQL string>
    Line: 124
    Text: end IF;
      

  3.   

    这样不行的。你要把条件写在cursor打开前才可以的
    create   or   replace   package   body   INVOICE_PACKAGE   is 
    function   FINDSUBINVOICE(a   varchar2, 
                                                    b   date, 
                                                    c   date, 
                                                    )   return   SYS_REFCURSOR   is 
                      outCursor   SYS_REFCURSOR; 
                      str   varchar2(256); 
            begin 
                                 
               if  a <> null && b <> null && c <> null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header 
                          where 
                          ACCOUNT_ID   =   a and
                          TRAFFIC_START_DT >= b and TRAFFIC_END_DT   <=   c;
               end;
               
              if  a <> null && b <> null && c = null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header 
                          where 
                          ACCOUNT_ID   =   a and
                          TRAFFIC_START_DT >= b;
               end;
               
              if  a <> null && b = null && c = null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header 
                          where 
                          ACCOUNT_ID   =   a   ;                   
               end;
               
              if  a = null && b <> null && c <> null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header 
                          where 
                          TRAFFIC_START_DT >= b and TRAFFIC_END_DT   <=   c;                    
               end;
               
            if  a = null && b = null && c <> null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header 
                          where 
                          ACCOUNT_ID   =   a                      
               end;
               
               
               if  a = null && b = null && c = null then
                       open   outCursor   for 
                      select 
                                    INVOICE_ID, 
                                    ACCOUNT_ID, 
                                    STATUS, 
                                    INVOICE_NUMBER, 
                                    INVOICE_DT, 
                                    ENTERED_DT, 
                                    DUE_DT, 
                                    INVOICED_AMOUNT, 
                                    CURRENCY_BASE, 
                                    AGREED_AMOUNT, 
                                    DISPUTE_AMOUNT, 
                                    DISPUTE_ID, 
                                    TRAFFIC_START_DT, 
                                    TRAFFIC_END_DT, 
                                    TOTAL_CALLS, 
                                    TOTAL_MINS, 
                                    UPDATE_USER, 
                                    UPDATE_DT, 
                                    GLOBAL_SITE_NAME 
                          from   invoice_header;                    
               end;
                          
            return   outCursor; 
    end   FINDSUBINVOICE; 
    end   INVOICE_PACKAGE; 
      

  4.   

    楼主的代码太帅了,呵呵。
    最简单的解决方法:select 
      INVOICE_ID, 
      ACCOUNT_ID, 
      STATUS, 
      INVOICE_NUMBER, 
      INVOICE_DT, 
      ENTERED_DT, 
      DUE_DT, 
      INVOICED_AMOUNT, 
      CURRENCY_BASE, 
      AGREED_AMOUNT, 
      DISPUTE_AMOUNT, 
      DISPUTE_ID, 
      TRAFFIC_START_DT, 
      TRAFFIC_END_DT, 
      TOTAL_CALLS, 
      TOTAL_MINS, 
      UPDATE_USER, 
      UPDATE_DT, 
      GLOBAL_SITE_NAME 
    from invoice_header 
    where (ACCOUNT_ID = a or a is null) 
      and (TRAFFIC_START_DT >= b or b is null) 
      and (TRAFFIC_END_DT >= c or c is null) 
    最佳解决方案是使用动态游标,这里就不给具体的脚本了。
      

  5.   

    sql写得不好,请谅解。
    还有好的写法吗?
      

  6.   

    给你一个动态游标的范例:declare 
      Strsql        Varchar2(4000);  
      Type Tcur Is Ref Cursor;
      Cur_Queue     Tcur;
    begin
      --设置游标SQL
      Strsql := 'SELECT * FROM …………';   --这里的SQL可以根据需要生成  --循环执行
      Open Cur_Queue For Strsql;
      Loop
        Fetch Cur_Queue Into ……;
        Exit When Cur_Queue%Notfound;
        Begin
          ……
           ……   
        END;
      END LOOP;
      Close Cur_Queue;    
    end;
      

  7.   

    下面是我写的sql,但是有错误
    function FINDSUBINVOICE(accountId_ varchar2,
                            trafficEndDate_ date,
                            trafficStartDate_ date,
                            status_ varchar2,
                            invoiceDate_ date,
                            invoiceNumber_ varchar2
                            ) return SYS_REFCURSOR is
             outCursor SYS_REFCURSOR;
             strsql varchar2(1000);
        begin
         strsql :=   'select '
         ||'INVOICE_ID, '
         ||'ACCOUNT_ID, '
         ||'STATUS, '
         ||'INVOICE_NUMBER, '
         ||'INVOICE_DT, '
         ||'ENTERED_DT, '
         ||'DUE_DT, '
         ||'INVOICED_AMOUNT, '
         ||'CURRENCY_BASE, '
         ||'AGREED_AMOUNT, '
         ||'DISPUTE_AMOUNT, '
         ||'DISPUTE_ID, '
         ||'TRAFFIC_START_DT, '
         ||'TRAFFIC_END_DT, '
         ||'TOTAL_CALLS, '
         ||'TOTAL_MINS, '
         ||'UPDATE_USER, '
         ||'UPDATE_DT, '
         ||'GLOBAL_SITE_NAME '
         ||'from invoice_header '
         ||'where 0=0 ';
         if accountId_ is NULL then
         strsql := strsql ||'';
          else
              strsql := strsql || 'and ACCOUNT_ID = accountId_ ';
         end if;
          if status_ is NULL then
              strsql := strsql ||'';
          else
              strsql := strsql || 'and STATUS = status_ ';
          end if;
          if trafficStartDate_ is null then
              strsql := strsql || '';
          else 
              strsql := strsql || 'and TRAFFIC_START_DT >= trafficStartDate_ ';
          end if;
          if trafficEndDate_ is null then
              strsql := strsql || '';
          else 
              strsql := strsql || 'and TRAFFIC_END_DT <= trafficEndDate_ ';
          end if; 
          if invoiceDate_ is null then
              strsql := strsql || '';
          else 
              strsql := strsql || 'and INVOICE_DT = invoiceDate_ ';
          end if;
          if invoiceNumber_ is null then
              strsql := strsql || ';';
          else 
              strsql := strsql || 'and INVOICE_NUMBER = invoiceNumber_; ';
          end if;
          open outCursor for strsql;
          return outCursor;
    end FINDSUBINVOICE;在执行时,显示 open outCursor for strsql; 行有错 ORA-00911 字符无效  错误, 怎么解决? 
      

  8.   

    问题1:SQL不能带“;”
                  “strsql ¦¦ ';'”问题2:SQL字符串拼接有问题(好几个地方)strsql := strsql ¦¦ 'and STATUS = status_ '; 改成:strsql := strsql ¦¦ 'and STATUS = ''' || status_ || '''';
      

  9.   

    我浪费点时间吧。function FINDSUBINVOICE(accountId_        varchar2,
                            trafficEndDate_   date,
                            trafficStartDate_ date,
                            status_           varchar2,
                            invoiceDate_      date,
                            invoiceNumber_    varchar2)
      return SYS_REFCURSOR is
      outCursor SYS_REFCURSOR;
      strsql    varchar2(1000);
    begin
      strsql := 'select '
              ||chr(10)||'  INVOICE_ID, ' 
              ||chr(10)||'  ACCOUNT_ID, ' 
              ||chr(10)||'  STATUS, ' 
              ||chr(10)||'  INVOICE_NUMBER, ' 
              ||chr(10)||'  INVOICE_DT, ' 
              ||chr(10)||'  ENTERED_DT, ' 
              ||chr(10)||'  DUE_DT, ' 
              ||chr(10)||'  INVOICED_AMOUNT, ' 
              ||chr(10)||'  CURRENCY_BASE, ' 
              ||chr(10)||'  AGREED_AMOUNT, ' 
              ||chr(10)||'  DISPUTE_AMOUNT, ' 
              ||chr(10)||'  DISPUTE_ID, ' 
              ||chr(10)||'  TRAFFIC_START_DT, ' 
              ||chr(10)||'  TRAFFIC_END_DT, ' 
              ||chr(10)||'  TOTAL_CALLS, ' 
              ||chr(10)||'  TOTAL_MINS, ' 
              ||chr(10)||'  UPDATE_USER, ' 
              ||chr(10)||'  UPDATE_DT, ' 
              ||chr(10)||'  GLOBAL_SITE_NAME ' 
              ||chr(10)||'from invoice_header' 
              ||chr(10)||'where 0=0';   if accountId_ is not null then
        strsql := strsql ||chr(10)|| '  and ACCOUNT_ID = ''' || accountId_ || '''';
      end if;
      if status_ is not null then
        strsql := strsql ||chr(10)|| '  and STATUS = ''' || status_ || '''';
      end if;
      if trafficStartDate_ is not null then
        strsql := strsql ||chr(10)|| '  and TRAFFIC_START_DT >= to_date(''' || to_char(trafficStartDate_, 'yyyy-mm-dd hh24:mi:ss') || '''  ,''yyyy-mm-dd hh24:mi:ss'')';
      end if;
      if trafficEndDate_ is not null then
        strsql := strsql ||chr(10)|| '  and TRAFFIC_END_DT <= to_date(''' || to_char(trafficEndDate_, 'yyyy-mm-dd hh24:mi:ss') || '''  ,''yyyy-mm-dd hh24:mi:ss'')';
      end if;
      if invoiceDate_ is not null then
        strsql := strsql ||chr(10)|| '  and INVOICE_DT = to_date(''' || to_char(invoiceDate_, 'yyyy-mm-dd hh24:mi:ss') || '''  ,''yyyy-mm-dd hh24:mi:ss'')';
      end if;
      if invoiceNumber_ is not null then
        strsql := strsql ||chr(10)|| '  and INVOICE_NUMBER = ''' || invoiceNumber_ ||'''';
      end if;
      open outCursor for strsql;
      return outCursor;
    end FINDSUBINVOICE;
      

  10.   

    还有个问题,如果我在java中定义了两个参数:      String accountId;
         Date   trafficStartDate;
    因为没有给这两个参数初始化,所以在java中值是null,如果将参数传给上面的存储过程,那么oracle中得到的将是
    什么值,还是不是null,或者会出现什么错误?