返回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
飞翔天使 阅读(1455)
评论(0) 编辑 收藏 所属分类:
java