oracle 新手问题求助

[夺命胖子]

有一个表
ID ClassName ZoneArea IsLock
21 物流天地 3 0
22 天路 4 0
23 天路ppp 5 0

将ID为22的 ZoneArea 和 ID为23 ZoneArea一换，实现自定义排序，

下面函数参数为当前ID，该函数从表中找比当前ID的ZoneArea第一大的那条记录，然后交换顺序。

注释中的SQL%ROWCOUNT 第一次为1 第二次为3， 函数可能写得有问题，请大哥帮忙看看

create or replace function IndexInfoClassFDown(
  ID NUMBER)
  RETURN NUMBER 
as
currZone tbindexinfoclass.zonearea%Type;   /**//*定义临时变量*/
downZone tbindexinfoclass.zonearea%Type;
downid  tbindexinfoclass.id%Type;
retval number;
begin  
retval:=0;
select zonearea into currZone from tbindexinfoclass where tbindexinfoclass.id=ID and rownum=1;

select max(t.id) into downid  from (select * from tbindexinfoclass where tbindexinfoclass.zonearea>currZone and islock=0 order by zonearea asc) t
where rownum=1;

/*savepoint point1;*/ 
if downid is not null then
    select zonearea INTO downzone from tbindexinfoclass where tbindexinfoclass.id=downid AND rownum=1;  
    update tbindexinfoclass set zonearea=currZone where tbindexinfoclass.id=downid;
    retval:=retval+SQL%ROWCOUNT;  /* SQL%ROWCOUNT 为1*/
    update tbindexinfoclass set zonearea=downzone where tbindexinfoclass.id=ID;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); /* SQL%ROWCOUNT 为3 为什么不懂*/
    retval:=retval+SQL%ROWCOUNT; 
end if;
COMMIT;
    IF RetVal<2 THEN
       ROLLBACK;
    END IF;
    RETURN retval;
end;

[小灰狼W]

update tbindexinfoclass set zonearea=downzone where tbindexinfoclass.id=ID;
ID=ID，自然是更新全表了

另外，你这个函数不对，函数里不能有dml语句
而且
COMMIT;
IF RetVal<2 THEN
ROLLBACK;
END IF;
将rollback写在commit后面，没有意义

[小灰狼W]

将传入变量ID改成P_ID，后面涉及到的地方也作相应修改
这是存储过程开发中经常遇到的问题，过程解析时会将其优先解析为字段名，但表中不存在该字段时才会解析成绑定变量

[gelyon]

你的意思倒是懂了，但是你的函数我没咋看懂

[minitoy]

你的意思我也没懂。。。。

[kingkingzhu]

create or replace procedure IndexInfoClassFDown(LS_ID in NUMBER) is
currZone tbindexinfoclass.zonearea%Type; /**/ /*定义临时变量*/
downZone tbindexinfoclass.zonearea%Type;
downLS_ID tbindexinfoclass.ID%Type;
retval number;
begin
retval := 0;

select zonearea
into currZone
from tbindexinfoclass
where tbindexinfoclass.ID = LS_ID
and rownum = 1;

select max(t.ID)
into downLS_ID
from (select *
from tbindexinfoclass
where tbindexinfoclass.zonearea > currZone
and islock = 0
order by zonearea asc) t
where rownum = 1;

/*savepoint point1;*/
if downLS_ID is not null then
select zonearea
INTO downzone
from tbindexinfoclass
where tbindexinfoclass.ID = downLS_ID
AND rownum = 1;
update tbindexinfoclass
set zonearea = currZone
where tbindexinfoclass.ID = downLS_ID;
retval := retval + SQL%ROWCOUNT; /* SQL%ROWCOUNT 为1*/
update tbindexinfoclass
set zonearea = downzone
where tbindexinfoclass.ID = LS_ID;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); /* SQL%ROWCOUNT 为3 为什么不懂*/
retval := retval + SQL%ROWCOUNT;
end if;
end;
其实我也没懂 呵呵