很Happy,通过看代码学会了另一种写法,将代码改造如下:
表结构不变。
DECLARE
cst_SYNC_MAX constant integer := 5;
cst_SYNC_NEW constant integer := 1;
cst_SYNC_OLD constant integer := 0;
acount integer := 0;
BEGIN
for c in (SELECT w.ID,
w.userName,
w.userAge,
w.userNumber
FROM wawa w
WHERE w.sync_flag = cst_SYNC_NEW
AND ROWNUM < cst_SYNC_MAX) LOOP
begin
SELECT count(1)
INTO acount
FROM test t
WHERE t.id = c.id;
DBMS_OUTPUT.PUT_LINE('acount = ' || acount);
if(acount>0) then
UPDATE test
SET userName = c.username,
userAge = c.userage,
userNumber = c.usernumber
WHERE id = c.id;
end if;
if(acount<=0) then
INSERT INTO test
(test.ID,
test.userName,
test.userAge,
test.userNumber,
test.sync_date)
VALUES
(c.id,
c.username,
c.userage,
c.usernumber,
sysdate);
end if;
UPDATE wawa
SET sync_flag = cst_SYNC_OLD
WHERE ID = c.id;
end;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End of the procedure');
END;
说明:这种写法里没有显式定义游标,而是使用了一个FOR循环,比较新奇,不知道这算不算是隐式游标...