posts - 64,  comments - 9,  trackbacks - 0

--动态sql创建ORACLE存储过程.txt
create or replace procedure p3(v_tname varchar2) as
  i      number;
  m      number;
  /*v_tname varchar2(10) := 't1';*/
  e_createerror exception;
begin
execute immediate 'select count(*) from all_tables where table_name =''' ||
          upper(v_tname) || '''' into i;
  if i > 0 then
    dbms_output.put_line('table is also exists');
    execute immediate 'drop table ' || v_tname;
  end if;
  begin
    execute immediate 'create table ' || v_tname || ' (id1 number)';
  exception
    when others then
      raise e_createerror;
  end;
  execute immediate 'insert into ' || v_tname || '  values (3)';
  execute immediate 'insert into ' || v_tname || '  values (3)';
  execute immediate 'insert into ' || v_tname || '  values (3)';
  execute immediate 'insert into ' || v_tname || '  values (3)';
  commit;
  execute immediate ' select count(*) from all_tables where table_name =''' ||upper(v_tname) || ''''
    into m;

  if m > 0 then
    --execute immediate ' select count(*) from '''||upper(v_tname) || '''' into m;
    dbms_output.put_line('good1!');dbms_output.put_line('count of v_tname:'||m);
  end if;
exception
  when e_createerror then
    dbms_output.put_line('表创建语句出错请检查');
end;
/

posted on 2009-07-21 14:15 super_nini 阅读(912) 评论(0)  编辑  收藏

只有注册用户登录后才能发表评论。


网站导航:
 
<2009年7月>
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

常用链接

留言簿

随笔档案

文章档案

相册

搜索

  •  

最新评论

阅读排行榜

评论排行榜