Oracle中利用存储过程建表
Oracle中存储过程不可以执行DDL语句,但是我们可以利用动态sql语句来建立表格。
如下:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
技术改变世界