Oracle中游标Cursor的使用

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/hellosmu/article/details/54970601

Cursor用来遍历临时表的查询结果。

声明cursor后可用for或者fetch进行遍历。

使用for进行遍历的代码:

declare
cursor c_students is 
select * from students;
	c_names students%rowtype;
begin
	for c_names in c_students loop
		dbms_output.put_line(c_names.first_name || '-' || c_names.last_name);
	end loop;
end;

使用fetch进行遍历的代码:

declare
	cursor c_students is
		select * from students;
	c_names c_students%rowtype;
begin
	open c_students;
	loop
		fetch c_students
			into c_names;
		exit when c_students%notfound;
		dbms_output.put_line(c_names.first_name || '-' || c_names.last_name);
	end loop;
	close c_students;
end;


展开阅读全文

with as表达式 和 CURSOR游标

11-04

[code=SQL]rnset ANSI_NULLS ONrnset QUOTED_IDENTIFIER ONrngornALTER PROCEDURE [dbo].[ds_GetHisData]rn @OrgId nvarchar(30),rn @FromTime DateTime,rn @EndTime DateTimernASrnBEGINrn SET NOCOUNT ON;rn Declare @jlyid nvarchar(20) ;rn Declare @sqlStr nvarchar(2000);rn Declare @tablenames nvarchar(1024);rn --通过递归取得所有的组织编号rn WITH userorgs(op_upkey,op_name,op_key,op_rank,orglevel)rn ASrn (rn SELECT op_upkey,op_name,op_key,op_rank ,0 AS orglevel FROM organize_inforn WHERE op_key = @OrgIdrn UNION ALLrn SELECT l.op_upkey,l.op_name,l.op_key,l.op_rank,orglevel+1 FROM organize_info l rn INNER JOIN userorgs p ON l.op_upkey=p.op_keyrn )rn --with as 后面必须跟着使用表达式的语句,我想把userorgs用在游标cur_JLYList里rn select * from userorgsrn rn --在这个游标里用userorgs无效?????rn DECLARE cur_JLYList CURSOR FAST_FORWARD FORrn select G_JLYIDrn from JLY_Inforn where g_opkey in (SELECT op_key FROM userorgs);rnrn OPEN cur_JLYList;rnrn FETCH NEXT FROM cur_JLYList INTO @JLYIdrn ---日期分表处理rn set @tablenames='( HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)rn while Datepart(yyyy,@FromTime)!=Datepart(yyyy,@EndTime) or Datepart(mm,@FromTime)!=Datepart(mm,@EndTime)rn beginrn set @FromTime=Dateadd(m,1,@FromTime)rn set @tablenames=@tablenames+' union HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)rn endrn set @tablenames=@tablenames+') as hisdata'+@jlyidrn rn set @sqlStr = ' select * from '+@tablenamesrn WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。rn beginrn --开始循环游标变量rn FETCH NEXT FROM cur_JLYList INTO @JLYIdrn --执行sql操作rn ---日期分表处理rn set @tablenames='( HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)rn while Datepart(yyyy,@FromTime)!=Datepart(yyyy,@EndTime) or Datepart(mm,@FromTime)!=Datepart(mm,@EndTime)rn beginrn set @FromTime=Dateadd(m,1,@FromTime)rn set @tablenames=@tablenames+' union HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)rn endrn set @tablenames=@tablenames+') as hisdata'+@jlyidrn rn set @sqlStr = @sqlStr + ' union select * from '+@tablenamesrn endrn print @sqlStrrnENDrnrngornexec [ds_GetHisData] 'C100158','2010-11-4 00:00:00','2010-11-4 10:00:00'rn[/code] 论坛

没有更多推荐了,返回首页