informix 检查表是否存在然后读取值
我在 informix（版本 11.50.UC4）中有一个名为 NextRecordID 的表，其中只有一列名为 id 的列，并且它将有一行。 我想要做的是将这个值复制到另一个表中。 但如果该表不存在，我不希望我的查询失败。 就像是
if table NextRecordID exists
then insert into sometable values ('NextRecordID', (select id from NextRecordID))
else insert into sometable values ('NextRecordID', 1)
I have a table in informix (Version 11.50.UC4) called NextRecordID with just one column called id and it will have one row. What I want to do is copy this value into another table. But don't want my query to fail if this table does not exist. Something like
if table NextRecordID exists
then insert into sometable values ('NextRecordID', (select id from NextRecordID))
else insert into sometable values ('NextRecordID', 1)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我最终使用了下面的 SQL 查询。 它不是 ANSI SQL，但适用于我正在使用的 informix 服务器。
这里发生的事情是在
insert查询中，我使用select查询获取要插入的值。 现在select查询很有趣。 它使用 Informix 的case语句。 我编写了一个select查询来检查nextrecordid表是否存在于systables中，如果存在则返回 1。 如果此查询返回 1，我会在表nextrecordid中查询该值，否则我会编写一个查询来返回默认值 1。这对我有用。I ended up using the below SQL query. Its not ANSI SQL but works the informix server I am using.
What is happening here is within
insertquery I get the value to be inserted by usingselectquery. Now thatselectquery is interesting. It usescasestatement of Informix. I have written aselectquery to check if the tablenextrecordidexists insystablesand return 1 if it exists. If this query returns 1, I query the tablenextrecordidfor the value or else I wrote a query to return the default value 1. This work for me.您应该能够通过检查
systables表来完成此操作。You should be able to do this by checking the
systablestable.感谢您提供服务器版本信息 - 它使回答您的问题变得更加容易。
您尚未表明您正在使用哪种语言。
不过，通常情况下，您设计的程序会期望某个模式（存在某些表），然后如果这些表不存在，则失败（最好是在受控制的情况下）。 此外，还不清楚是否会因为重复执行第二个 INSERT 语句而遇到问题。 也不清楚 NextRecordID 表何时更新 - 据推测，一旦使用该值，就必须更新它。
您应该查看 SERIAL (BIGSERIAL) 并看看它是否适合您。
您还应该看看 SEQUENCE 是否适合在这里使用 - 它看起来确实很适用。
正如 Adam Hughes 指出的那样，如果您想检查数据库中是否存在 NextRecordID 表，您可以查看 systables 表。 但请注意，您的搜索需要针对全小写的名称 (nextrecordid)。
此外，MODE ANSI 数据库使生活变得复杂 - 您必须担心表的所有者（因为 MODE ANSI 数据库中可能有多个名为 nextrecordid 的表）。 最有可能的是，您不必担心这一点 - 就像您可能不必担心表“someone”.“NextRecordID”（这是与 person.NextRecordID 不同的表）的分隔标识符一样。
Thank you for including server version information - it makes answering your question easier.
You've not indicated which language(s) you are using.
Normally, though, you design a program to expect a certain schema (certain tables to be present), and then fail - preferably under control - if those tables are not present. Also, it is not clear whether you would get into problems because of repeated execution of the second INSERT statement. Nor is it clear when the NextRecordID table is updated - presumably, once the value has been used, it must be updated.
You should look at SERIAL (BIGSERIAL) and see whether that is appropriate for you.
You should also look at whether a SEQUENCE would be appropriate to use here - it certainly looks rather like it might be applicable.
As Adam Hughes points out, if you want to check whether the NextRecordID table is present in the database, you would look in the systables table. Be aware, though, that your search will need to be against an all lower-case name (nextrecordid).
Also, MODE ANSI databases complicate life - you have to worry about the table's owner (because there could be multiple tables called nextrecordid in a MODE ANSI database). Most likely, you don't have to worry about that - any more than you are likely to have to worry about delimited identifiers for table "someone"."NextRecordID" (which is a different table from someone.NextRecordID).