CREATE OR REPLACE p_query
(
emp_id IN VARCHAR2,
name IN VARCHAR2,
birthday IN VARCHAR2,
……
v_cur OUT pk_type.ref_cursor
/*
存储过程所需要的所有条件由前台传过来,前台在获取用户检索条件的时候由代码判断对应的条件,如果为空,则对应的变量设置为'%',如果不为空则设为'%用户条件%',然后用这些条件调用存储过程,过程返回检索到的数据。
*/
)
AS
……
BEGIN
OPEN v_cur for
SELECT relation,nvl(sum(num_cdrs),0),nvl(sum(durations),0),nvl(sum(sett_fees/100),0)
FROM trw_settle_report
WHERE 员工号 LIKE 'emp_id'
AND 姓名 LIKE 'name'
AND 出生日期 LIKE 'birthday'
AND ……;
EXCEPTION:
WHEN OTHERS THEN
……;
str:='select * from tab1 where empno=nvl('||p_empno||',empno) and name=nvl('||p_name||',name) and bdate=nvl('||p_bdate||',bdate) and zm=nvl('||p_zm||',zm) and zf=nvl('||p_zf||',zf) and dept=nvl('||p_dept||',dept) and xj=nvl('||p_xj||',xj)';
open str for p_rc;
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create procedure pro(p_empno in varchar2,p_name in varchar2,p_bdate in date,p_zm in varchar2,p_zf in varchar2,p_dept in varchar2,p_xj in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(100);
begin
str;='select * from tab1 where 1=1';
if p_empno is not null then
str:=str||' and empno='||p_empno;
end if;
if p_name is not null then
str:=str||' and name='||p_name;
end if;
if p_bdate is not null then
str:=str||' and bdate='||p_bdate;
end if;
if p_zm is not null then
str:=str||' and zm='||p_zm;
end if;
if p_zf is not null then
str:=str||' and zf='||p_zf;
end if;
if p_dept is not null then
str:=str||' and dept='||p_dept;
end if;
if p_xj is not null then
str:=str||' and xj='||p_xj;
end if;
open str for p_rc;
end;
/