存储过程动态参数

create or replace procedure testdynamicparams(p_cmbno      in varchar2,
                                              p_trade_date in varchar2) is
      cursor testcursor is
            select cmbno, trade_date, securno, turnover
            from   xxx t
            where  1 = 1 and
                   ((p_cmbno is null) or (t.cmbno = p_cmbno)) and
                   ((p_trade_date is null) or
                   (t.trade_date = p_trade_date));
      v_testcur testcursor%rowtype;
begin
      open testcursor;
      loop
            fetch testcursor
                  into v_testcur;
            exit when testcursor%notfound;
            dbms_output.put_line(v_testcur.cmbno || '-------' ||
                                 v_testcur.securno || '--------' ||
                                 v_testcur.turnover);
      end loop;
      close testcursor;
end testdynamicparams;

说明：当p_cmbno = null时，where 后面是1=1 and p_cmbno is null
当p_trade_date = null && p_cmbno = null时就是1=1 and p_cmbno is null and p_trade_date is null
如果都不为Null，就是1=1 and t.cmbno = p_cmbno and t.trade_date = p_trade_date;