问一存储过程写法.现在出现两种情况要进行判断的,已知有5个参数A,B,C,D,E(D,E分别是年和月),
第一种情况是知道A,B,C三个参数进行查询;D,E为不选!!!
第二种情况是A,B,C,D,E全选!!问在ORACLE的存储过程中如何写呢`~?~select * from customer
where a=:a and b=:b and c=:c and
D=:Year and E=:Month
第一种情况是知道A,B,C三个参数进行查询;D,E为不选!!!
第二种情况是A,B,C,D,E全选!!问在ORACLE的存储过程中如何写呢`~?~select * from customer
where a=:a and b=:b and c=:c and
D=:Year and E=:Month
where a=:a and b=:b and c=:c and
D like nvl(:Year,'%') and E like nvl(:Month,'%')
where (1=1 AND a=:a and b=:b and c=:c and D=:Year and E=:Month)
OR (1=0 AND a:=a and b:=b and c:=c)
;1=1是你的第1个条件
1=0是你的第2个条件
v_2 tablename.B%type,
v_3 tablename.C%type,
v_4 tablename.D%type,
v_5 tablename.E%type,
SY_CU OUT SYS_REFCURSOR) asbegin
IF V_4 IS NULL AND V_5 IS NULL THEN
OPEN SY_CU FOR
SELECT *
FROM TABLENAME
WHERE A = V_1
AND B = V_2
AND C = V_3;
ELSE
OPEN SY_CU FOR
SELECT *
FROM TABLENAME
WHERE A = V_1
AND B = V_2
AND C = V_3
AND D = V_4
AND E = V_5;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('参数错误');
end;
SY_CU OUT SYS_REFCURSOREXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('参数错误');这些都是什么意思?
exception 那块是异常处理