Oracle 10g下PL/SQL块SQL查询忽略WHERE条件问题求助

问题详情 #

创建EMPP表并插入单条记录后，直接SQL查询结果正常，但PL/SQL块无论输入什么薪资值，都会返回薪资为2850的员工记录，完全忽略WHERE条件。

表结构与初始化语句 #

CREATE TABLE EMPP(
  EMPNO NUMBER PRIMARY KEY,
  ENAME VARCHAR2(15),
  JOB VARCHAR2(15),
  SAL NUMBER(7,2)
)

INSERT INTO EMPP VALUES(1, 'A','B',2850);

问题PL/SQL代码 #

DECLARE
same_sal EXCEPTION;
sal EMPP.SAL%TYPE := :Enter_Salary;
no EMPP.EMPNO%TYPE;
ename EMPP.ENAME%TYPE;
job EMPP.JOB%TYPE;
total_row NUMBER;
BEGIN
SELECT COUNT(*) INTO total_row FROM EMPP WHERE SAL = sal;
    dbms_output.put_line(sal);
IF (total_row > 1) THEN
    raise same_sal;
ELSE
    SELECT EMPNO, ENAME, JOB INTO no, ename, job FROM EMPP WHERE SAL = sal;

    dbms_output.put_line('No :  ' || no);
    dbms_output.put_line('Emp Name :  ' || ename);
    dbms_output.put_line('Job :  ' || job);
END IF;

EXCEPTION
WHEN same_sal THEN
    dbms_output.put_line('There are more than 1 employee with same salary');
END;

异常现象 #

• 直接SQL查询结果符合预期：

  SELECT COUNT(*) FROM EMPP WHERE SAL = 2850;  -- 返回1
  SELECT COUNT(*) FROM EMPP WHERE SAL = 100;   -- 返回0
  SELECT COUNT(*) FROM EMPP WHERE SAL = 1000;  -- 返回0
  

• PL/SQL块中，无论绑定变量:Enter_Salary输入100、1000等值，均返回薪资2850的员工记录。

问题原因 #

PL/SQL块中声明的变量sal与表EMPP的字段名SAL完全同名。Oracle解析SQL语句时，会优先将WHERE SAL = sal中的两个标识符都解析为表字段，等价于执行WHERE SAL = SAL——这会匹配表中所有非空薪资的记录，因此始终返回唯一存在的那条2850薪资的记录。

解决方案 #

修改PL/SQL变量名，避免与表字段名冲突。建议给变量添加统一前缀（如v_表示局部变量），修改后的代码如下：

DECLARE
same_sal EXCEPTION;
v_sal EMPP.SAL%TYPE := :Enter_Salary; -- 修改变量名
no EMPP.EMPNO%TYPE;
ename EMPP.ENAME%TYPE;
job EMPP.JOB%TYPE;
total_row NUMBER;
BEGIN
SELECT COUNT(*) INTO total_row FROM EMPP WHERE SAL = v_sal; -- 使用新变量名
    dbms_output.put_line(v_sal);
IF (total_row > 1) THEN
    raise same_sal;
ELSE
    SELECT EMPNO, ENAME, JOB INTO no, ename, job FROM EMPP WHERE SAL = v_sal; -- 使用新变量名

    dbms_output.put_line('No :  ' || no);
    dbms_output.put_line('Emp Name :  ' || ename);
    dbms_output.put_line('Job :  ' || job);
END IF;

EXCEPTION
WHEN same_sal THEN
    dbms_output.put_line('There are more than 1 employee with same salary');
END;

额外建议 #

编写PL/SQL代码时，养成给变量、参数添加前缀的习惯（如v_表示局部变量，p_表示输入参数），可以有效避免此类命名冲突问题。

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