27,579
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[syspro_getfavorites]
-- Add the parameters for the stored procedure here
@iuserid varchar(40),
@ilang varchar(40),
@return cursor varying output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @return =cursor forward_only static for
-- Insert statements for procedure here
select b.res_id id, b.upid pid, a.res_name name, a.res_enname, a.res_url, (case
when a.res_open = '0' then
'false'
else
'true'
end) 'open'
from syst_res_info a, syst_res_rel b, syst_res_list c, syst_factory_info d
where a.deleteflag = '0'
and a.res_state = '0'
and a.id = b.res_id
and b.res_list_id = c.id
and c.id = d.res_list_id
and c.is_base = '1';
open @return;
END
CallableStatement cstmt=null;
Connection conn=getConnection();
cstmt=conn.prepareCall("call dbo.syspro_getfavorites(?,?,?)");
cstmt.setString(1,iuserid);
cstmt.setString(2,ilang);
cstmt.registerOutParameter(3,Types.OTHER);
cstmt.execute();
com.microsoft.sqlserver.jdbc.SQLServerException: 操作数类型冲突: varbinary 与 cursor 不兼容
2.java中调用
2.1 (数据库使用连接池)
public String cursorTest(){
Connection conn = this.dao.getJdao().getConnection();
ResultSet rs = null;
try {
CallableStatement stmt = conn.prepareCall("{call cursor_test_package.cursor_test_pc(?,?)}");
stmt.setString(1, "测试");
stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );
stmt.execute();
rs = (ResultSet)stmt.getObject(2);
while(rs.next()){
System.out.println(rs.getString(1)+":"+rs.getString(2));
}
} catch (SQLException e) {
System.out.println("执行存储过程发生错误!"+e.getMessage());
e.printStackTrace();
}
return "ok";
}
2.2(数据库使用JDBC连接)
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:ora","net7b.com","net7b");
oracle.jdbc.OracleCallableStatement stmt = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call audit.sp_audit_GetTaxpayerList(?, ?)}");
stmt.setString( 1, "23201020100");
stmt.registerOutParameter( 2, oracle.jdbc.OracleTypes.CURSOR );
stmt.execute();
rs = stmt.getCursor( 2 );
procedure syspro_getfacresinfotree(iuserid in varchar2,
ilang in varchar2,
p_cursor out cursortype)
ALTER PROCEDURE [dbo].[syspro_getfavorites]
-- Add the parameters for the stored procedure here
@iuserid varchar(40),
@ilang varchar(40)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select b.res_id id, b.upid pid, a.res_name name, a.res_enname, a.res_url, (case
when a.res_open = '0' then
'false'
else
'true'
end) 'open'
from syst_res_info a, syst_res_rel b, syst_res_list c, syst_factory_info d
where a.deleteflag = '0'
and a.res_state = '0'
and a.id = b.res_id
and b.res_list_id = c.id
and c.id = d.res_list_id
and c.is_base = '1';
end
ALTER PROCEDURE [dbo].[syspro_getfavorites]
-- Add the parameters for the stored procedure here
@iuserid varchar(40),
@ilang varchar(40)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select b.res_id id, b.upid pid, a.res_name name, a.res_enname, a.res_url, (case
when a.res_open = '0' then
'false'
else
'true'
end) 'open'
from syst_res_info a, syst_res_rel b, syst_res_list c, syst_factory_info d
where a.deleteflag = '0'
and a.res_state = '0'
and a.id = b.res_id
and b.res_list_id = c.id
and c.id = d.res_list_id
and c.is_base = '1';
--直接返回就是了,不像oracle需要用游标返回的.
end