CREATE OR REPLACE PACKAGE SCOTT.pkg_test as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor; 

--函数申明
function get(intID number , bbn out number) return myrctype;

Procedure getstudent( 
        intID number,
       return_list out myrctype 
  );

function getforpage(  
    Pcount out number,               --返回分页总数
    Pindex in number,                --分页索引   
    Psize in number,              --页面大小
    Pordby  in varchar               --排序字段
) return myrctype;

Procedure getforpagep( 
    Pcount out number,               --返回分页总数
    return_list out myrctype,
    Pindex in number,                --分页索引   
    Psize in number,               --页面大小
    Pordby   in varchar               --排序字段
  );

end pkg_test;

CREATE OR REPLACE PACKAGE BODY SCOTT.pkg_test as
--函数体
    function get(intID number ,bbn out 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;
        bbn := 0;
        return rc;
    end get;
    
  Procedure getstudent( 
         intID number,
       return_list out myrctype 
  )as 
   
   begin 
       open return_list
       for 
        select id,name,sex,address,postcode,birthday from student;
  end getstudent; 



function getforpage(  
    Pcount out number,               --返回分页总数
    Pindex in number,                --分页索引   
    Psize in number,              --页面大小
    Pordby  in varchar               --排序字段
) return myrctype
    is
          rc myrctype; --定义ref cursor变量
          sqlstr varchar2(500);
          v_sql VARCHAR2(1000);
          v_count number;  
          v_Plow number;
          v_Phei number;
        begin
            sqlstr :='select id,name,sex,address,postcode,birthday from student';
              ------------------------------------------------------------取分页总数
              v_sql := 'select count(*) from (' || sqlstr || ')';
              execute immediate v_sql into v_count;
              Pcount := ceil(v_count/Psize);
              ------------------------------------------------------------显示任意页内容
              v_Phei := Pindex * Psize + Psize;
              v_Plow := v_Phei - Psize + 1;
              --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
              ---select * from (select rownum rn , t.* from  (select  t.* from student t) t )where rn between '2' and '3'
              v_sql := 'select id,name,sex,address,postcode,birthday from (select rownum rn , t.* from  (' || sqlstr || ') t order by '|| Pordby ||') where rn between ' || v_Plow || ' and ' || v_Phei ;

              open rc for v_sql;
            return rc;
    end getforpage;




Procedure getforpagep( 
    Pcount out number,               --返回分页总数
    return_list out myrctype,
    Pindex in number,                --分页索引   
    Psize in number,               --页面大小
    Pordby  in varchar               --排序字段
  )as 
          rc myrctype; --定义ref cursor变量
          sqlstr varchar2(500);
          v_sql VARCHAR2(1000);
          v_count number;  
          v_Plow number;
          v_Phei number;
   
        begin
            sqlstr :='select id,name,sex,address,postcode,birthday from student';
              ------------------------------------------------------------取分页总数
              v_sql := 'select count(*) from (' || sqlstr || ')';
              execute immediate v_sql into v_count;
              Pcount := ceil(v_count/Psize);
              ------------------------------------------------------------显示任意页内容
              v_Phei := Pindex * Psize + Psize;
              v_Plow := v_Phei - Psize + 1;
              --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
              
              v_sql := 'select id,name,sex,address,postcode,birthday from (select rownum rn , t.* from  (' || sqlstr || ') t order by '|| Pordby ||' ) where rn between ' || v_Plow || ' and ' || v_Phei ;

              open return_list for v_sql;
      end getforpagep;


end pkg_test;
/