检查行是否存在 [英] Check if row exist

问题描述

如果WHERE如下所示，以下PL/SQL代码的行为将有所不同:

The following PL/SQL code behaves differently if the WHERE looks like this:

WHERE USERNAME = 'aaaaaa'

和如果看起来像这样的话则不同:

and differently if looks like this:

WHERE USERNAME = userName

如果userName := 'aaaaaa'，结果为何不相同?我究竟做错了什么? 谢谢！

Why is the result not the same if userName := 'aaaaaa'? What am I doing wrong? Thank you!

declare
  isFound  NUMBER;
  userName VARCHAR2(30);
begin
  isFound  := 0;
  userName := 'aaaaaa';

  SELECT COUNT(*)
    INTO isFound
    FROM MyTable
   WHERE USERNAME = 'aaaaaa' -- userName
     AND ROWNUM = 1;

  IF isFound > 0 THEN
    dbms_output.put_line('Found');
  ELSE
    dbms_output.put_line('Not found');
  END IF;

end;

推荐答案

在此版本中:

  SELECT COUNT(*)
    INTO isFound
    FROM MyTable
   WHERE USERNAME = userName
     AND ROWNUM = 1;

...正在将表的USERNAME列与其自身进行比较，因此它将始终匹配.您没有将其与局部变量进行比较.如果要执行此操作，则需要为变量指定不同的名称:

... the table's USERNAME column is being compared with itself, so it will always match. You are not comparing it with the local variable. If you want to do that, you'll need to give the variable a different name to the column:

declare
  isFound  NUMBER;
  localUserName VARCHAR2(30);
begin
  isFound  := 0;
  userName := 'aaaaaa';

  SELECT COUNT(*)
    INTO isFound
    FROM MyTable
   WHERE USERNAME = localUserName
     AND ROWNUM = 1;

  IF isFound > 0 THEN
    dbms_output.put_line('Found');
  ELSE
    dbms_output.put_line('Not found');
  END IF;

end;

或者按照David Aldridge的建议，使用标签将局部变量与表列区分开来:

Or as David Aldridge suggests, use a label to distinguish the local variable from the table column:

<<local>>
declare
  isFound  NUMBER;
  userName MyTable.USERNAME%TYPE;
begin
  isFound  := 0;
  userName := 'aaaaaa';

  SELECT COUNT(*)
    INTO isFound
    FROM MyTable
   WHERE USERNAME = local.userName
     AND ROWNUM = 1;
...

您也可以对命名块使用该方法；如果这是在函数内部，则可以将局部变量称为function_name.variable_name.由于这是一个匿名块，因此标签在本质上与function_name相同.

You can use that approach with named blocks too; if this was inside a function you could refer to a local variable as function_name.variable_name. Since this is an anonymous block the label plays the same role as function_name would, essentially.

文档中有有关名称解析的部分

这篇关于检查行是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！