[学习笔记] ORACLE 游标提取值与表内列名相同引发的问题

学习了游标, 在做练习的过程中出现了一个问题.

题目是,将 所有客户签单总额,超过100万的,客户等级加2,50到100万之间的,客户等级加1, 10万以下的客户,等级减1.

开始SQL语句如下,查看后发现所有的客户等级都加2.

-- Created on 2013/12/7 by ADMINISTRATOR
declare
 totalmoney NUMBER;
 id NUMBER;
 
 CURSOR customer_id IS     --声明游标
SELECT c.id,totalmoney FROM custom c,salerecord s
 WHERE c.id = s.customerid AND to_char(s.contacttime,'yyyy')=to_char(SYSDATE,'yyyy');
begin
     OPEN customer_id;    ---打开游标
   LOOP
     FETCH customer_id INTO  id,totalmoney;   --提取游标
     EXIT WHEN customer_id%NOTFOUND;
     IF totalmoney>1000000 THEN
     UPDATE custom  SET levels = levels+ 2 WHERE custom.id = id;

     ELSIF totalmoney>=500000 AND totalmoney<=1000000 THEN
     UPDATE custom  SET levels = levels+1 WHERE custom.id = id;
     
       ELSIF  totalmoney<100000 THEN
     UPDATE custom  SET levels = levels-1 WHERE custom.id = id;
 
     END IF;
     END LOOP;
     CLOSE customer_id; --关闭游标 
end;

最后发现, 原来是声明变量的时候, id NUMBER; 所致, 由于id与表内列名 id 同名, 导致结果均为true,  所以出现计算错误的情况. 后来改为下面语句后,  计算正常.

-- Created on 2013/12/7 by ADMINISTRATOR
declare
 totalmoney NUMBER;
 custid NUMBER;
 
 CURSOR customer_id IS     --声明游标
SELECT c.id,totalmoney FROM custom c,salerecord s
 WHERE c.id = s.customerid AND to_char(s.contacttime,'yyyy')=to_char(SYSDATE,'yyyy');
begin
     OPEN customer_id;    ---打开游标
   LOOP
     FETCH customer_id INTO  custid,totalmoney;   --提取游标
     EXIT WHEN customer_id%NOTFOUND;
     IF totalmoney>1000000 THEN
     UPDATE custom  SET levels = levels+ 2 WHERE custom.id = custid;

     ELSIF totalmoney>=500000 AND totalmoney<=1000000 THEN
     UPDATE custom  SET levels = levels+1 WHERE custom.id = custid;
     
       ELSIF  totalmoney<100000 THEN
     UPDATE custom  SET levels = levels-1 WHERE custom.id = custid;
 
     END IF;
     END LOOP;
     CLOSE customer_id; --关闭游标
 
end;