blocks|key|5250833|text|如果您想要计算keyvalue的值多次出现的次数，则需要使用子选择：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5250834|SELECT+COUNT(*)+INTO+cntmismatch+
FROM
(+SELECT+keyvalue,+count(*)+FROM+tbl_temp+GROUP+BY+keyvalue+HAVING+count(*)+>+1)|code-block|syntax|javascript|5250835|entityMap^0|7|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

If you want to count how many values of <code>keyvalue</code> occur more than once you need to use a sub-select:

<pre><code>SELECT COUNT(*) INTO cntmismatch 
FROM
( SELECT keyvalue, count(*) FROM tbl_temp GROUP BY keyvalue HAVING count(*) &gt; 1)
</code></pre>

blocks|key|3782715|text|查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3782716|SELECT+COUNT(keyvalue)+INTO+cntmismatch+FROM+tbl_temp+GROUP+BY+keyvalue|code-block|syntax|javascript|3782717|..。将尝试为每个keyvalue在cntmismatch中返回一行，如果表为空，则返回no_data_found，如果存在多个keyvalue，则返回too_many_rows。|offset|length|style|CODE|3782718|添加：|3782719|HAVING+count(keyvalue)+>+1|3782720|..。很明显，这意味着它只会为包含多个条目的任何keyvalue返回值，因此如果表为空或没有keyvalue出现两次，则返回no_data_found；如果多个keyvalue有重复项，则返回too_many_rows。只有当你恰好有一个重复的keyvalue时，它才会起作用。|3782721|您还没有展示其余的逻辑，但是假设您正在根据该返回值执行某些操作，在这种情况下-如果只有一个keyvalue+(这是不太可能的)-您可以省略HAVING子句，只测试值：|3782722|SELECT+COUNT(keyvalue)+INTO+cntmismatch+FROM+tbl_temp+GROUP+BY+keyvalue;
IF+cntmismatch+>+1+THEN
+++...+--+some+processing
END+IF:|3782723|但充其量，它只会告诉您是否存在多次出现的keyvalue；它不会告诉您它们的keyvalue是什么，并且仍然无法处理存在多个and的情况。|3782724|如果您希望检索具有多个匹配行的所有值，并对其执行某些操作，则可以使用游标：|3782725|FOR+dup_keyvalues+IN+(SELECT+keyvalue,+COUNT(*)+AS+cnt+
++++FROM+tbl_temp+GROUP+BY+keyvalue+HAVING+COUNT(*)+>+1)+LOOP
++++...+-+some+processing,+e.g.
++++DBMS_OUTPUT.PUT_LINE('keyvalue+'+%7C%7C+dup_keyvalues.keyvalue
++++++++%7C%7C+'+has+'+%7C%7C+dup_keyvalues.cnt+%7C%7C+'+matches');
++++...
END+LOOP;|3782726|entityMap^0|0|0|9|8|I|B|18|D|1S|8|24|D|0|0|0|O|8|1A|8|1Q|D|28|8|2O|D|3E|8|0|19|8|1X|6|0|0|K|8|12|8|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|16|8|@$I|17|J|18|K|L]|$I|19|J|1A|K|L]|$I|1B|J|1C|K|L]|$I|1D|J|1E|K|L]|$I|1F|J|1G|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1H|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1J|8|@$I|1K|J|1L|K|L]|$I|1M|J|1N|K|L]|$I|1O|J|1P|K|L]|$I|1Q|J|1R|K|L]|$I|1S|J|1T|K|L]|$I|1U|J|1V|K|L]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1W|8|@$I|1X|J|1Y|K|L]|$I|1Z|J|20|K|L]]|9|@]|A|$]]|$1|U|3|V|5|D|7|21|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|22|8|@$I|23|J|24|K|L]|$I|25|J|26|K|L]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|27|8|@]|9|@]|A|$]]|$1|10|3|11|5|D|7|28|8|@]|9|@]|A|$E|F]]|$1|12|3|-4|5|6|7|29|8|@]|9|@]|A|$]]]|13|$]]

The query:

<pre><code>SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue
</code></pre>

... would attempt to return one row into <code>cntmismatch</code> for every <code>keyvalue</code>, which would fail with <code>no_data_found</code> if the table was empty and with <code>too_many_rows</code> if there was more than one <code>keyvalue</code>.

Adding:

<pre><code>HAVING count(keyvalue) &gt; 1
</code></pre>

... means it will only return a value for any <code>keyvalue</code> with more than one entry, clearly, so now you'll get <code>no_data_found</code> if the table is empty or if no <code>keyvalue</code> appears twice; and <code>too_many_rows</code> if more than one <code>keyvalue</code> has duplicates. It will only work if you have exactly one <code>keyvalue</code> that has duplicates.

You haven't shown the rest of your logic, but presumably you're doing something based on that returned value, in which case - if there was only one <code>keyvalue</code>, which is unlikely - you could leave out the <code>HAVING</code> clause and just test the value:

<pre><code>SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue;
IF cntmismatch &gt; 1 THEN
 ... -- some processing
END IF:
</code></pre>

But at best, all it's telling you is whether there is any <code>keyvalue</code> that appears more than once; it doesn't tell you what they <code>keyvalue</code> is, and still can't cope with there being more than one.

If you want to retrieve all the values that have more than one matching row and do something with them then you could use a cursor:

<pre><code>FOR dup_keyvalues IN (SELECT keyvalue, COUNT(*) AS cnt 
 FROM tbl_temp GROUP BY keyvalue HAVING COUNT(*) &gt; 1) LOOP
 ... - some processing, e.g.
 DBMS_OUTPUT.PUT_LINE('keyvalue ' || dup_keyvalues.keyvalue
 || ' has ' || dup_keyvalues.cnt || ' matches');
 ...
END LOOP;
</code></pre>

blocks|key|5250918|text|您只需在代码中捕获并处理异常即可。在您的例子中，您将得到一个NO_DATA_FOUND异常。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5250919|下面你会看到一个例子。|5250920|PROCEDURE+count_mismatch+AS
++cntmismatch+NUMBER;
BEGIN
++BEGIN
++++SELECT+COUNT(keyvalue)+INTO+cntmismatch+FROM+tbl_temp+GROUP+BY+keyvalue;
++EXCEPTION
++WHEN+no_data_found+THEN
++++cntmismatch+:=+-1;
++END;
END+|code-block|syntax|javascript|5250921|我认为，你不再需要HAVING子句了。因此，我删除了它。如果您的语句将返回超过预期的1行，那么您还必须处理TOO_MANY_ROWS异常！|5250922|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You should simply catch and handle the exception in your code.
In your case, you will get a NO_DATA_FOUND exception.

Below you will find an example.

<pre><code>PROCEDURE count_mismatch AS
 cntmismatch NUMBER;
BEGIN
 BEGIN
 SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue;
 EXCEPTION
 WHEN no_data_found THEN
 cntmismatch := -1;
 END;
END 
</code></pre>

I argue, you don't need the HAVING clause anymore. Therefore I have removed it.
If your statement will return more than the expected 1 row, then you have to handle also the TOO_MANY_ROWS exception!

blocks|key|3782768|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3782769|BEGIN
+++SELECT+COUNT(keyvalue)+INTO+cntmismatch+
++++FROM+tbl_temp+GROUP+BY+keyvalue+HAVING+count(keyvalue)+>+1
EXCEPTION
++WHEN+no_data_found+THEN+cntmismatch+:=+0;
END;|code-block|syntax|javascript|3782770|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try this

<pre><code>BEGIN
 SELECT COUNT(keyvalue) INTO cntmismatch 
 FROM tbl_temp GROUP BY keyvalue HAVING count(keyvalue) &gt; 1
EXCEPTION
 WHEN no_data_found THEN cntmismatch := 0;
END;
</code></pre>

There is an issue with my stored procedure.
It fails in the below case

<pre><code>cntmismatch NUMBER;

SELECT COUNT(keyvalue) INTO cntmismatch 
FROM tbl_temp GROUP BY keyvalue HAVING count(keyvalue) &gt; 1
</code></pre>

When there is no data for the above criteria , it fails

I even tried using 

<pre><code>SELECT NVL(COUNT(keyvalue),0) INTO cntmismatch 
FROM tbl_temp GROUP BY keyvalue HAVING count(keyvalue) &gt; 1
</code></pre>

But still it retuns me null here and proc fails

Failure in Oracle Stored Procedure

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的存储过程有问题。在以下情况下失败cntmismatch NUMBER;SELECT COUNT(keyvalue) INTO cntmismatch FROM tbl_temp GROUP BY keyvalue HAVING count(keyvalue) > 1如果没有满足上述条件的数据，则失败我甚至试着用SE...

问Oracle存储过程中出现故障
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle存储过程中出现故障EN