下面这段程序是 一个循环,循环体是 判断一个记录是不是存在,如果存在,就进行两个update的操作,不知道错在什么地方,不知道哪个大侠能帮帮忙,看看啊begin
loop
if exists(select * from mfg_tbl_rework_bk where HISTDATE='2008-08-21'and eqptype is null) then
begin
UPDATE MFG_TBL_REWORK_BK D SET (EQPTYPE,EQPID)=(SELECT EQPTYPE,EQPID
FROM HIST@ODSPRD A,
(SELECT C.PARENTID LOT,B.LOTID, MAX(B.PREVHISTTIME) PREVHISTTIME
FROM HIST_LOTEVENTS@ODSPRD A, HIST@ODSPRD B,MFG_TBL_REWORK C,ACTL@ODSPRD D
WHERE A.LOTID=D.PARENTID
AND EVTYPE = 'NTKO'
AND A.LOTID = B.LOTID
AND A.PREVHISTTIME = B.PREVHISTTIME
AND B.EQPTYPE IN ('LII', 'LDI')
AND C.PARENTID=D.LOTID
AND C.EQPTYPE IS NULL
AND C.HISTDATE=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')
GROUP BY B.LOTID,C.PARENTID) B
WHERE A.LOTID = B.LOTID
AND A.PREVHISTTIME = B.PREVHISTTIME
and d.PARENTID=b.lot)
where D.EQPTYPE IS NULL
AND HISTDATE='2008-08-21';
UPDATE MFG_TBL_REWORK_BK D SET (PARENTID)=(select PARENTID from actl@odsprd a
where d.PARENTID=a.lotid)
where D.EQPTYPE IS NULL
AND HISTDATE='2008-08-21';
end else exit;
end if;
end loop;
end
loop
if exists(select * from mfg_tbl_rework_bk where HISTDATE='2008-08-21'and eqptype is null) then
begin
UPDATE MFG_TBL_REWORK_BK D SET (EQPTYPE,EQPID)=(SELECT EQPTYPE,EQPID
FROM HIST@ODSPRD A,
(SELECT C.PARENTID LOT,B.LOTID, MAX(B.PREVHISTTIME) PREVHISTTIME
FROM HIST_LOTEVENTS@ODSPRD A, HIST@ODSPRD B,MFG_TBL_REWORK C,ACTL@ODSPRD D
WHERE A.LOTID=D.PARENTID
AND EVTYPE = 'NTKO'
AND A.LOTID = B.LOTID
AND A.PREVHISTTIME = B.PREVHISTTIME
AND B.EQPTYPE IN ('LII', 'LDI')
AND C.PARENTID=D.LOTID
AND C.EQPTYPE IS NULL
AND C.HISTDATE=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')
GROUP BY B.LOTID,C.PARENTID) B
WHERE A.LOTID = B.LOTID
AND A.PREVHISTTIME = B.PREVHISTTIME
and d.PARENTID=b.lot)
where D.EQPTYPE IS NULL
AND HISTDATE='2008-08-21';
UPDATE MFG_TBL_REWORK_BK D SET (PARENTID)=(select PARENTID from actl@odsprd a
where d.PARENTID=a.lotid)
where D.EQPTYPE IS NULL
AND HISTDATE='2008-08-21';
end else exit;
end if;
end loop;
end
解决方案 »
- job执行删除MLOG$_
- 多表查询问题(谢谢先)
- 如何实现Form的背景色每周轮换改变啊?
- 如何跟踪存储过程的执行情况
- 在命令提示符下用EXP导出数据库,要导出的用户:这项写什么?
- Oracle中如何判断是否为数字?
- oracle 已经安装好了,但是登录不上,请高手指教,在线等,一定给分
- 我用表b中的c_csrq(date型,“1949-10-19”),更新另一个表a中的dcsrq(date型),变成了“2049-10-19”。
- 存储过程问题 300分 在线等待
- 过程中有关数组的用法,这样有错吗?
- 在线等~~~有关DML空值查询的疑问
- (紧急求助)建立的job不定时执行(oracle9i)??解决马上给分!谢谢
这句有问题啊你用个变量
flag number;select count(*) into flag
from mfg_tbl_rework_bk
where HISTDATE='2008-08-21'and eqptype is null;
if flag>1 then
begin另外把错误代码贴出来
你的UPDATE要保证set后面子查询出来是单行数据