我刚学Oracle写了一个存储过程创建一个表:如下
create or replace procedure CreateTable(CreateTableStr in Varchar2,DropTableStr in Varchar2,TableName in Varchar2)
as
Count_Number Number:=0;
begin
select Count(*) into Count_Number from user_tables where table_name=upper(TableName);
if Count_Number>0 then
execute immediate DropTableStr;
end if;
execute immediate CreateTableStr;
Count_Number:=0;
select Count(*) into Count_Number from user_tables where table_name=upper(TableName);
if Count_Number>0 then
dbms_output.put_line('成功创建'||TableName||'表');
else
dbms_output.put_line('创建'||TableName||'表失败');
end if;
dbms_output.put_line(chr(10));
exception
when others then
dbms_output.put_line('创建'||TableName||'时出现以下错误:'||SQLERRM);
dbms_output.put_line(chr(10));
end CreateTable;
/
参数说明:
CreateTableStr :创建表的语句,如'create table aaa(n number)'
DropTableStr:删除原表,如'drop table aaa'
TableName :表名。如'aaa'调用如下:
exec CreateTable('create table aaa(n number)','drop table aaa','aaa');我刚学Oracle,不对之外,请指出。
create or replace procedure CreateTable(CreateTableStr in Varchar2,DropTableStr in Varchar2,TableName in Varchar2)
as
Count_Number Number:=0;
begin
select Count(*) into Count_Number from user_tables where table_name=upper(TableName);
if Count_Number>0 then
execute immediate DropTableStr;
end if;
execute immediate CreateTableStr;
Count_Number:=0;
select Count(*) into Count_Number from user_tables where table_name=upper(TableName);
if Count_Number>0 then
dbms_output.put_line('成功创建'||TableName||'表');
else
dbms_output.put_line('创建'||TableName||'表失败');
end if;
dbms_output.put_line(chr(10));
exception
when others then
dbms_output.put_line('创建'||TableName||'时出现以下错误:'||SQLERRM);
dbms_output.put_line(chr(10));
end CreateTable;
/
参数说明:
CreateTableStr :创建表的语句,如'create table aaa(n number)'
DropTableStr:删除原表,如'drop table aaa'
TableName :表名。如'aaa'调用如下:
exec CreateTable('create table aaa(n number)','drop table aaa','aaa');我刚学Oracle,不对之外,请指出。
query.sql.add('create table ...');
...
with adoconnection do
begin
query1.close;
query1.sql.clear;
query1.sql.add('begin ');
query1.sql.add('execute immediate ''create table a (col1 varchar2(10))'';');
query1.sql.add('end;');
query1.execsql;
end 还需要grant create any table to 当前用户