java db2 sql语句_DB2 java存储过程调用返回错误SQLCODE = -440,SQLSTATE = 42884
我正在对DB2进行简单的存储过程调用.虽然它调用存储过程,但它总是返回此错误：
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE, DRIVER=3.66.46
========== Java代码：
String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver";
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// to execute the stored procedure.
System.out.println("CALL median_result_set(?)");
String sql = "CALL median_result_set(?)";
CallableStatement stmt1 = conn.prepareCall(sql);
stmt1.registerOutParameter(1, Types.DOUBLE);
stmt1.execute();
System.out.println("jdbcadapter->callproc after execute " + sql);
stmt1.close();
conn.close();
==============
db2 clp命令行有效：
c:SP>db2 call median_result_set(?)
Value of output parameters
--------------------------
Parameter Name : MEDIANSALARY
Parameter Value : +7.68582000000000E+004
Result set 1
--------------
NAME JOB SALARY
--------- ----- ---------
Marenghi Mgr 77506.75
O'Brien Sales 78006.00
================
存储过程定义：
CREATE PROCEDURE median_result_set
-- Declare medianSalary as OUT so it can be used to return values
(OUT medianSalary DOUBLE)
RESULT SETS 2
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT salary FROM staff
ORDER BY CAST(salary AS DOUBLE);
-- use WITH RETURN in DECLARE CURSOR to return a result set
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) > medianSalary
ORDER BY salary;
-- use WITH RETURN in DECLARE CURSOR to return another result set
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE CAST(salary AS DOUBLE) < medianSalary
ORDER BY SALARY DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET medianSalary = 6666;
-- initialize OUT parameter
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
-- return 1st result set, do not CLOSE cursor
OPEN c2;
-- return 2nd result set, do not CLOSE cursor
OPEN c3;
END @