plsql 练习五 批量查询指定方案的建表语句

create or replace procedure BATCH_CHECK_DDL_P(in_username in varchar2 ) is
  /***************************************************************
  *NAME : BATCH_INSERT_TABLEA_P
  *PURPOSE : --批量查询数据库内某个用户的建表语句
  *IMPUT : --  表名子
  *OUTPUT : -- N/A
  *Author : -- CICI
  *CreateDate : -- 2012、12、24
  *UpdateDate : --
  ************************************************************/
V_TABLE_NAMES   VARCHAR2(10000);
cursor cur is select    OBJECT_NAME
   from all_objects
  where OWNER = upper(in_username)
    and object_type = 'TABLE';
begin
 /*查询属于ss_hr用户的全部表的信息*/
  open cur;
  fetch cur into V_TABLE_NAMES;
 
  WHILE cur%FOUND LOOP
  
    exit when not cur%found; --如果游标到尾则结束
 /*遍历获取建表语句*/
 dbms_output.put_line('=================================================');
    dbms_output.put_line
    (dbms_metadata.get_ddl(object_type => 'TABLE',
                            name => upper(V_TABLE_NAMES),
                            schema=>upper(in_username))); 
  dbms_output.put_line('=================================================');  
 
    fetch cur
    into V_TABLE_NAMES;
  END LOOP;

  CLOSE cur;
 
end BATCH_CHECK_DDL_P;