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<=@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();
}
}