如何检查数据库Sequencer与记录中最高ID值是否同步

需求：判断指定序列的CURRVAL是否与某表ID列的最大值一致，一致时返回1，不一致返回0。

示例数据：

当序列CURRVAL为13时返回1，其他值（如1、5、18）返回0。

错误尝试及报错 #

尝试1：WHERE子句中使用聚合函数

SELECT COUNT(*) AS n
FROM "MY_SCHEMA"."MY_TABLE" p
WHERE "MY_SCHEMA"."MY_SEQUENCE".CURRVAL = max(p.id);

报错：

ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
Error at Line: 3 Column: 62

尝试2：HAVING子句中使用序列CURRVAL

SELECT COUNT(*) AS n
FROM "LOGMODGCSS_DATA"."SUFFIX_ITEMS" p
HAVING "LOGMODGCSS_DATA"."SUFFIX_ITEMS_SEQ".CURRVAL = max(id);

报错：

ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.
Error at Line: 3 Column: 45

可行解决方案 #

可以通过子查询获取ID最大值，再与序列CURRVAL做比较，用CASE或DECODE实现返回1/0的逻辑，单行语句即可完成：

方法1：使用CASE表达式

SELECT CASE 
         WHEN "MY_SCHEMA"."MY_SEQUENCE".CURRVAL = (SELECT MAX(id) FROM "MY_SCHEMA"."MY_TABLE")
         THEN 1 
         ELSE 0 
       END AS sync_status
FROM DUAL;

方法2：使用DECODE函数

SELECT DECODE("MY_SCHEMA"."MY_SEQUENCE".CURRVAL, (SELECT MAX(id) FROM "MY_SCHEMA"."MY_TABLE"), 1, 0) AS sync_status
FROM DUAL;

说明

• 利用DUAL表作为查询载体，仅需一次聚合查询获取ID最大值，避免对业务表做无意义的全表扫描
• 子查询先计算出ID列的最大值，再和序列当前值做等值判断，完全符合Oracle语法规则，不会触发之前的报错

内容的提问来源于stack exchange，提问作者Achiral