Oracle中存储过程不可以执行DDL语句,但是我们可以利用动态sql语句来建立表格。
如下:
代码
create
or
replace
procedure
spCreateTestTable
is
v_CreateString varchar2 ( 1000 );
begin
declare
v_count number ;
begin
v_count : = 0 ;
select count ( * )
into v_count
from tab
where tname = ' TEST_TABLE ' ;
if v_count = 1 then
dbms_output.put_line( ' test table already exists ' );
v_CreateString : = ' drop table test_table ' ;
execute immediate v_CreateString;
commit ;
else
dbms_output.put_line( ' test table created ' );
end if ;
v_CreateString : = ' create table test_table( ' ||
' aa varchar2(5), ' ||
' bb varchar2(5)) ' ;
execute immediate v_CreateString;
commit ;
exception
when others
then
rollback ;
end ;
end ;
is
v_CreateString varchar2 ( 1000 );
begin
declare
v_count number ;
begin
v_count : = 0 ;
select count ( * )
into v_count
from tab
where tname = ' TEST_TABLE ' ;
if v_count = 1 then
dbms_output.put_line( ' test table already exists ' );
v_CreateString : = ' drop table test_table ' ;
execute immediate v_CreateString;
commit ;
else
dbms_output.put_line( ' test table created ' );
end if ;
v_CreateString : = ' create table test_table( ' ||
' aa varchar2(5), ' ||
' bb varchar2(5)) ' ;
execute immediate v_CreateString;
commit ;
exception
when others
then
rollback ;
end ;
end ;