Question

我写了一个pl/sql，主要是用游标遍历一张表.

修改此表和相关表的某些字段。虽然数据量在10万以上，是比较大，但是这个pl/sql执行了十几个小时也没有结果。我在substr(slsj,1,4)上做过索引了，请大家看看何原因这么慢的？
create or replace procedure yw_promote_do_not_use(yearc in varchar2) is
countNum int;
tmp varchar2(1);
yw_old varchar2(32);
yw_new varchar2(32);
v_cyh varchar2(12);
cursor c1 is select cyh,yw,pzyw,djyw from cyd where substr(slsj,1,4)=yearc;
begin
countNum:=0;
for rec in c1 loop
v_cyh:=rec.cyh;
yw_new:='';
yw_old:='';
if(rec.yw is not null and rtrim(ltrim(rec.yw)) is not null) then
yw_old:=rtrim(ltrim(rec.yw));
for i in 1..lengthb(yw_old) loop
tmp:=substr(yw_old,i,1);
if(tmp='A') then yw_new:=yw_new||'B3';
elsif(tmp='B') then yw_new:=yw_new||'B3';
elsif(tmp='C') then yw_new:=yw_new||'C1';
elsif(tmp='D') then yw_new:=yw_new||'D1';
end if;
end loop;
update cyd set yw=yw_new where cyh=rec.cyh;
update fhz set yw=yw_new where cyh=rec.cyh;
end if;
countNum:=countNum+1;
if(countNum=1000) then
countNum:=0;
commit;
end if;
end loop;
commit;
exception
when others then
rollback;
end yw_promote_do_not_use;

Answer 1

1.检查一下游标C1中查询语句的执行计划，看看是不是耗时很多。10万数据并不多，而且你做了函数索引。所以我猜这个不是问题产生的原因；
2.检查一下你的两层循环到底有多少次，是不是次数过多。目测不会超过10万吧。所以应该也不是问题关键；
*.以上两点可以通过使用plsql developer工具debug该过程时，点击create profiler report，然后执行，再从debug界面的profiler分页查看。
3.你的过程中 update cyd 和 fhz，执行过程的时候，有没有什么其他程序再多两个表做update，有没有锁表。cup使用率是否高；
4.通常不要在查询a表的循环中对a表update。你这里查询Cyd内部就对Cyd进行了update，这样有问题。我曾经遇到过，程序会锁住。
*.对于你的程序，给你个建议，把一下这段代码写成一个function，在由表中查寻结果中直接调用：
FOR i IN 1 .. Lengthb(Yw_Old) LOOP
Tmp := Substr(Yw_Old, i, 1);
IF (Tmp = 'A') THEN
Yw_New := Yw_New || 'B3';
ELSIF (Tmp = 'B') THEN
Yw_New := Yw_New || 'B3';
ELSIF (Tmp = 'C') THEN
Yw_New := Yw_New || 'C1';
ELSIF (Tmp = 'D') THEN
Yw_New := Yw_New || 'D1';
END IF;
END LOOP;
*.假设function叫做fun_pack，游标改成这样：
CURSOR C1 IS
SELECT Cyh,
fun_pack(Yw),
Pzyw,
Djyw
FROM Cyd
WHERE Substr(Slsj, 1, 4) = Yearc;

Answer 2

你的程序读到了需要处理的记录(CYD表)，但修改该记录时又查询一次，10万条记录要查询10万次，浪费多少时间啊。
修改：cursor c1 is select cyh,yw,pzyw,djyw from cyd where substr(slsj,1,4)=yearc;
改为：cursor c1 is select cyh,yw,pzyw,djyw from cyd
where substr(slsj,1,4)=yearc for update of yw;
修改：update cyd set yw=yw_new where cyh=rec.cyh;
改为：update cyd set yw=yw_new where current of c1;
这样修改CYD时不会再查找一次，直接修改当前行记录。

Answer 3

十万不至于这么慢，没有接触过pl/sql，但是道理都是相通的。你可以自建一个dummy table，看一下你的逻辑有没有死循环。
另外对于比较大的表格，如果可以不用 cursor最好。
如果在sql server, 我会做 replace case when 这样的事情。

Answer 4

for i in 1..lengthb(yw_old) loop

不对
for i in 1..length(yw_old) loop