java调用有参数的存储过程_JAVA中对存储过程的调用方法（二）带输出参数的

二、带输出参数的 1:返回int -------------------------带输出参数的---------------- alter procedure getsum @n int =0, @result int output as declare @sum int declare @i int set @sum=0 set @i=0 while @i set @sum=@sum+@i set @i=@i+1 end set @res

二、带输出参数的

1:返回int

-------------------------带输出参数的----------------

alter procedure getsum

@n int =0,

@result int output

as

declare @sum int

declare @i int

set @sum=0

set @i=0

while @i<=@n begin

set @sum=@sum+@i

set @i=@i+1

end

set @result=@sum

-------------------在查询分析器中执行------------

declare @myResult int

exec getsum 100,@myResult output

print @myResult

------------在JAVA中调用---------------------

import java.sql.*;

public class ProcedureTest

{

public static void main(String args[]) throws Exception

{

//加载驱动

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

//获得连接

Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");

//创建存储过程的对象

CallableStatement c=conn.divpareCall("{call getsum(?,?)}");

//给存储过程的第一个参数设置值

c.setInt(1,100);

//注册存储过程的第二个参数

c.registerOutParameter(2,java.sql.Types.INTEGER);

//执行存储过程

c.execute();

//得到存储过程的输出参数值

System.out.println (c.getInt(2));

conn.close();

}

}

2:返回varchar

----------------存储过程带游标----------------

---在存储过程中带游标 使用游标不停的遍历orderid

create procedure CursorIntoProcedure

@pname varchar(8000) output

as

--定义游标

declare cur cursor for select orderid from orders

--定义一个变量来接收游标的值

declare @v varchar(5)

--打开游标

open cur

set @pname='--给@pname初值

--提取游标的值

fetch next from cur into @v

while @@fetch_status=0

begin

set @pname=@pname+';'+@v

fetch next from cur into @v

end

print @pname

--关闭游标

close cur

--销毁游标

deallocate cur

------------执行存储过程--------------

exec CursorIntoProcedure '

--------------JAVA调用------------------

import java.sql.*;

public class ProcedureTest

{

public static void main(String args[]) throws Exception

{

//加载驱动

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

//获得连接

Connection conn=DriverManager.getConnection("jdbc:odbc:mydata","sa","");

CallableStatement c=conn.divpareCall("{call CursorIntoProcedure(?)}");

c.registerOutParameter(1,java.sql.Types.VARCHAR);

c.execute();

System.out.println (c.getString(1));

conn.close();

}

}