oracle返回结果集
新项目启动想使用存储过程返回结果集，上家公司经常写，这才几年就忘的差不多了，费了老大劲才搞好。留个印，以备后续查看。。。
oracle 返回结果集 需要使用到包（package），结果集是作为游标类型(CURSOR)返回. 且返回的结果集不能在pl/sql中利用exec 存储
过程名() 看到结果集(模糊记得是可以但没实现，通过函数实现是看到的一个游标结果集，点击是可以看到结果集的，也许是记错了，
期待有知道的朋友解答)。
事例代码，实现分页存储过程。
fn_page 通过函数实现分页，pl/sql执行 可以看到结果集，但函数不够灵活；
sp_page 存储过程实现。
包头的定义
fn_page
1 CREATE OR REPLACE PACKAGE pkg_test 2 AS 3 type refCursorType is REF CURSOR; 4 5 function fn_Page 6 (p_PageSize int, --每页记录数 7 p_PageNo int, --当前页码,从 1 开始 8 p_SqlSelect varchar2) 9 return refCursorType; 10 11 PROCEDURE sp_Page 12 (p_PageSize int, --每页记录数 13 p_PageNo int, --当前页码,从 1 开始 14 p_SqlSelect varchar2, --查询语句,含排序部分 15 p_OutRecordCount out int,--返回总记录数 16 p_OutCursor out refCursorType); --Package中声明名为get 的Procedure(只有接口没内容) 17 18 19 END pkg_test;
包体
1 create or replace package body pkg_test 2 as procedure sp_Page(p_PageSize int, --每页记录数 3 p_PageNo int, --当前页码,从 1 开始 4 p_SqlSelect varchar2, --查询语句,含排序部分 5 p_OutRecordCount out int,--返回总记录数 6 p_OutCursor out refCursorType) 7 is 8 v_sql varchar2(3000); 9 v_count int; 10 v_heiRownum int; 11 v_lowRownum int; 12 13 begin 14 ----取记录总数 15 v_sql := 'select count(*) from (' || p_SqlSelect || ')'; 16 execute immediate v_sql into v_count; 17 p_OutRecordCount := v_count; 18 ----执行分页查询 19 v_heiRownum := p_PageNo * p_PageSize; 20 v_lowRownum := v_heiRownum - p_PageSize + 1; 21 22 v_sql := 'SELECT * 23 FROM ( 24 SELECT A.*, rownum rn 25 FROM ('|| p_SqlSelect ||') A 26 WHERE rownum <= '|| to_char(v_heiRownum) || ' 27 ) B 28 WHERE rn >= ' || to_char(v_lowRownum) ; 29 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 30 31 OPEN p_OutCursor FOR v_sql; 32 33 end sp_Page; 34 35 ------------------------------------------------------------------------------- 36 function fn_page 37 (p_PageSize int, --每页记录数 38 p_PageNo int, --当前页码,从 1 开始 39 p_SqlSelect varchar2) 40 return refCursorType 41 is allinfo refCursorType; 42 v_sql varchar2(3000); 43 --v_count int; 44 v_heiRownum int; 45 v_lowRownum int; 46 begin 47 v_heiRownum := p_PageNo * p_PageSize; 48 v_lowRownum := v_heiRownum - p_PageSize + 1; 49 50 v_sql := 'SELECT * 51 FROM ( 52 SELECT A.*, rownum rn 53 FROM ('|| p_SqlSelect ||') A 54 WHERE rownum <= '|| to_char(v_heiRownum) || ' 55 ) B 56 WHERE rn >= ' || to_char(v_lowRownum) ; 57 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 58 59 OPEN allinfo FOR v_sql; 60 -- open allinfo for select id,name from test where rownum<40; 61 return allinfo; 62 end fn_page; 63 end pkg_test;
调用方式可以使用 call、exec\execute ,begin ... end; 的方式。
