oracle存储过程动态建立表、添加字段注释

create or replace procedure pro_createTable(filename varchar2) authid current_user is
  --授予调用者在存储过程中拥有其角色权限
  num number;
  errorException exception; --申明异常  
  errorCode number; --异常代号  
  errorMsg  varchar2(1000); --异常信息  
  flag      varchar2(10);
  v_sql     varchar2(1000);
begin
  select count(1)
    into num
    from user_tables
   where table_name = lower(filename)
      or table_name = Upper(filename);--因表名大小写敏感
  if num > 0 then
    --立即执行
    execute immediate 'drop table ' || filename;
  end if;
  execute immediate 'create table ' || filename || '(
         id VARCHAR2(64)  PRIMARY KEY,
         collecttime long,
         collectvalue number,
         create_date date,
         update_date date,
         create_by varchar2(64),
         update_by varchar2(64)
     )';
  v_sql := 'comment on table  ' || filename || ' is ''采集值''';--注意双引号
  execute immediate v_sql;
  v_sql := 'comment on column ' || filename || '.collecttime is ''采集时间''';
  execute immediate v_sql;
  v_sql := ' comment on column ' || filename || '.collectvalue is ''采集值''';
  execute immediate v_sql;
  commit;
exception
  when errorException then
    errorCode := SQLCODE;
    errorMsg  := SUBSTR(SQLERRM, 1, 200);
    flag      := 'false';
  when others then
    errorCode := SQLCODE;
    errorMsg  := SUBSTR(SQLERRM, 1, 200);
    flag      := 'false';
    dbms_output.put_line(v_sql);
    dbms_output.put_line(errorCode || ',' || errorMsg);
    rollback;
end;