学习了游标, 在做练习的过程中出现了一个问题.
题目是,将 所有客户签单总额,超过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;