错误信息如下: 各位帮忙看看,是不是SQL脚本里不能有if then else ? ORA-06550: 第 43 行, 第 10 列: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall <a single-quoted SQL string>
你不能直接写create table之类的,要用动态SQL。例如,在存储过程中创建表TEMP declare v_str varchar(1000); begin if ... then v_str :=' CREATE TABLE TEMP (a number(10)) '; execute immediate v_str; end if; end; /
写过程嘛。 create procedure info as m number; n number; str varchar2(100); begin select count(1) into m from dual where exists(select 1 from user_table where table_name='表名') if m=0 then str:='create table 表名(....)'; execute immediate str; else select count(1) into n from dual where exists(select 1 from user_ind_columns where COLUMN_NAME='列名'; if n=0 then str:='create index index_name on table_name(列名)'; execute immediate str; end if; end if; ...... end; /
ORA-06550: 第 43 行, 第 10 列:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
declare
v_str varchar(1000);
begin
if ... then
v_str :=' CREATE TABLE TEMP (a number(10)) ';
execute immediate v_str;
end if;
end;
/
create procedure info
as
m number;
n number;
str varchar2(100);
begin
select count(1) into m from dual where exists(select 1 from user_table where table_name='表名')
if m=0 then
str:='create table 表名(....)';
execute immediate str;
else
select count(1) into n from dual where exists(select 1 from user_ind_columns where COLUMN_NAME='列名';
if n=0 then
str:='create index index_name on table_name(列名)';
execute immediate str;
end if;
end if;
......
end;
/
ORA-06550: 行25、列14:
PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时?
:=.(@%;