oracle游标传参数,oracle存储过程，package,传参数值。游标返回值。

p_SqlCount= "select count(*) from  tbl_box_track_history where vid = " + nVId.ToString()+"  and location_time between :1 and :2";

create or replace package p_vfl_getpage

is

type ResultData is ref cursor;

procedure sp_Page(p_PageSize int,

p_PageNo int,

p_t1 DATE,

p_t2 DATE,

p_SqlSelect varchar2,

p_SqlCount varchar2,

p_RealPageNo out int,

p_OutRecordCount out int,

p_OutCursor out ResultData);

end p_vfl_getpage;

create or replace package body p_vfl_getpage

is

procedure sp_Page(p_PageSize int,

p_PageNo int,

p_t1 DATE,

p_t2 DATE,

p_SqlSelect varchar2,

p_SqlCount varchar2,

p_RealPageNo out int,

p_OutRecordCount out int,

p_OutCursor out ResultData)

as

v_sql varchar2(3000);

v_count int;

v_heiRownum int;

v_lowRownum int;

begin

execute immediate p_SqlCount into v_count using p_t1,p_t2;

p_OutRecordCount := v_count;

v_heiRownum := p_PageNo * p_PageSize;

v_lowRownum := v_heiRownum - p_PageSize +1;

if(v_lowRownum > v_count) then

if(mod(v_count,p_PageSize) = 0) then

p_RealPageNo := trunc(v_count / p_PageSize);

else

p_RealPageNo := trunc(v_count / p_PageSize) + 1;

end if;

v_heiRownum := p_RealPageNo * p_PageSize;

v_lowRownum := v_heiRownum - p_PageSize +1;

else

p_RealPageNo := p_PageNo;

end if;

v_sql := 'SELECT *

FROM (

SELECT A.*, rownum rn

FROM  ('|| p_SqlSelect ||') A

WHERE rownum <= '|| to_char(v_heiRownum) || '

) B

WHERE rn >= ' || to_char(v_lowRownum) ;

OPEN p_OutCursor FOR  v_sql using p_t1,p_t2;

end sp_Page;

end p_vfl_getpage;

CREATE OR REPLACE PACKAGE pkg_test AS     TYPE myrctype IS REF CURSOR;        PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); END pkg_test; /    CREATE OR REPLACE PACKAGE BODY pkg_test AS     PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)     IS        sqlstr   VARCHAR2 (500);     BEGIN        IF p_id = 0 THEN           OPEN p_rc FOR              SELECT ID, NAME, sex, address, postcode, birthday                FROM student;        ELSE           sqlstr :=              'select id,name,sex,address,postcode,birthday             from student where id=:w_id';           OPEN p_rc FOR sqlstr USING p_id;        END IF;     END get; END pkg_test; /    函数返回记录集: 建立带ref cursor定义的包和包体及函数： CREATE OR REPLACE package pkg_test as /* 定义ref cursor类型     不加return类型，为弱类型，允许动态sql查询，     否则为强类型，无法使用动态sql查询; */    type myrctype is ref cursor;       --函数申明    function get(intID number) return myrctype; end pkg_test; /     CREATE OR REPLACE package body pkg_test as --函数体     function get(intID number) return myrctype is       rc myrctype;  --定义ref cursor变量       sqlstr varchar2(500);     begin       if intID=0 then          --静态测试，直接用select语句直接返回结果          open rc for select id,name,sex,address,postcode,birthday from student;       else          --动态sql赋值，用:w_id来申明该变量从外部获得          sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';          --动态测试，用sqlstr字符串返回结果，用using关键词传递参数          open rc for sqlstr using intid;       end if;           return rc;     end get;     end pkg_test; /