declare i number; begin select count(1) into i from user_indexes where name='IX_writeenExam'; if i>0 then drop index IX_writeenExam; end if; create NONCLUSTERED index IX_writeenExam on tb1 (IX_writeenExam) with fillfactor=30; end;
Oracle中没有 if exists,你这应该是其他数据的写法,像sql sever就可以这样写
declare i number; begin select count(1) into i from sysindexes where name='IX_writeenExam'; if i>0 then drop index IX_writeenExam; end if; create NONCLUSTERED index IX_writeenExam on tb1 (IX_writeenExam) with fillfactor=30; end; 我写上去了,可是还是错。 drop下面冒红线了。
改成这样 drop index IX_writeenExam on tb1
记混了,上面写的是sqlsever的写法 oracle应该就是 drop index 索引名 你那有红线,显示是什么错?
ORA-06550: line 5, column 13: PLS-00103: Encountered the symbol "DROP" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> <<上面的就是显示的错误信息,可我看不懂。
declare i number; begin select count(1) into i from user_indexes where name='IX_writeenExam'; if i>0 then execute immediate 'drop index IX_writeenExam'; end if; execute immediate 'create NONCLUSTERED index IX_writeenExam on tb1 (IX_writeenExam) with fillfactor=30'; end;
用动态SQL:declare i integer; str varchar2(1000); begin select count(name) into i from sys.indexes where name = 'IX_writeenExam'; if i > 0 then str := 'drop index IX_writeenExam'; execute immediate str; str := 'create NONCLUSTERED index IX_writeenExam on tb1(IX_writeenExam) with fillfactor = 30'; execute immediate str; end if;end;
这次是在 IF 处出红线 ,End处出红线。
declare i number; begin select count(1) into i from sysindexes where name='IX_writeenExam'; if i>0 then drop index IX_writeenExam; end if; create NONCLUSTERED index IX_writeenExam on tb1 (IX_writeenExam) with fillfactor=30; end;
2、if exists的用法貌似没有吧
i number;
begin
select count(1) into i from user_indexes where name='IX_writeenExam';
if i>0 then
drop index IX_writeenExam;
end if;
create NONCLUSTERED index IX_writeenExam
on tb1 (IX_writeenExam)
with fillfactor=30;
end;
begin
select count(1) into i from sysindexes where name='IX_writeenExam';
if i>0 then
drop index IX_writeenExam;
end if;
create NONCLUSTERED index IX_writeenExam
on tb1 (IX_writeenExam)
with fillfactor=30;
end;
我写上去了,可是还是错。 drop下面冒红线了。
drop index IX_writeenExam on tb1
oracle应该就是 drop index 索引名
你那有红线,显示是什么错?
PLS-00103: Encountered the symbol "DROP" when expecting one of the following: begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<上面的就是显示的错误信息,可我看不懂。
i number;
begin
select count(1) into i from user_indexes where name='IX_writeenExam';
if i>0 then
execute immediate 'drop index IX_writeenExam';
end if;
execute immediate 'create NONCLUSTERED index IX_writeenExam
on tb1 (IX_writeenExam)
with fillfactor=30';
end;
i integer;
str varchar2(1000);
begin
select count(name) into i from sys.indexes where name = 'IX_writeenExam';
if i > 0 then
str := 'drop index IX_writeenExam';
execute immediate str;
str := 'create NONCLUSTERED index IX_writeenExam on tb1(IX_writeenExam)
with fillfactor = 30';
execute immediate str;
end if;end;
declare i number;
begin
select count(1) into i from sysindexes where name='IX_writeenExam';
if i>0 then
drop index IX_writeenExam;
end if;
create NONCLUSTERED index IX_writeenExam
on tb1 (IX_writeenExam)
with fillfactor=30;
end;
这个一直就是drop处出红线。
另外,FILLFACTOR参数去掉,Oracle中没有这个用法