Check if row exist

Question

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

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;

Answer 1

In this version:

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

... 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;

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;
...

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.

The documentation has a section about name resolution.

Answer 2

You can use label.

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

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

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

end;