oracle数据包的创建和使用

--表头
CREATE OR REPLACE PACKAGE GetPageList
AS
TYPE o_Cursor IS REF CURSOR;
PROCEDURE Get_News(pageIndex in Number,pageSize in Number,isDelu in

Number,uRowCount OUT Number,pageCount OUT Number,o_remCursor OUT o_Cursor);
end GetPageList;

--表体
CREATE OR REPLACE PACKAGE BODY GetPageList
AS
PROCEDURE Get_News(pageIndex in Number,pageSize in Number,
isDelu in Number,uRowCount OUT Number,pageCount OUT Number,o_remCursor OUT o_Cursor)
IS
BEGIN
OPEN o_remCursor FOR
SELECT NID,NTITLE,NCONTENT,NPICADDRESS,NADDTIME,NISDEL
FROM (
      SELECT A.*, ROWNUM RN FROM (SELECT * FROM News order by NID) A
      WHERE ROWNUM <= pageIndex*pageSize
  )
 WHERE RN >= (pageIndex-1)*pageSize+1 and NISDEL =isDelu;
select count(*) into uRowCount From News;
pageCount := ceil(uRowCount/pageSize);
END Get_News;
END GetPageList;


--调用数据包
declare
     R_Cursor  REPORT.o_Cursor;
begin  
     REPORT.Get_DepartmentOverview(R_Cursor);
END; 
--调用返回游标的数据包
declare
  out_info sys_refcursor;
  type typ_a_b is record (week VARCHAR2(50),rq VARCHAR2(50),sj  VARCHAR2(50),salesno  VARCHAR2(50),salesname  VARCHAR2(50),
  companyid  VARCHAR2(50),scomapnyname  VARCHAR2(50),ZT  VARCHAR2(50));
  result_temp typ_a_b;
begin
  Sales_Attendance.Get_Detail('2019-03-21','2019-04-11','11232',out_info);
loop
  fetch out_info into result_temp;
  exit when out_info%notfound;
  Dbms_output.put_line(result_temp.salesno);
end loop;
close out_info;
END;