oracle存储过程返回结果集SYS_REFCURSOR

点击打开链接

create or replace procedure proc_query_rent
(
param_region varchar2,--定义区
param_room number,--定义室
param_hall number,--定义厅
param_rentMin number,--定义租金上限
param_rentMax number,--定义租金下限
param_resultSet OUT SYS_REFCURSOR --定义out参数返回结果集
)
as
--v_text_sql varchar2(500);
begin
open param_resultSet for
select * from tb_rent
where
region like
case
when param_region IS null then '%'
else param_region
end
AND
room like
case
when param_room IS null then '%'
else to_char(param_room)
end
AND
hall like
case
when param_hall IS null then '%'
else to_char(param_hall)
end
AND
rent between
case
when param_rentMin IS null then 0
else param_rentMin
end
AND
case
when param_rentMax IS null then 99999999
else param_rentMax
end;
end;

declare
v_rent_rows SYS_REFCURSOR;
v_rent_row tb_rent%rowType;
begin
proc_query_rent('山区',null,null,1200,null,v_rent_rows);
Dbms_output.put_line('所在区 室 厅 租金');
loop
fetch v_rent_rows into v_rent_row;
exit when v_rent_rows%NOTFOUND;
Dbms_output.put_line(v_rent_row.region||' '||v_rent_row.room||' '||v_rent_row.hall||' '||v_rent_row.rent);
end loop;
close v_rent_rows;
end;