问题说明:执行抛出异常“java.sql.SQLException: 参数名的数目与已注册参数的数目不匹配”,请高手们指点迷津,第一次用存储过程写代码,菜鸟级的,多多帮助啊!
测试用例代码是:
public class ShopTest extends TestCase {
public void testSelect(){
ShopDAO shopDao=new ShopDAO();
shopDao.listShopByType("美食");
}
}
存储过程是这样的:
CREATE OR REPLACE PROCEDURE getCatalogIdByName(
pv_name IN sns_shop_catalog.catalogname%TYPE,
pc_catalog OUT sys_refcursor,
pn_result_code OUT NUMBER,
pv_result_info OUT VARCHAR2
)IS
--过程名
lc_procedure_name VARCHAR2(4000) := ' getCatalogIdByName ';
BEGIN
OPEN pc_catalog FOR SELECT catalogid FROM sns_shop_catalog WHERE catalogname=pv_name;
pn_result_code:=0;
pv_result_info:='OK';
RETURN;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
pn_result_code := SQLCODE;
pv_result_info := SQLERRM||' IN ' || lc_procedure_name;
RETURN;
END;
jdbc程序代码如下:
public List<User> listShopByType(String shopType){
CallableStatement callableStatement=null;
try{
callableStatement=conn.prepareCall("{ CALL getCatalogIdByName(?,?,?,?) }");
// Setting the type of output parameters
callableStatement.registerOutParameter(2,OracleTypes.CURSOR);
callableStatement.registerOutParameter(3,OracleTypes.NUMBER);
callableStatement.registerOutParameter(4,OracleTypes.VARBINARY);
// Setting the Input parameters for Java Stored Procedure
callableStatement.setString("pv_name", shopType);
// Call Java Stored Procedure to fetch calalogId
callableStatement.execute();
System.out.println(callableStatement.getLong("catalogid"));
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
callableStatement.close();
conn.close();
}catch(Exception ee){
ee.printStackTrace();
}
}
return null;
}
测试用例代码是:
public class ShopTest extends TestCase {
public void testSelect(){
ShopDAO shopDao=new ShopDAO();
shopDao.listShopByType("美食");
}
}
存储过程是这样的:
CREATE OR REPLACE PROCEDURE getCatalogIdByName(
pv_name IN sns_shop_catalog.catalogname%TYPE,
pc_catalog OUT sys_refcursor,
pn_result_code OUT NUMBER,
pv_result_info OUT VARCHAR2
)IS
--过程名
lc_procedure_name VARCHAR2(4000) := ' getCatalogIdByName ';
BEGIN
OPEN pc_catalog FOR SELECT catalogid FROM sns_shop_catalog WHERE catalogname=pv_name;
pn_result_code:=0;
pv_result_info:='OK';
RETURN;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
pn_result_code := SQLCODE;
pv_result_info := SQLERRM||' IN ' || lc_procedure_name;
RETURN;
END;
jdbc程序代码如下:
public List<User> listShopByType(String shopType){
CallableStatement callableStatement=null;
try{
callableStatement=conn.prepareCall("{ CALL getCatalogIdByName(?,?,?,?) }");
// Setting the type of output parameters
callableStatement.registerOutParameter(2,OracleTypes.CURSOR);
callableStatement.registerOutParameter(3,OracleTypes.NUMBER);
callableStatement.registerOutParameter(4,OracleTypes.VARBINARY);
// Setting the Input parameters for Java Stored Procedure
callableStatement.setString("pv_name", shopType);
// Call Java Stored Procedure to fetch calalogId
callableStatement.execute();
System.out.println(callableStatement.getLong("catalogid"));
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
callableStatement.close();
conn.close();
}catch(Exception ee){
ee.printStackTrace();
}
}
return null;
}
callableStatement.registerOutParameter(3,OracleTypes.NUMBER);
callableStatement.registerOutParameter(4,OracleTypes.VARBINARY);
这只有3个参数,貌似少一个输入参数的。
callableStatement.registerinParameter(1,OracleTypes.VARBINARY);