java 带返回值的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33121521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure with return values
提问by Harbinger
I have a stored procedure "test", which looks like:
我有一个存储过程“test”，它看起来像：
CREATE PROCEDURE test
@name varchar(32)
AS
DECLARE
@login_status TINYINT,
@syb_login varchar(20),
@syb_pass varchar(20)
...
..
BEGIN
SELECT @status = 0
SELECT @login as login,
@pass as pass,
@status as status
RETURN 0
END
I need to pass a single input parameter "myName" as input parameter to this procedure and which in turn returns the login, pass and status as output (from only one record) parameters.
我需要将单个输入参数“myName”作为输入参数传递给此过程，然后返回登录、传递和状态作为输出（仅来自一条记录）参数。
In JDBC, I tried to do like below:
在 JDBC 中，我尝试执行以下操作：
String query = "{call test(?,?,?)}";
System.out.println(query);
CallableStatement proc = null;
ResultSet rs;
try {
proc = connection.prepareCall(query);
proc.setString(1, "myName");
proc.registerOutParameter(2, java.sql.Types.VARCHAR);
proc.registerOutParameter(3, java.sql.Types.VARCHAR);
proc.execute();
System.out.println(proc.getString(2));
This always gives the exception:
这总是给出例外：
java.sql.SQLException: JZ0SG: A CallableStatement did not return as many output parameters as the application had registered for it.
at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
at com.sybase.jdbc4.jdbc.ParamManager.nextResult(Unknown Source)
at com.sybase.jdbc4.jdbc.ParamManager.doGetOutValueAt(Unknown Source)
at com.sybase.jdbc4.jdbc.ParamManager.getOutValueAt(Unknown Source)
at com.sybase.jdbc4.jdbc.SybCallableStatement.getString(Unknown Source)
I tried with JDBC execute SQL Server stored procedure with return value and input/output parameters, https://msdn.microsoft.com/en-us/library/ms378108.aspxbut this didn't work.
我尝试使用JDBC 执行带有返回值和输入/输出参数的 SQL Server 存储过程， https://msdn.microsoft.com/en-us/library/ms378108.aspx但这没有用。
回答by flo
There are two ways:
有两种方式：
1. Redefine your procedure(recommended)
1. 重新定义你的程序（推荐）
Output paramteres have to be declared when creating a stored procedure:
创建存储过程时必须声明输出参数：
CREATE PROCEDURE test
@name varchar(32),
@login varchar(32) = null output,
@pass varchar(32) = null output,
@status int = -1 output
AS
BEGIN
SET @status = 0
SELECT @login = logincolumn,
@pass = passcolumn
FROM usertable
END
Then you can use it with JDBC as follows:
然后您可以将其与 JDBC 一起使用，如下所示：
String query = "{call test(?,?,?,?)}";
[...]
proc = connection.prepareCall(query);
proc.setString(1, "myName");
proc.registerOutParameter(2, java.sql.Types.VARCHAR);
proc.registerOutParameter(3, java.sql.Types.VARCHAR);
proc.registerOutParameter(4, java.sql.Types.INTEGER);
Note:
笔记：
- You have to
setorselectinto the declared variables to give them output values. - Usually it isn't a good idea to pass passwords around
- 您必须
set或select进入声明的变量才能为它们提供输出值。 - 通常，传递密码不是一个好主意
2. Catch the results from multiple resultsets(not recommended)
2.从多个结果集中抓取结果（不推荐）
Connection con;
CallableStatement proc = null;
ResultSet rs;
String qry = "{call test(?)}";
proc = con.prepareCall(qry);
proc.setString(1, "name");
proc.executeQuery();
// first result set returning the status
rs = proc.getResultSet();
if (rs.next()) {
System.out.println(rs.getString(1));
}
// second result set returning login and pass
if (proc.getMoreResults()) {
rs = proc.getResultSet();
if (rs.next()) {
System.out.println(rs.getString("login"));
System.out.println(rs.getString("pass"));
}
}
