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;
/