ORACLE存储过程 练习系列六 关键字 分页查询某个方案下的建表语句

create or replace package PAGEQUERY_PACK is

  -- Author  : ADMINISTRATOR
  -- Created : 2013/1/13 15:47:30
  -- Purpose : FEN YE CHA XUN 
  
  -- Public type declarations
  TYPE CUR IS REF CURSOR;
   -- Public function and procedure declarations
   PROCEDURE PAGINATING_all_objects_P(
       P_CURSOR OUT  CUR,
       startno IN INTEGER ,
       getnum IN INTEGER ,
       IN_owner in varchar2);
       
   PROCEDURE PAGINATING_all_objects_PP
   ( startno IN INTEGER ,
       getnum IN INTEGER ,
       IN_owner in varchar2);
 
end PAGEQUERY_PACK;
 

create or replace package body PAGEQUERY_PACK is
 
   
  PROCEDURE PAGINATING_all_objects_P(P_CURSOR OUT   CUR,
                                     startno  IN INTEGER,
                                     getnum   IN INTEGER,
                                     IN_owner in varchar2) IS
    /***************************************************************
    *NAME : [Name]
    *PURPOSE : --分页查询 all_objects表 指定方案的 table类型数据
    *参数 startno 开始 NO     从1 开始
    *参数 getnum 得到的数量   包括开始no 
             查询内容 OBJECT_ID  OBJECT_NAME
    
    ************************************************************/
  BEGIN
    OPEN p_CURSOR FOR
      select row_n.OBJECT_ID, row_n.OBJECT_NAME, row_n.rownum_
        FROM (SELECT row_.*, ROWNUM rownum_
                FROM (select object_id,object_name
                        from all_objects
                       where object_type = 'TABLE'
                         and owner = upper(IN_owner)) row_
               WHERE ROWNUM < startno + getnum) row_n
       WHERE rownum_ >= startno;
   
  END PAGINATING_all_objects_P;
  
  
PROCEDURE PAGINATING_all_objects_PP(startno  IN INTEGER,
                                    getnum   IN INTEGER,
                                    IN_owner in varchar2) as
/*调用PAGINATING_all_objects_P的存储过程*/
  v_cur PAGEQUERY_PACK.CUR;
  type A_ROW is record(
    id   ALL_OBJECTS.OBJECT_ID%type,
    name ALL_OBJECTS.OBJECT_name%type,
    num  number);
  a_r A_ROW;
begin
  PAGEQUERY_PACK.PAGINATING_all_objects_P(v_cur, startno, getnum, IN_owner);
  fetch v_cur
    into a_r;
  while v_cur%found loop
    dbms_output.put_line('ALL_OBJECTS.OBJECT_ID'||'  :  '||a_r.id);
     dbms_output.put_line('=======================================');
    dbms_output.put_line(dbms_metadata.get_ddl(object_type => 'TABLE',
                                               name        => a_r.name,
                                               schema      => IN_owner));
   dbms_output.put_line('=======================================');
    fetch v_cur
      into a_r;
  end loop;
end PAGINATING_all_objects_PP;
begin
  NULL;
end PAGEQUERY_PACK;

call Pagequery_Pack.PAGINATING_all_objects_PP(1,10,'CICI');