create procedure test @first int = NULL, @last int output as begin select * from testtableend --================= exec test 这样就能返回testtable的整个数据结果集了
Procedure 中生成、处理、关闭结果集 那就是lsxaa(小李铅笔刀) 说到的:游标了!
to vinsonshen(有空多来,努力升星~~) :要是像你那样做法,分开存存储过程及程序代码两个处理,还不如不用存储过程,所有的都在程序中写还方便....变变思维方式吧!!!!----------------------------to skyboy0720 :没有过,不会用,可否举个例子!
create procedure test @first int = NULL, @last int output as declare curTemp cursor for select a,b from testtable open curTemp fetch next from curTemp into @a ,@b while (@@fetch_status=0) begin .................. fetch next from curTemp into @varFDBName end close curTemp deallocate curTemp
to TNTBOMB() :请问整个连接过程,如下:Connection conn = getConnection(URL,UserName,Password); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(SQL); ... rs.close(); st.close(); conn.close();这在存储过程中如何实现?
补充:中间再加上一个 try {...}catch(Exception e) {...}:)
TNTBOMB() 的方法对呀,用游标
to tddw(学习.net & C#中) :我知道TNTBOMB() 的方法对,现在要更进一步:看上两层!!!
用游标: declare @id int declare @col varchar(30) declare mycurm cursor for select id,col1 from testtable --定义游标 open mycurm --打开游标 fetch next from mycurm into @tableid,@col --获取游数据 while @@fetch_status=0 --游标中有数据时重复 begin fetch next from mycurm into @tableid, @col --再次获取游标数据 end close mycurm --关闭游标 deallocate mycurm --释放游标变量
一般的查询处理在 Java 中,上面的例子已经知道是那样处理了,现在是问:在存储过程中如何实现相同的效果!!!!
@first int = NULL,
@last int output
as
begin
select * from testtableend
--=================
exec test
这样就能返回testtable的整个数据结果集了
那就是lsxaa(小李铅笔刀) 说到的:游标了!
@first int = NULL,
@last int output
as
declare curTemp cursor for select a,b from testtable
open curTemp
fetch next from curTemp into @a ,@b
while (@@fetch_status=0)
begin
..................
fetch next from curTemp into @varFDBName
end
close curTemp
deallocate curTemp
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(SQL);
...
rs.close();
st.close();
conn.close();这在存储过程中如何实现?
declare @id int
declare @col varchar(30)
declare mycurm cursor for select id,col1 from testtable --定义游标
open mycurm --打开游标
fetch next from mycurm into @tableid,@col --获取游数据
while @@fetch_status=0 --游标中有数据时重复
begin
fetch next from mycurm into @tableid, @col --再次获取游标数据
end
close mycurm --关闭游标
deallocate mycurm --释放游标变量
Connection conn = getConnection(URL,UserName,Password);
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(SQL);
...
rs.close();
st.close();
conn.close();
}catch(Exception e) {
....
}上面這段過程多了Connection,Statement及try{}catch{},在存儲過程中又可否實現呢?:)