此次需求很简单,将金额总和等于0的客户状态由1变更为2,即置为无效;总和大于0和小于0的,且状态为2(无效)的置为1。
为了记录学习将这次的代码放到这里:
create or replace procedure UPDATE_STATUS
is
m_custno VARCHAR2(20);
m_code VARCHAR2(255);
m_custname VARCHAR2(255);
m_custstatus VARCHAR2(1);
m_bal NUMBER(16,2);
m_dte VARCHAR2(10);
m_tme TIMESTAMP(6);
m_usr VARCHAR2(30);
--游标将客户信息表的最新的客户信息以及状态罗列出来,关联租赁业务信息表取出项目本金余额字段方便后面的判断更新
cursor c_cust
is
select custno,code,custname,custstatus,sum(bal) as bal,dte,tme,usr
from (
select a1.custno,a1.code,a1.custname,a1.custstatus,a1.dte,a1.tme,a2.bal,a1.usr
from (select t.*
from (select y.*,row_number() over(partition by y.custno||y.custname order by y.TME desc) as rank
from cust y) t where t.rank=1) a1
join (select t1.custno,t1.code,t1.custname,t1.bal
from leas t1
group by t1.custno,t1.code,t1.custname,t1.bal) a2
on a1.custno = a2.custno and a1.code =a2.code and a1.custname = a2.custname)
group by custno,code,custname,custstatus,amddte,amdtme,amdusr;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
DBMS_OUTPUT.put_line('----------------start------------------');
open c_cust;
LOOP --遍历游标
FETCH c_cust --遍历游标中每行字段值到对应的变量中
INTO m_custno, m_code, m_custname,m_custstatus,m_bal,m_dte,m_tme,m_usr;
EXIT WHEN c_cust%NOTFOUND; --遍历完毕 退出
if m_bal=0
then
UPDATE cust a SET a.custstatus='2'
, a.dte=to_char(sysdate,'yyyy-mm-dd')
, a.tme=cast(systimestamp as timestamp)
, a.cnl='LBS'
, a.usr='zzz'
WHERE a.custno = m_custno
and a.code = m_code
and a.custname = m_custname
and a.dte = m_dte
and a.tme = m_tme ;
COMMIT;
END IF;
if m_bal>0 or m_bal<0
then
UPDATE cust a SET a.custstatus='1'
, a.dte=to_char(sysdate,'yyyy-mm-dd')
, a.tme=cast(systimestamp as timestamp)
, a.cnl='LBS'
, a.usr='zzz'
where a.custno = m_custno
and a.code = m_code
and a.custname = m_custname
and a.dte = m_dte
and a.tme = m_tme
and m_custstatus='2';
COMMIT;
END IF;
END LOOP; --结束遍历游标
CLOSE c_cust; --关闭游标
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
end UPDATE_STATUS;
这次存储过程遇到的问题:
1、编写困难,照抄很多版本才能让此次的编译没有报错问题
2、if语句条件符合,却不执行update,一开始以为是语法问题,但是后面看了别人的博客发现,需要注意数据问题:匹配不上,才导致不更新,于是将两方的数据放到临时表进行leftjoin 对比发现匹配不上,然后再次确定了需求,发现是本金余额的总额的问题,因为每个人都有多条记录的金额数。
3、解决了匹配不上的问题,发现匹配上也修改了,但是连非0的客户也会一起修改了数据来源,幸好数据量不大,修改回来,定位到if判断之后,之前写的if bal> and status='2' 这种问题不能再犯,应该放在update之后的where条件里。