有一个表
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;
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;
解决方案 »
- 请问查询该表是不是分区表的语句除了以下还有其他写法么?
- 哪位兄弟有OracleXE 10.2 linux 64 bit的安装程序?
- 从sql转oracle
- 求助oracle中SQL动态执行的问题,急!!
- 请大家帮我解决一个问题!谢谢
- 谁有vc里occi开发的资料或者例程~谢谢啊
- 菜鸟问题:这到底是什么意思?冥思苦想,不得其解
- 毕业了
- mrp中的模块化BOM怎么实现呀
- Couldn't perform the edit because another user changed the record.(再线等待,谢谢解决)
- 怎么通过一个存储过程连接另外一个用户?帮忙看看
- oracle 作业定期执行作业,如某月的1号0辰更新表的数据某一字段为0
ID=ID,自然是更新全表了另外,你这个函数不对,函数里不能有dml语句
而且
COMMIT;
IF RetVal<2 THEN
ROLLBACK;
END IF;
将rollback写在commit后面,没有意义
这是存储过程开发中经常遇到的问题,过程解析时会将其优先解析为字段名,但表中不存在该字段时才会解析成绑定变量
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;
其实我也没懂 呵呵