前端之家收集整理的这篇文章主要介绍了
用oracle存储过程创建表,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
create or replace procedure createtable(tname in varchar2)
is
sqlTEXT varchar2(400);
v_createsql varchar2(400);
v_dropsql varchar2(100);
v_count number(9);
begin
sqlTEXT := 'grant create any table to newsname'; --newsname为数据库用户名,这里需要给他创建表的权限
EXECUTE IMMEDIATE sqlTEXT;
v_createsql:='create table '||tname||'(
a number(8) primary key,b varchar2(20))';
v_dropsql:='drop table '||tname||' cascade constraints';
select count(*) into v_count from user_tables where table_name=upper(tname);
if v_count>0 then
execute immediate v_dropsql;
commit;
end if;
execute immediate v_createsql;
commit;
end;