-- 一个存储过程,根据其输入的参数作为查询条件,输出结果集----如:create or replace package pkg_emp_info
as
type myrctype is ref cursor;
procedure emp_info_proc(v_empno in number,v_ename in VARCHAR2, v_deptno number,p_rc out myrctype);
end pkg_emp_info;
/create or replace package body pkg_emp_info
as
procedure emp_info_proc(v_empno in number,v_ename in VARCHAR2, v_deptno number,p_rc out myrctype)
is
sqlstr VARCHAR2(2000);
begin sqlstr :=
'SELECT * FROM emp where 1=1 and empno='
if(v_empno is not null) --如果存储过程的第一个参数不为空,条件怎么写
sqlstr := v_empno
sqlstr := ' and v_empno ... if(v_ename is not null) --如果存储过程的第二个参数不为空,条件怎么写
sqlstr := v_ename
sqlstr := ' and v_ename ... if(v_ename is not null) --如果存储过程的第三个参数不为空,条件怎么写
sqlstr := v_deptno
sqlstr := ' and v_deptno ... OPEN p_rc FOR sqlstr;-- USING v_mobile; --,v_fromDate,v_fromDate,v_toDate,v_toDate,v_sign,v_sign; end mobile_status_detail_proc;
end pkg_emp_info;
/
as
type myrctype is ref cursor;
procedure emp_info_proc(v_empno in number,v_ename in VARCHAR2, v_deptno number,p_rc out myrctype);
end pkg_emp_info;
/create or replace package body pkg_emp_info
as
procedure emp_info_proc(v_empno in number,v_ename in VARCHAR2, v_deptno number,p_rc out myrctype)
is
sqlstr VARCHAR2(2000);
begin sqlstr :=
'SELECT * FROM emp where 1=1 and empno='
if(v_empno is not null) --如果存储过程的第一个参数不为空,条件怎么写
sqlstr := v_empno
sqlstr := ' and v_empno ... if(v_ename is not null) --如果存储过程的第二个参数不为空,条件怎么写
sqlstr := v_ename
sqlstr := ' and v_ename ... if(v_ename is not null) --如果存储过程的第三个参数不为空,条件怎么写
sqlstr := v_deptno
sqlstr := ' and v_deptno ... OPEN p_rc FOR sqlstr;-- USING v_mobile; --,v_fromDate,v_fromDate,v_toDate,v_toDate,v_sign,v_sign; end mobile_status_detail_proc;
end pkg_emp_info;
/
is
sqlstr VARCHAR2(2000);
begin sqlstr :=
'SELECT * FROM emp where 1=1 '
if(v_empno is not null) then --如果存储过程的第一个参数不为空,条件怎么写 sqlstr := sqlstr||' and empno = '||v_empno; --如果是字符串,写成:sqlstr := sqlstr||' and empno = '''||v_empno||'''';
end if;
if(v_ename is not null) then --如果存储过程的第二个参数不为空,条件怎么写
sqlstr := sqlstr||' and ename ='||v_ename;
end if;
if(v_ename is not null) then --如果存储过程的第三个参数不为空,条件怎么写
sqlstr := sqlstr||' and deptno = '||v_deptno;
end if;
OPEN p_rc FOR sqlstr; end mobile_status_detail_proc;