各位请帮忙看看我的问题。其实这个问题如果有第三个表很容易解决，只是没有创建表权限，所以就在两个表之间做工作。
这是原始视图，
desc tblprestu
Name Null Type
-------------- -------- -------------
STUIDNOT NULL VARCHAR2(11)
STUNAMENOT NULL VARCHAR2(11)
EVENTID
VARCHAR2(255)
TERM NOT NULL VARCHAR2(4)
CLASSNO NOT NULL VARCHAR2(5)
MODULEHK
VARCHAR2(64)
ACADYEAR NOT NULL VARCHAR2(4)
这是要处理的表，这个表总是和上面的视图比较，找出更新，删除，新插入的列。
desc tblstu
Name Null Type
-------------- ---- -------------
STUID VARCHAR2(255)
STUNAME VARCHAR2(255)
EVENTID
VARCHAR2(255)
ACADYEAR
VARCHAR2(255)
TERM
VARCHAR2(255)
CLASSNO
VARCHAR2(255)
MODULEHKVARCHAR2(255)
STATUS
VARCHAR2(1)
AUDITTIME DATE
INTRANSFER
NUMBER
PL/SQL来负责更新上面的表，但是现在的问题是，似乎逻辑不正确，比如我更新视图中的某些列的eventid大概十几个受影响，tblstu按照下面的逻辑只应该存在那十几行而且的status是U，但是很多不相干行，eventid根本没变的，status也变成U了，搞不懂。。。 请大拿帮忙看看！多谢！
declare
tblstu tblstu%ROWTYPE;
stuid tblstu.STUID%TYPE;
stuname tblstu.STUNAME%TYPE;
stuactid tblstu.EVENTID%TYPE;
stuacdyear tblstu.acadyear%TYPE;
stuterm tblstu.term%TYPE;
stuclass tblstu.CLASSNO%TYPE;
stumodkey tblstu.MODULEHK%TYPE;
stustatus tblstu.status%TYPE;
stuaudit tblstu.audittime%TYPE;
cursor c1 is
select nvl(b.STUID, a.STUID),a.STUNAME, a.EVENTID,a.acadyear,a.term,a.CLASSNO,a.MODULEHK,
case
when (a.STUID = b.STUID) and (a.STUNAMEb.STUNAME or a.CLASSNO b.CLASSNO or a.EVENTID b.EVENTID or a.acadyearb.acadyear or a.termb.term or a.MODULEHKb.MODULEHK) then 'U'
when a.STUID is null and b.STUID is not null then 'D'
when a.STUID is not null and b.STUID is null then 'I'
end status,
sysdate
from tblprestu a
full join tblstu b on a.STUID = b.STUID;
begin
update tblstu set intransfer = 0;
open c1;
loop
fetch c1 into stuid,stuname,stuactid,stuacdyear,stuterm,stuclass,stumodkey,stustatus,stuaudit;
exit when c1%NOTFOUND;
if stustatus = 'D' then
update tblstu set status = 'D' where STUID = stuid;
update tblstu set INTRANSFER= 1 where STUID = stuid;
elsif stustatus = 'I' then
insert into tblstu values(stuid,stuname,stuactid,stuacdyear,stuterm,stuclass,stumodkey,stustatus,stuaudit,1);
elsif stustatus = 'U' then
delete from tblstu where STUID = stuid;
insert into tblstu values(stuid,stuname,stuactid,stuacdyear,stuterm,stuclass,stumodkey,stustatus,stuaudit,1);
--dbms_output.put_line(stuid||'-'||stuname||'-'||stuactid||'-'||stuacdyear||'-'||stuterm||'-'||stuclass||'-'||stumodkey||'-'||stustatus||'-'||stuaudit);
else
null;
end if;
end loop;
close c1;
commit;
end;
|
