如果IBM Db2中存在if语法 [英] Syntax of if exists in IBM Db2
问题描述
如果表存在,则下列查询将丢弃一个表,但似乎不适用于IBM Db2。
开始原子
if(exists(
SELECT 1 FROM SYSIBM.SYSTABLES
WHERE NAME ='EMAIL'AND TYPE = 'T'AND creator ='schema1'
))然后
drop表EMAIL;
end if;
结束
而如果我有一个DML语句而不是表drop语句,则exisits语法可以使用相同的语句。更新1:我读到你不能在开始原子块中运行DDL语句,所以我的第一个语句失败,但第二个更好。
我的方式如下:
开始原子
if(exists(SELECT 1
FROM SYSIBM.SYSTABLES
WHERE NAME ='EMAIL'AND TYPE ='T'AND creator = 'schema1'
)
)
then customStoredproc('drop table EMAIL');
end if;
结束
我的customStoredProc只有一个stmt执行立即@dynsql; / p>
The follow query drops a table if the table exists but it doesnt seem to work for IBM Db2.
Begin atomic
if( exists(
SELECT 1 FROM SYSIBM.SYSTABLES WHERE NAME='EMAIL' AND TYPE='T' AND creator = 'schema1' )) then drop table EMAIL; end if; End
Whereas the same if exisits syntax works if i have a DML statement instead of table drop statement. Any help on this is appreciated
Update 1: I read that you cannot run DDL statement within begin atomic block hence my first statement fails but the second goes fine.
The way i did it is as follows
Begin atomic
if( exists( SELECT 1
FROM SYSIBM.SYSTABLES
WHERE NAME='EMAIL' AND TYPE='T' AND creator = 'schema1'
)
)
then customStoredproc('drop table EMAIL');
end if;
End
My customStoredProc just has one stmt execute immediate @dynsql;
这篇关于如果IBM Db2中存在if语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!