返回int
-------------------------帶輸出參數的----------------
1
alter procedure getsum
2@n int =0,
3@result int output
4as
5declare @sum int
6declare @i int
7set @sum=0
8set @i=0
9while @i<=@n begin
10set @sum=@sum+@i
11set @i=@i+1
12end
13set @result=@sum
-------------------在查詢分析器中執行------------
1declare @myResult int
2exec getsum 100,@myResult output
3print @myResult
--------------在Java中調用--------------------
1import java.sql.*;
2
3
public class ProcedureTest 
{
4
public static void main(String args[]) throws Exception {
5
//加載驅動
6 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
7 //獲得連接
8 Connection conn = DriverManager.getConnection("jdbc:odbc:mydata", "sa",
9 "");
10 //創建存儲過程的對象
11 CallableStatement c = conn.prepareCall("{call getsum(?,?)}");
12 //給存儲過程的第一個參數設置值
13 c.setInt(1, 100);
14 //注冊存儲過程的第二個參數
15 c.registerOutParameter(2, java.sql.Types.INTEGER);
16 //執行存儲過程
17 c.execute();
18 //得到存儲過程的輸出參數值
19 System.out.println(c.getInt(2));
20 conn.close();
21
}
22
}
返回varchar
----------------存儲過程帶游標----------------
1---在存儲過程中帶游標 使用游標不停的遍歷orderid
2create procedure CursorIntoProcedure
3@pname varchar(8000) output
4as
5--定義游標
6declare cur cursor for select orderid from orders
7--定義一個變量來接收游標的值
8declare @v varchar(5)
9--打開游標
10open cur
11set @pname=''--給@pname初值
12--提取游標的值
13fetch next from cur into @v
14while @@fetch_status=0
15 begin
16set @pname=@pname+';'+@v
17 fetch next from cur into @v
18end
19print @pname
20--關閉游標
21close cur
22--銷毀游標
23deallocate cur
------------執行存儲過程--------------
1exec CursorIntoProcedure ''
--------------在Java中調用--------------------
1import java.sql.*;
2
3public class ProcedureTest {
4 public static void main(String args[]) throws Exception {
5 // 加載驅動
6 DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
7 // 獲得連接
8 Connection conn = DriverManager.getConnection("jdbc:odbc:mydata", "sa",
9 "");
10 CallableStatement c = conn.prepareCall("{call CursorIntoProcedure(?)}");
11 c.registerOutParameter(1, java.sql.Types.VARCHAR);
12 c.execute();
13 System.out.println(c.getString(1));
14 conn.close();
15 }
16}
posted on 2009-01-26 11:28
飛翔天使 閱讀(1457)
評論(0) 編輯 收藏 所屬分類:
java