使用Select正确使用存储过程

Question

我创建了一个存储过程，在这个过程中，我可以通过可调用语句选择我在存储过程中地址的列。我试图使用类似于准备语句的语法的SELECT SECTION NAME FROM allsections_list WHERE SECTION_NAME = ?，但我认为使用这种语法是不兼容的。我才刚学到这个mysql。

存储过程

CREATE STORED PROCEDURE getSECTION_NAME(OUT SECTION_NAME VARCHAR)
SELECT SECTION_NAME FROM allsections_list

码

 private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String searchSection = Section_SearchSection_Textfield.getText();
    String searchSection_Name = Section_SectionName_TextField.getText();

    if (searchSection.isEmpty())
    {
        JOptionPane.showMessageDialog(null, "Please fill up this fields");
    }
    else 

        try (Connection myConn = DBUtil.connect();
             CallableStatement myCs = myConn.prepareCall("{call getSECTION_NAME(?)}"))
        {
            myCs.setString(1, searchSection_Name);

            try (ResultSet myRs = myCs.executeQuery())
            {
                int resultsCounter = 0;
                while (myRs.next())
                {
                    String getSection_Name = myRs.getString(1);
                    Section_SectionName_TextField.setText(getSection_Name);
                    resultsCounter++;
                }
            }
        } 
        catch (SQLException e) 
        {
            DBUtil.processException(e);
        }

当我查记录的时候。如果存在记录，则该值将打印到文本字段。但它没有打印出来。它给我抛出一个错误getSECTION_NAME does not exist.如果我想选择多个值，怎么办？因为我有个项目我在做一个注册系统。我根据读取的内容选择此存储过程，而不是批处理语句。任何帮助都会感激的。谢谢!

Answer 1

我不使用MySql，但在Oracle中有一个类似的例子(我认为这也适用于MySql )。

CREATE PROCEDURE get_section_name(OUT secName VARCHAR(100))
BEGIN
SELECT SECTION_NAME INTO secName FROM allsections_list WHERE some_condition   = 100; //your procedure does not use any input arguments if you want to return just one record then you'll probably need to specify a where clause
END
/  //when executing a stored procedure in a DB  client you will need to specify a terminator character (in this case slash /)

请注意，没有返回语句，因为我们正在使用OUT参数。

getOutValueForStoredProcedure方法调用存储过程并检索out值。

public String getOutValueForStoredProcedure(String procedureName, int sqlType) throws EasyORMException{

    String out=null;
    CallableStatement stmt=null;
    try{
            //out parameters must me marked with question marks just as input parameters
          sqlQuery = "{call " + procedureName +"(?)}";
          stmt=conn.prepareCall(sqlQuery);//I assume that a Connection has been created

          stmt.registerOutParameter(1, sqlType);

          stmt.execute();    

          out = stmt.getString(1);//you get the out variable through the Statement, not the ResultSet

    }catch(Exception e){
          //log exception
    }finally{
          //close stmt
    }
    return out;
}

若要调用此存储过程，可以使用

   String out = getOutValueForStoredProcedure("get_section_name", java.sql.Types.VARCHAR);

要在MySql中创建存储过程，请检查此链接http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843

有关更详细的示例，请查看此http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/