COUNT(*)=0 计算结果为 true？

发布于 2024-12-21 09:22:57 字数 574 浏览 2 评论 0原文

正如标题所说。我在 if 语句中有一条 SQL 语句 (Oracle 9)，其计算结果为 true。据我所知，这样的语句将执行 SQL Server 中所期望的操作。在这种情况下我做错了什么？

IF (SELECT COUNT(*) FROM CUSTOMER_LIST WHERE CUSTOMER='Target')=0 THEN  
INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target')

或者更好的是，确保我不插入已经存在的数据的适当策略是什么。所编写的代码始终会插入。这显然不是我想要的，因为这一行已经存在。

当此代码运行时，我收到以下控制台消息。

Error starting at line 1 in command:
IF (SELECT COUNT(*) FROM CUSTOMER_LIST WHERE CUSTOMER='Target')=0 THEN
Error report:
Unknown Command
1 rows inserted.

原文

Just like the title says. I have a SQL statement (Oracle 9) in an if statement that evaluates to true. From what I recall such a statement would do what is expected in SQL Server. What am I doing wrong in this case?

IF (SELECT COUNT(*) FROM CUSTOMER_LIST WHERE CUSTOMER='Target')=0 THEN  
INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target')

or better yet, what is an appropriate strategy to ensure that I do not insert data that already exists. The code as written always inserts. This obviously is not what I want since this row exists already.

When this code runs I get the following console message.

Error starting at line 1 in command:
IF (SELECT COUNT(*) FROM CUSTOMER_LIST WHERE CUSTOMER='Target')=0 THEN
Error report:
Unknown Command
1 rows inserted.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

比忠 2024-12-28 09:22:57

问题似乎是你在它期望直接 SQL 的地方给它 PL/SQL。要么直接给出 SQL:，

INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER)
SELECT CUSTOMER_ID_SEQ.nextval, 'Target' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM CUSTOMER_LIST WHERE CUSTOMER = 'Target')

要么更改周围的上下文，以便 Oracle 期望 PL/SQL。这将取决于您如何传递此代码；但一种可能的方法是将其包装在 BEGIN ... END 块中。

此外，您不能将查询用作 PL/SQL 中的表达式；如果您采用 PL/SQL 路线，则需要将结果存储在某个地方，例如变量中。总而言之，你可能会遇到这样的情况：

DECLARE
  v_customer_exists INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_customer_exists FROM CUSTOMER_LIST WHERE CUSTOMER='Target';
  IF v_customer_exists = 0 THEN
    INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
    (CUSTOMER_ID_SEQ.nextval, 'Target');
  END IF;
END;
/

The problem seems to be that you're giving it PL/SQL where it's expecting straight SQL. Either give it straight SQL:

INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER)
SELECT CUSTOMER_ID_SEQ.nextval, 'Target' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM CUSTOMER_LIST WHERE CUSTOMER = 'Target')

or else change the surrounding context so that Oracle will expect PL/SQL. That will depend on how you're passing this code in; but one likely way is to wrap it in a BEGIN ... END block.

Additionally, you can't use a query as an expression in PL/SQL; if you go the PL/SQL route, you'll need to store the result somewhere, such as in a variable. All told, you might have this:

DECLARE
  v_customer_exists INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_customer_exists FROM CUSTOMER_LIST WHERE CUSTOMER='Target';
  IF v_customer_exists = 0 THEN
    INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
    (CUSTOMER_ID_SEQ.nextval, 'Target');
  END IF;
END;
/

回复收藏 0 原文

悲凉≈ 2024-12-28 09:22:57

    DECLARE
       v_count number;

    BEGIN
    SELECT COUNT(*) INTO v_count FROM CUSTOMER_LIST WHERE CUSTOMER='Target';

    IF v_count = 0 THEN
      INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target');
    END IF;
    END;

    DECLARE
       v_count number;

    BEGIN
    SELECT COUNT(*) INTO v_count FROM CUSTOMER_LIST WHERE CUSTOMER='Target';

    IF v_count = 0 THEN
      INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target');
    END IF;
    END;

回复收藏 0 原文

狠疯拽 2024-12-28 09:22:57

尝试类似的操作来检查是否从选择中返回了任何行。当选择没有返回元组时，NOT EXISTS 返回 true：

IF  NOT EXISTS (SELECT * FROM CUSTOMER_LIST WHERE CUSTOMER='Target') THEN  
INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target')

Try something like this to check if any rows are returned from the selection. NOT EXISTS returns true when no tuples were returned from the select:

IF  NOT EXISTS (SELECT * FROM CUSTOMER_LIST WHERE CUSTOMER='Target') THEN  
INSERT INTO CUSTOMER_LIST (CUSTOMER_ID, CUSTOMER) VALUES
(CUSTOMER_ID_SEQ.nextval, 'Target')

回复收藏 0 原文

~没有更多了~