Why is this Oracle function with parameters not input? -- The truth has finally surfaced"

Why is this Oracle function with parameters not input? -- The truth is that there are two tables in my Oracle Database: emp (employee table) and dept (Department table). The table structure is as follows: the table www.2cto.com Emp is as follows:

The dept table is as follows:

In the emp table, there are employee ID (empno), employee name (ename), employee job, employee salary (salary), employee bonus (bonus), and employment date (hiredate), employee lead (mgr), department number (deptno ). The dept table contains the Department ID (deptno), department name (dname), and department location ). The purpose of writing such a function is to pass in the department name (dname) and return the average salary of the employees of the Department. Although the requirement is very simple, the result is not as expected, the return value is null. Functions and execution results are as follows:

Cause Analysis: 1. Is there no developer department? The result of querying the dept table is as follows, which proves that this department is not absent, for example:

2. Can we see that the developer Department number is 10? Isn't there any employee in the developer department whose department number is 10? The following results show that four employees belong to the developer department and their salaries are 10000, 8000, 9000, and 5000, for example, www.2cto.com.

3. Is the Key SQL statement in the function incorrect? I run the SQL statement separately. The results show that not only the SQL statement is correct, but the result is consistent with the actual result (10000 + 8000 + 9000 + 5000)/4 = 8000, for example:

After reading the books, I found that if a function in the books has input parameters, there is such a writing method. For this example, you can change it to v_dname dept. dname % type indicates that the input parameter name is v_dname and the parameter type is the type of the dname field in the dept table. The result is 8000, which is a positive solution. The reason why the previously passed parameters are not obtained is that the parameter type is inconsistent with the dname In the dept table. Because you think that the Department name belongs to the string, the passed parameter is defined as v_dname in varchar2, and the type of the dname field in the self-built table is char (20 ), the result is null due to inconsistent types. Change the input parameter type to dept. dname % type to get the correct result. The result is as follows:

Author: Yang Zhe