ORACLE: Considerations for passing in parameter variable names when writing a function

When developers in Oracle write their own SQL functions, the entry parameter name should not be the same as the field name of the table in the SELECT statement. Otherwise, although the compilation succeeds, the running result is often incorrect.

Example:

 1 CREATE OR REPLACE FUNCTION S_GET_EMP_NAME(EMPNO nvarchar2) return nvarchar2 is 2   ENAME nvarchar2(100); 3 begin 4   SELECT E.ENAME 5     INTO ENAME 6     FROM EMP E 7    WHERE E.EMPNO = EMPNO 8      AND ROWNUM = 1; 9   RETURN ENAME;10 end;

The code is very simple. Get the employee name ename through employee ID empno.

The test code is as follows:

Select s_get_emp_name (7654) from dual;

Returned result: Smith

But this is not the expected value. The data in EMP is as follows:

Select empno, ename from EMP;

----------------------------------------------------

7369 Smith
7499 Allen
7521 ward
7566 Jones
7654 Martin
7698 Blake
7782 Clark
7788 Scott
7839 king
7844 Turner
7876 Adams
7900 James
7902 Ford
7934 Miller

----------------------------------------------------

Apparently, 7654 does not correspond to Smith. What is the problem?

WhereE. empno = empno

In this row of conditions, because the variable name empno is the same as the field empno In the table, when executing the SQL statement, it is considered that the column empno is compared with itself, so it is always true, in the end, only rownum = 1 is used, and only the first row is returned.

 

Solution:

Rename the entry parameter in the function. For example, add the prefix "in _" to "in_empno ".