老大布置了个任务,将一个表中几个字段同步到另一个表。
第一想法是去公司图书馆找相关的书,七翻八翻找到《Oracle PL/SQL实例精讲》一书,如获至宝,昼夜读之,大有收获。
于是乎,一边翻书,一边百度,一边代码,历时两天,终于完成。
表结构如下
ID | userName | userAge | userNumber | SYNC_FLAG |
1 | vvin | 11 | 101 | 1 |
2 | ggth | 12 | 102 | 1 |
3 | hello | 13 | 103 | 1 |
4 | world | 14 | 104 | 1 |
5 | gg | 15 | 105 | 0 |
ID | userName | userAge | userNumber | SYNC_DATE |
1 | ||||
2 | ||||
3 | ||||
4 | ||||
5 |
本机测试代码如下:
create or replace procedure pro_test(str out varchar2)
is
CURSOR c_wawa is
SELECT ID,userName,userAge,userNumber
FROM wawa
WHERE sync_flag=1;
cst_sync_flag CONSTANT integer := 0;
cst_sync_max CONSTANT integer := 3;
vr_wawa c_wawa%ROWTYPE;
vr_date DATE;
vr_count integer;
begin
OPEN c_wawa;
SELECT sysdate into vr_date from dual;
vr_count := 0;
loop
FETCH c_wawa into vr_wawa;
-- 游标结束或超过最多同步数量时,退出循环
EXIT WHEN (c_wawa%NOTFOUND OR (vr_count > cst_sync_max));
UPDATE test set
test.userName = vr_wawa.username,
test.userAge = vr_wawa.userage,
test.userNumber = vr_wawa.usernumber,
test.sync_date = vr_date
WHERE test.id = vr_wawa.id;
UPDATE wawa set
wawa.sync_flag = cst_sync_flag
WHERE wawa.id = vr_wawa.id;
vr_count := vr_count + 1;
END loop;
SELECT username into str from wawa where ID =1;
CLOSE c_wawa;
end;
总结:今天一个上午都在纠结 update那一句。仿真时候,刚开始一切正常,半路上莫名其妙的出错,单步跟踪只要一运行到这一句,PL/SQL就卡死状态,百思不解。一个偶然关了DOS CMD窗口,测试OK,好吧,猜测估计是当SCOTT通过CMD窗口登录到ORCALE时候,仿真SCOTT用户下的表会出点儿问题。