oracle存储过程返回结果集SYS_REFCURSOR

 --创建表

create table tb_rent(
   region  varchar2(2000),
   room    varchar2(2000),
   hall    varchar2(2000),
   rent    varchar2(2000)
)

 

---创建存储过程

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;