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');