本人新手,要做一个存储过程传入参数,通过参数作为表名建表,判断这个表是否存在,如果不存在,创建该表,如果存在,先删除掉该表,然后创建该表
create or replace procedure ddltest
( tbl_name in varchar2)
authid current_user as
num number;
begin
select count(1) into num from user_tables where table_name=tbl_name;
if num=1 then
execute immediate 'drop table '||tbl_name;
end if;
execute immediate 'create table'||tbl_name||'(t1 date,
t2 varchar2(10))';
end;begin
ddltest('TEST_LIKE');
end;报错:ORA 00901:无效CREATE命令
create or replace procedure ddltest
( tbl_name in varchar2)
authid current_user as
num number;
begin
select count(1) into num from user_tables where table_name=tbl_name;
if num=1 then
execute immediate 'drop table '||tbl_name;
end if;
execute immediate 'create table'||tbl_name||'(t1 date,
t2 varchar2(10))';
end;begin
ddltest('TEST_LIKE');
end;报错:ORA 00901:无效CREATE命令
t2 varchar2(10))';
测了下,这个 'create table' table后面加个空格 'create table '。
好建议!!!
我再给个建议,如果表很多,而且字段不一样的话,建议弄个配置表!
'create table'||tbl_name||'(t1 date,t2 varchar2(10))';
拼接出来的结果是
create tabletbl_name(t1 date,t2 varchar2(10));