declare v_countone int; begin select count(1) into v_countone --`select count(1) from user_tables b where b.table_name='SHISHIBIAO' ;
-- end; if(v_countone <> 0) then execute immediate 'drop table SHISHIBIAO'; else execute immediate 'create table shishibiao ( id int not null, subject int not null, begin date, lend date )'; end if;
commit;
end; 这部分drop table和create table 都能执行成功。insert into SHISHIBIAO(id,subject,begin,lend) values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd')); commit; 如果加上这部分一起执行,那么会提示表与视图不存在。如果这2部分单独运行都能执行成功
我先判断要执行的表存在不存在,如果在就drop,然后create,继续insert into 不在就直接create,继续insert into。 这逻辑有什么问题么
如果在,你就drop,但没重建~~
我先判断要执行的表存在不存在,如果在就drop,然后create,继续insert into 不在就直接create,继续insert into。 这逻辑有什么问题么 drop后create代码呢?没贴出来?
declare v_countone int; begin select count(1) into v_countone --select count(1) from user_tables b where b.table_name=upper('shishibiao') ;
-- end; if(v_countone <> 0) then execute immediate 'drop table shishibiao'; end if;
/*end;*/ execute immediate 'create table shishibiao ( id int not null, subject int not null, begind date, lend date )'; commit; /*end;*/ insert into shishibiao(id,subject,begind,lend) values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd')); end;代码改了下,但是还是有问题
declare v_countone int; begin select count(1) into v_countone --select count(1) from user_tables b where b.table_name=upper('shishibiao') ;
-- end; if(v_countone <> 0) then execute immediate 'drop table shishibiao'; end if;
/*end;*/ execute immediate 'create table shishibiao ( id int not null, subject int not null, begind date, lend date )'; commit; /*end;*/ insert into shishibiao(id,subject,begind,lend) values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd')); end;代码改了下,但是还是有问题 当shishibiao表本来就存在的话,执行该代码不会出错,如果shishibiao表不在,就会有问题
declare v_countone int; begin select count(1) into v_countone --select count(1) from user_tables b where b.table_name=upper('shishibiao') ;
-- end; if(v_countone <> 0) then execute immediate 'drop table shishibiao'; end if;
/*end;*/ execute immediate 'create table shishibiao ( id int not null, subject int not null, begind date, lend date )'; commit; /*end;*/ insert into shishibiao(id,subject,begind,lend) values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd')); end;代码改了下,但是还是有问题你最后insert into 在编译的时候回去检查表是否存在。可以改为: execute immediate 'insert into shishibiao(id,subject,begind,lend) values(1,1,to_date(''2013-12-17'',''yyyy-mm-dd''),to_date(''2009-11-11'',''yyyy-mm-dd''))';
你最后insert into 在编译的时候回去检查表是否存在。可以改为: execute immediate 'insert into shishibiao(id,subject,begind,lend) values(1,1,to_date(''2013-12-17'',''yyyy-mm-dd''),to_date(''2009-11-11'',''yyyy-mm-dd''))';谢谢了,自学太痛苦了
当你表存在的时候drop表?后面插入到哪里?
v_countone int;
begin
select count(1) into v_countone
--`select count(1)
from user_tables b
where b.table_name='SHISHIBIAO' ;
-- end;
if(v_countone <> 0) then
execute immediate 'drop table SHISHIBIAO';
else
execute immediate 'create table shishibiao
(
id int not null,
subject int not null,
begin date,
lend date
)';
end if;
commit;
end;
这部分drop table和create table 都能执行成功。insert into SHISHIBIAO(id,subject,begin,lend)
values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd'));
commit;
如果加上这部分一起执行,那么会提示表与视图不存在。如果这2部分单独运行都能执行成功
我先判断要执行的表存在不存在,如果在就drop,然后create,继续insert into
不在就直接create,继续insert into。
这逻辑有什么问题么
我先判断要执行的表存在不存在,如果在就drop,然后create,继续insert into
不在就直接create,继续insert into。
这逻辑有什么问题么
drop后create代码呢?没贴出来?
declare
v_countone int;
begin
select count(1) into v_countone
--select count(1)
from user_tables b
where b.table_name=upper('shishibiao') ;
-- end;
if(v_countone <> 0) then
execute immediate 'drop table shishibiao';
end if;
/*end;*/
execute immediate 'create table shishibiao
(
id int not null,
subject int not null,
begind date,
lend date
)';
commit;
/*end;*/
insert into shishibiao(id,subject,begind,lend)
values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd'));
end;代码改了下,但是还是有问题
declare
v_countone int;
begin
select count(1) into v_countone
--select count(1)
from user_tables b
where b.table_name=upper('shishibiao') ;
-- end;
if(v_countone <> 0) then
execute immediate 'drop table shishibiao';
end if;
/*end;*/
execute immediate 'create table shishibiao
(
id int not null,
subject int not null,
begind date,
lend date
)';
commit;
/*end;*/
insert into shishibiao(id,subject,begind,lend)
values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd'));
end;代码改了下,但是还是有问题
当shishibiao表本来就存在的话,执行该代码不会出错,如果shishibiao表不在,就会有问题
把insert写成动态语句就好了,然后在最后别忘了加commit。另外insert前面那个commit是不需要的,因为你执行的是ddl语句,即使是动态sql,也会自动提交。
declare
v_countone int;
begin
select count(1) into v_countone
--select count(1)
from user_tables b
where b.table_name=upper('shishibiao') ;
-- end;
if(v_countone <> 0) then
execute immediate 'drop table shishibiao';
end if;
/*end;*/
execute immediate 'create table shishibiao
(
id int not null,
subject int not null,
begind date,
lend date
)';
commit;
/*end;*/
insert into shishibiao(id,subject,begind,lend)
values(1,1,to_date('2013-12-17','yyyy-mm-dd'),to_date('2009-11-11','yyyy-mm-dd'));
end;代码改了下,但是还是有问题你最后insert into 在编译的时候回去检查表是否存在。可以改为: execute immediate 'insert into shishibiao(id,subject,begind,lend)
values(1,1,to_date(''2013-12-17'',''yyyy-mm-dd''),to_date(''2009-11-11'',''yyyy-mm-dd''))';
values(1,1,to_date(''2013-12-17'',''yyyy-mm-dd''),to_date(''2009-11-11'',''yyyy-mm-dd''))';谢谢了,自学太痛苦了
如果像我这样用execute immediate 创建表,那么插入数据也要用动态sql吗?
如果像我这样用execute immediate 创建表,那么插入数据也要用动态sql吗?
由于你的动态语句必须在执行是才有意义,所以在后面写插入数据sql的时候编译检查的时候会通不过、