Column Name Precedence

在PL/SQL编程中, 如果SQL语句用到的变量名或形参名与字段名相同, 则解释为字段名. 出现非预想的结果.

当然平常保持好的编程风格很重要, 避免出现这种情形. 下面是示范这种情形及解决办法.

下文来自Oracle Database PL/SQL Language Reference 11g Release 2 (11.2)

Column Name Precedence

If a SQL statement references a name that belongs to both a column and either a local variable 

or formal parameter, then the column name takes precedence.

In Example B–2, the name last_name belongs to both a local variable and 

a column (names are not case-sensitive).Therefore, in the WHERE clause, 

both references to last_name resolve to the column, and all rows are deleted.

Example B–2 Variable Name Interpreted as Column Name Causes Unintended Result

DROP TABLE employees2;

 

CREATE TABLE employees2 AS 

 SELECT LAST_NAME FROM employees; 

 

DECLARE 

   last_name VARCHAR2(10) := 'King';

BEGIN 

   DELETE FROM employees2 WHERE LAST_NAME = last_name; -- 删掉的是整个表的数据呵! 

   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');

END;

/ 

Result:

Deleted 107 rows.

Example B–3 solves the problem in Example B–2 by giving the variable a different name.

Example B–3 Fixing Example B–2 with Different Variable Name

DECLARE 

   v_last_name VARCHAR2(10) := 'King';

BEGIN 

   DELETE FROM employees2 WHERE LAST_NAME = v_last_name; 

   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');

END;

/ 

Result:

Deleted 2 rows.

Example B–4 solves the problem in Example B–2 by labeling the block and qualifying 

the variable name with the block name.

Example B–4 Fixing Example B–2 with Block Label

<<main>>

DECLARE 

   last_name VARCHAR2(10) := 'King';

BEGIN 

   DELETE FROM employees2 WHERE last_name = main.last_name; 

   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');

END;

/ 

Result:

Deleted 2 rows.

In Example B-5, the the function dept_name has a formal parameter and a local variable whose names are 

those of columns of the table DEPARTMENTS. The parameter and variable name are qualified 

with the function name to distinguish them from the column names.

Example B–5 Subprogram Name for Name Resolution

DECLARE 

   FUNCTION dept_name (department_id IN NUMBER) 

   RETURN departments.department_name%TYPE 

IS 

   department_name departments.department_name%TYPE; 

BEGIN 

   SELECT department_name INTO dept_name.department_name 

   --    ^column             ^local variable 

    FROM departments 

   WHERE department_id = dept_name.department_id;

   --   ^column         ^formal parameter 

   RETURN department_name; 

END dept_name;

 

BEGIN 

   FOR item IN (SELECT department_id 

                 FROM departments 

                ORDER BY department_name) LOOP 

      DBMS_OUTPUT.PUT_LINE ('Department: ' || dept_name(item.department_id)); 

   END LOOP;

END;

/ 

Result:

Department: Accounting

Department: Administration

Department: Benefits

.

.

.