count_teachers(dept_name VARCHAR): return as an OUT parameter the total number of teachers belonging to the department named exactly as dept_name.

I can't do this last part of the exercise, I need to do this procedure in Java. I don't know how to do this subquery because it is inside a procedure with in and out parameters

public static void count_Teacher_By_Department_Procedure() {
    try {
        Connection con = conexion("jdbc:hsqldb:.\\database\\db");
        Statement stt;
        stt = con.createStatement();
        String sql = "DROP PROCEDURE count_Teacher_By_Department_Procedure IF EXISTS;";
        stt.executeUpdate(sql);

        sql = "CREATE PROCEDURE count_Teacher_By_Department_Procedure(IN id int, OUT nombre Varchar(20))"
                  "READS sql DATA "
                  "BEGIN ATOMIC "
                  "SET id = SELECT COUNT id FROM teachers WHERE dept_num IN (SELECT dept_num FROM departments WHERE name = nombre); " // pasar al set el parametro out
                  "END";
        stt.executeUpdate(sql);
        con.close();
    } // 
    catch (SQLException ex) {
        Logger.getLogger(Act3_4.class.getName()).log(Level.SEVERE, null, ex);
    }
}

CodePudding user response：

I made same DB in sqlServer... and the subquery works but not in a Procedure in Java and I don't know why

enter image description here

CodePudding user response：

It seems you have some syntax error and your IN and OUT parameters are not correct.

 sql = "CREATE PROCEDURE count_Teacher_By_Department_Procedure(OUT idcount int, IN nombre Varchar(20))"
              "READS sql DATA "
              "BEGIN ATOMIC "
              "SET idcount = SELECT COUNT( id ) FROM teachers WHERE dept_num IN (SELECT dept_num FROM departments WHERE name = nombre); " // pasar al set el parametro out
              "END";