--Sybase游标示例一： 
create PROCEDURE DBA.p_proc_test() ON EXCEPTION RESUME  
begin 
 declare @table_name varchar(100); 
  declare @count varchar(100); 
 declare @rownumber int; 
  declare HISTORYDATA dynamic scroll cursor for select  a.table_name ,a.count rownumber,number(*) from systable a,systable b,systable c order by a.count desc; 
 
  open HISTORYDATA with hold; 
  fetch next HISTORYDATA into @table_name,@count,@rownumber; 
  while @@SQLSTATUS = 0  loop 
    insert into test_tmp values(@table_name,@count,@rownumber); 
 fetch next HISTORYDATA into @table_name,@count,@rownumber; 
  end loop; 
  close HISTORYDATA; 
  deallocate cursor HISTORYDATA; 
 
end

--Sybase游标使用示例二（循环）： 
create PROCEDURE DBA.p_proc_test2() ON EXCEPTION RESUME  
begin 
 declare @table_name varchar(100); 
  declare @count varchar(100); 
 declare @rownumber int; 
 declare @num int; 
 
  declare HISTORYDATA dynamic scroll cursor for select  a.table_name ,a.count rownumber,number(*) from systable a order by a.count desc; 
set @num=(select 0); 
while @num<=30 loop 
  open HISTORYDATA with hold; 
  fetch next HISTORYDATA into @table_name,@count,@rownumber; 
  while @@SQLSTATUS = 0  loop 
    insert into test_tmp2 values(@table_name,@count,@rownumber,getdate()); 
 fetch next HISTORYDATA into @table_name,@count,@rownumber; 
  end loop; 
  close HISTORYDATA; 
  
set @num=(select @num+1); 
end loop; 
 deallocate cursor HISTORYDATA; 
end

--Sybase:游标嵌套用法 
create PROCEDURE DBA.p_teset(@i_rq int) 
begin 
-- 声明局部变量(变量需要一开始声明) 
declare @cur_pk_corp varchar(50); 
declare @c_pk_corp  varchar(50);  
declare @c_orgname  varchar(50);  
declare @c_pk_org varchar(30); 
declare @c_pk_deptdoc varchar(20); 
declare @c_px int;  
declare @c_px_new int;  
-- 声明游标 （游标需要一开始声明） 
--游标一 
declare c_corp dynamic scroll cursor   
　　for select a.pk_corp from t_corp a group by a.pk_corp order by a.pk_corp ;   
--游标二 
declare c_org  dynamic scroll  cursor   
　　for select a.pk_corp, a.orgname, coalesce(b.pk_org,a.pk_org) as pk_org,a.pk_deptdoc, 
　　 (select convert(int,case when substring(max(t.pk_org),char_length(max(t.pk_org)) ,char_length(max(t.pk_org))) is null then '0' else substring(max(t.pk_org),char_length(max(t.pk_org)) ,char_length(max(t.pk_org))) end)  from dba.t_dim_org_fix t where t.pk_corp=a.pk_corp) as px 
from t_corp a left outer join dba.t_org_fix b on a.pk_corp=b.pk_corp and a.pk_deptdoc=b.pk_deptdoc and a.pk_corp=@cur_pk_corp ; 
--初始化参数 
set @c_px = 0; 
-- 打开游标   
open c_corp with hold;   
FETCH  NEXT  c_corp INTO  @cur_pk_corp; 
--循环一个游标 
WHILE @@SQLSTATUS  = 0 loop 
　　set @c_px_new =1; 
　　-- 循环所有行   
　　-- 声明局部变量  
　  -- 打开游标   
open c_org with hold;  
FETCH  NEXT c_org INTO @c_pk_corp, @c_orgname, @c_pk_org, @c_pk_deptdoc, @c_px;   
-- 循环所有行  
WHILE @@SQLSTATUS = 0 loop 
　　-- 循环所有行   
　　set @c_px = @c_px  + @c_px_new ; 
set @c_px_new = @c_px_new +1; 
insert into dba.t_org_fix(pk_org,hr_name,enable_date,disabled_date,remark,pk_corp,pk_deptdoc) 
values(@pk_org||'X'||@c_px,@c_orgname,@i_rq,20991231,'', @c_pk_corp, @c_pk_deptdoc); 
FETCH  NEXT c_org INTO @c_pk_corp, @c_orgname, @c_pk_org, @c_pk_deptdoc, @c_px;   
END loop; 
-- 关闭游标   
close c_org; 
FETCH  NEXT  c_corp INTO  @cur_pk_corp; 
end loop; 
-- 关闭游标   
close c_corp ; 
--释放资源(最后释放) 
DEALLOCATE cursor c_org; 
DEALLOCATE CURSOR c_corp;  
 
end