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;