java - Jdbc方法返回结果不一致

Question

我似乎无法弄清楚下面的代码有什么问题。 getSalaryAverageDepartment 方法调用属于 HR 架构中 EMP_PGK 包的 PL/SQL 过程 get_salary_average_dept。 p>

    public float getSalaryAverageDepartment(int deptId)
    {
        Connection conn =  null;
        CallableStatement callStmt = null;
        Float avgDeptSal = -1f;

        try
        {
            // Register the Jdbc Driver  
            // Class.forName(JDBC_DRIVER_ORACLE);

            // Create a database connection
            conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PWD);

            // Create a SQL String
            String callProc = "{ call HR.EMP_PKG.get_salary_average_dept( ? , ? )}";

            // Create a callable statement
            callStmt = conn.prepareCall(callProc);

            // Bind value to the IN parameter
            callStmt.setInt(1, java.sql.Types.NUMERIC);

            // Register the OUT parameter's type to the SQL type of the return value
            callStmt.registerOutParameter(2, java.sql.Types.FLOAT);

            // Execute the callable statement
            callStmt.execute();

            // Retrieve the OUT parameter
            avgDeptSal = callStmt.getFloat(2);
            System.out.println("Department with Id : "+ deptId + " has average employee salary of : " + avgDeptSal);
        }
        catch (SQLException se) 
        {
            System.out.println("Exception occured in the database");
            System.out.println("Exception message: "+ se.getMessage());
            System.out.println("Dataabse error code: "+ se.getErrorCode());
            se.printStackTrace();
        }
        finally
        {
            // Clean up
            if(callStmt != null)
            {
                try
                {
                    callStmt.close();
                } 
                catch (SQLException se2) 
                {
                    se2.printStackTrace();
                }
            }

            if(conn != null)
            {
                try
                {
                    conn.close();
                } 
                catch (SQLException se2) 
                {
                    se2.printStackTrace();
                }
            }
        }

        return avgDeptSal;
    }

这里id是调用上面代码的main方法:

public static void main(String[] args) 
{
    HrManager hrMngr = new HrManager();

    int deptId = 80;

    hrMngr.getSalaryAverageDepartment(deptId);
}

以下是 PL/SQL 过程:

  -- Purpose: Returns the average salary of a department with given id
  PROCEDURE get_salary_average_dept(dept_id IN departments.department_id%type, avg_salary OUT FLOAT) IS
  BEGIN
   SELECT AVG(CAST(salary as FLOAT))
   INTO avg_salary
   FROM employees
   WHERE department_id = dept_id;
   dbms_output.put_line('Average salary for department with id : '|| dept_id ||' is : '|| avg_salary);
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('No department found with id: '|| dept_id);
   WHEN others THEN
   dbms_output.put_line('Error!');
  END get_salary_average_dept;

这是java程序的输出:

Department with Id : 80 has average employee salary of : 0.0

我在 Jdeveloper 中独立执行了 PL/SQL 过程，输出如下:

Connecting to the database Insight_Dev_Hr.
Average salary for department with id : 80 is : 9000
Process exited.

为什么两个输出不同。我在这里错过了什么吗？

Answer 1

我认为你的问题在这里:

      // Bind value to the IN parameter
      callStmt.setInt(1, java.sql.Types.NUMERIC);

您正在将 java.sql.Types.NUMERIC (2) 的值作为第一个参数的值绑定(bind)到存储过程。相反，你应该这样做:

      callStmt.setInt(1, deptId);