oracle用存储过程返回查询结果

在oracle的precedure要么使用cursor返回单个值（含单条记录），要么就使用ref cursor返回记录集。

 

如果是查询1个列，可以如下：

  2    cursor v_cur is
  3      select t2 from t;
  4  begin
  5     for v_t in v_cur
  6  loop
  7     dbms_output.put_line(v_t.t2);
  8  end loop;
  9  end;
 10  /
1
2
3
4
5
6
6

PL/SQL procedure successfully completed.

如果是个记录集可以如下：
create or replace procedure test(cr_test in out sys_refcursor)
is
  3  begin
  4     open cr_test for select * from t;
  5  end;
  6  /

Procedure created.

x refcursor
test(:x)

PL/SQL procedure successfully completed.

x

T1                                               T2
---------------------------------------- ----------
aa                                                1
bbb                                               2
?                                                 3
c                                                 4
T?                                               5
 6
dd                                                6

7 rows selected.

当然还有其他方法能返回，这里就不多啰唆了。