CREATE OR REPLACE PROCEDURE fan IS
CURSOR C IS
SELECT A.ROWID, A.AAE032, A.AAE033, A.AAE034, AAE010, AAE004, AAE005,AAB001
FROM czbx A
WHERE A.ROWID > (SELECT MIN(B.ROWID)
FROM czbx B
WHERE A.AAE004 = B.AAE004 AND A.AAE005 = B.AAE005 AND
A.AAE010 = B.AAE010 AND A.AAB001=B.AAB001 AND A.AAE004 IS NOT NULL);
I INTEGER;BEGIN
I := 0;
FOR R IN C LOOP
BEGIN
IF R.AAE032 IS NOT NULL THEN
UPDATE czbx
SET AAE032 = R.AAE032
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
IF R.AAE033 IS NOT NULL THEN
UPDATE czbx
SET AAE033 = R.AAE033
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
IF R.AAE034 IS NOT NULL THEN
UPDATE czbx
SET AAE034 = R.AAE034
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
I := I + SQL%ROWCOUNT;
DELETE FROM czbx WHERE ROWID = R.ROWID;
END;
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('total update : ' || I);END fan;这是说在czbx表中有重复的人的数据(aae032,aae033,aae034不重复),就把他合并成一条数据这样的存储过程执行很慢,有哪位高手能有化一下,万分感谢!
CURSOR C IS
SELECT A.ROWID, A.AAE032, A.AAE033, A.AAE034, AAE010, AAE004, AAE005,AAB001
FROM czbx A
WHERE A.ROWID > (SELECT MIN(B.ROWID)
FROM czbx B
WHERE A.AAE004 = B.AAE004 AND A.AAE005 = B.AAE005 AND
A.AAE010 = B.AAE010 AND A.AAB001=B.AAB001 AND A.AAE004 IS NOT NULL);
I INTEGER;BEGIN
I := 0;
FOR R IN C LOOP
BEGIN
IF R.AAE032 IS NOT NULL THEN
UPDATE czbx
SET AAE032 = R.AAE032
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
IF R.AAE033 IS NOT NULL THEN
UPDATE czbx
SET AAE033 = R.AAE033
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
IF R.AAE034 IS NOT NULL THEN
UPDATE czbx
SET AAE034 = R.AAE034
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
END IF;
I := I + SQL%ROWCOUNT;
DELETE FROM czbx WHERE ROWID = R.ROWID;
END;
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('total update : ' || I);END fan;这是说在czbx表中有重复的人的数据(aae032,aae033,aae034不重复),就把他合并成一条数据这样的存储过程执行很慢,有哪位高手能有化一下,万分感谢!
解决方案 »
- 图方便装了一个快捷版11G 现在像卸载 怎么办
- 求一oracle语句
- 创建了分区表,分区已经建好了,,但是发现建好的表无法在增加分区,怎么回事?
- 高分求解Oracle数据库的灵异事件
- 这条sql语句怎么写? 在线
- 问个项目中比较实际的问题,收集下大家的习惯...
- 我的ORICLE表结构被修改了~如何知道什么时候,被谁修改了
- Blob,Bfile类型 ORACLE里是怎么用的,谢谢
- 如何将字符串'2003-10-11 14:10:24.017'转化为日期类型?
- 关于hibernate 新手求教
- 求助:基于远程网络远程数据库应用解决方案问题!!!
- 如何在java中获得oracle存储过程out的二维数组[高分求救]
对于UPDATE,DELETE,这种情况,我建议一次性执行完毕
CURSOR C IS
SELECT A.ROWID, A.AAE032, A.AAE033, A.AAE034, AAE010, AAE004, AAE005,AAB001
FROM czbx A
WHERE A.ROWID > (SELECT MIN(B.ROWID)
FROM czbx B
WHERE A.AAE004 = B.AAE004 AND A.AAE005 = B.AAE005 AND
A.AAE010 = B.AAE010 AND A.AAB001=B.AAB001 AND A.AAE004 IS NOT NULL);
I INTEGER; BEGIN
I := 0;
FOR R IN C LOOP
BEGIN
IF R.AAE032 IS NOT NULL THEN
UPDATE czbx
SET AAE032 = R.AAE032
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
COMMIT;
END IF;
IF R.AAE033 IS NOT NULL THEN
UPDATE czbx
SET AAE033 = R.AAE033
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
COMMIT;
END IF;
IF R.AAE034 IS NOT NULL THEN
UPDATE czbx
SET AAE034 = R.AAE034
WHERE AAE004 = R.AAE004 AND AAE005 = R.AAE005 AND
AAE010 = R.AAE010 AND AAB001=R.AAB001;
COMMIT;
END IF;
I := I + SQL%ROWCOUNT;
DELETE FROM czbx WHERE ROWID = R.ROWID;
COMMIT;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'total update : ' || I); END fan; 用这个试试。
DML语句,建议多次提交,要比一次性提交效率的多。