ORACLE游标

[eason2000]

我有一个SQL游标，请问怎么写才能在ORACLE里执行？
declare @djid varchar2(255),
@dwid varchar2(255),
@je number(20,8),
@xde number(20,8),
@sumjine number(20,8),
@zjjine number(20,8)
Declare Source Cursor For
select djid from #a

Open Source



Fetch Next From Source Into @djid
While @@Fetch_Status = 0
Begin
declare source1 cursor for
select djid,je from tmp_fxb where djid=@djid
order by dwid,djid

Open Source1

Fetch Next From Source1 Into @djid,@je
Select @sumjine = @je
select @hjjine=isnull(sum(je),0) from t_djmx
select @zjjine=@sumjine+@hjjine

While @@Fetch_Status = 0
Begin
Fetch Next From Source1 Into @djid,@je
if @zjjine<=@xde
begin
insert into t_djmx(djid,je)values(@djid,@je)
end


End
Close Source1
Deallocate Source1

Fetch Next From Source Into @djid
End
Close Source
Deallocate Source
end

[duqiangcise]

[Quote=引用楼主 eason2000 的回复:]
我有一个SQL游标，请问怎么写才能在ORACLE里执行？
declare @djid varchar2(255),
@dwid varchar2(255),
@je number(20,8),
@xde number(20,8),
@sumjine number(20,8),
@zjjine number(20,8)
Declare Source Cursor For
select……
[/Quote]
你这个东东好像不是使用的是oracle数据库吧？？？可能在oracle上不能够执行。

[tangren]

--改了一下（不保证正确），自己调试一下
DECLARE
  v_djid    VARCHAR2(255);
  v_dwid    VARCHAR2(255);
  v_je      NUMBER(20, 8);
  v_xde     NUMBER(20, 8);
  v_sumjine NUMBER(20, 8);
  v_zjjine  NUMBER(20, 8);
  SOURCE  CURSOR FOR
    SELECT djid FROM a;

  source1 CURSOR FOR
    SELECT djid, je FROM tmp_fxb WHERE djid = djid ORDER BY dwid, djid;

BEGIN
  OPEN SOURCE;
  FETCH SOURCE
    INTO v_djid;
  WHILE SOURCE%FOUND LOOP
    OPEN Source1;
    FETCH Source1
      INTO v_djid, v_je;
    v_sumjine := v_je;
    SELECT isnull(SUM(je), 0) INTO v_hjjine FROM t_djmx;
    v_zjjine := v_sumjine + v_hjjine;
    WHILE SOURCE1%FOUND LOOP
      FETCH Source1
        INTO v_djid, v_je;
      IF v_zjjine <= v_xde THEN
        INSERT INTO t_djmx (djid, je) VALUES (v_djid, v_je);
      END IF;
    END LOOP;
    CLOSE Source1;
    FETCH SOURCE
      INTO v_djid;
  END LOOP;
  CLOSE SOURCE;
END;

[tzc]

pl_sql 就可以调试 传参

[eason2000]

不会啊。好像是语法错误！

[ojuju10]

你的是mssql的游标，楼主先熟悉下oracle游标的语法后，再慢慢调试吧

[eason2000]

是的，是MSSQL游标，ORACLE的游标不会弄

[friendjin]

你还是先看看oracle pl/sql编程吧，很简单的。

[心中的彩虹]

DECLARE
v_djid VARCHAR2(255);
v_dwid VARCHAR2(255);
v_je NUMBER(20, 8);
v_xde NUMBER(20, 8);
v_sumjine NUMBER(20, 8);
v_zjjine NUMBER(20, 8);
cursor source is select djid from a;
cursor source1 is SELECT djid, je FROM tmp_fxb WHERE djid = djid ORDER BY dwid,djid;

BEGIN
OPEN SOURCE;
FETCH SOURCE
INTO v_djid;
WHILE SOURCE%FOUND LOOP
OPEN Source1;
FETCH Source1
INTO v_djid, v_je;
v_sumjine := v_je;
SELECT isnull(SUM(je), 0) INTO v_hjjine FROM t_djmx;
v_zjjine := v_sumjine + v_hjjine;
WHILE SOURCE1%FOUND LOOP
FETCH Source1
INTO v_djid, v_je;
IF v_zjjine <= v_xde THEN
INSERT INTO t_djmx (djid, je) VALUES (v_djid, v_je);
END IF;
END LOOP;
CLOSE Source1;
FETCH SOURCE
INTO v_djid;
END LOOP;
CLOSE SOURCE;
END;

[qicai841125]

学习一下六楼给的例子