过程功能是每天创建一张新表,表明是A_YYYYMMDD,创建前先检查是否存在本日表,存在就删除。create or replace procedure sp_createtab_tbl_programme Authid Current_User as
tabname varchar(20);
str1 varchar(100);
str2 varchar(100);
n nunmber;
begin
select 'A_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
str1 := 'drop table ' || tabname || ' ';
str2 := 'create table ' || tabname || ' as select * from A where 1 != 1';
select count(*) into n from user_tables where table_name= '||tabname||';
if n!=0 then
execute immediate str1;
endif;
execute immediate str2;
commit;
end;报错如下:Compilation errors for PROCEDURE SYSTEM.SP_CREATETAB_TBL_PROGRAMMEError: PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
Line: 23
Text: end;请各位帮忙解释,非常感谢。
tabname varchar(20);
str1 varchar(100);
str2 varchar(100);
n nunmber;
begin
select 'A_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
str1 := 'drop table ' || tabname || ' ';
str2 := 'create table ' || tabname || ' as select * from A where 1 != 1';
select count(*) into n from user_tables where table_name= '||tabname||';
if n!=0 then
execute immediate str1;
endif;
execute immediate str2;
commit;
end;报错如下:Compilation errors for PROCEDURE SYSTEM.SP_CREATETAB_TBL_PROGRAMMEError: PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
Line: 23
Text: end;请各位帮忙解释,非常感谢。
tabname varchar(20);
str1 varchar(100);
str2 varchar(100);
n nunmber;
begin
select 'A_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
str1 := 'drop table ' || tabname || ' ';
str2 := 'create table ' || tabname || ' as select * from A where 1 != 1';
select count(*) into n from user_tables where table_name= '||tabname||';
if n!=0 then
execute immediate str1;
else
execute immediate str2;
end if;
commit;
end;
tabname varchar(20);
str1 varchar(100);
--str2 varchar(100);
n int;
begin
select 'A_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
str1 := 'drop table '||tabname||'';
--str2 := 'create table ' || tabname || ' as select * from A where 1 != 1';
select count(*) into n from user_tables where table_name= '||tabname||';
if n!=0 then
execute immediate str1;
commit;
end if;
commit;
end;代码改为如上,发现判断语句没有起作用,不能删除表。请再帮忙看看!!!
你select count(*) into n from user_tables where table_name= '||tabname||';
查询不到表!
建议你这样做:
select count(*) into n from user_tables where table_name= upper('||tabname||')';
你的是A_20100918
你再查查
select 'A_' || to_char(sysdate, 'yyyymmdd') from dual;
结果是A_20100919 ,而不是A_20100918
还有,如果你是system账号登陆的,那么你直接
select * from user_tables where table_name='A_20100918'; 是否有记录啊?
结果是A_20100918.
select * from user_tables where table_name='A_20100918';
有记录。唉,真不知道怎么回事!谢谢你的回复。
str1 := 'drop table '||tabname||'';改成试试:str1 := 'drop table '||tabname;
--或者这样改试下
create or replace procedure sp_createtab_tbl_programme2 Authid Current_User as
tabname varchar(20);
str1 varchar(100);
--str2 varchar(100);
n int;
begin
select 'A_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
str1 := 'drop table :1';
--str2 := 'create table ' || tabname || ' as select * from A where 1 != 1';
select count(*) into n from user_tables where table_name= '||tabname||';
if n!=0 then
execute immediate str1 using tabname ;
commit;
end if;
commit;
end;
哦 我知道了!
查询语句要这样改:select count(*) into n from user_tables where table_name= '''||tabname||''';保证这下OK了
这句话也要用动态SQL去拼:sql_str:= 'select count(*) from user_tables where table_name='''||tabname||'''';
execute immediate sql_str into n;