Oracle的PL/SQL编程之基础技能实战一
一>基础代码检查
检查以bm_开头的系统初始化编码表是否有空值。与业务系统相关的编码项不能存在空值，会导致系统业务无法办理。为初始化数据表、在做测试数据和正式上线前检查。上线运行后、仍存在空值表、需要进行核实、可能存在不经常办理的业务。也可能是冗余表。
PL/SQL代码块:
declare
v_table_name varchar(40);
v_sql_str varchar(4000):=' ';
v_cnt smallint:=0 ;
v_jgbm varchar(10):='01%';
cursor tmp_cur is select table_name from user_tables
where table_name like 'BM_%' ;
begin
open tmp_cur ;
loop
fetch tmp_cur into v_table_name ;
exit when tmp_cur%notfound;
select count(*) into v_cnt from user_tab_columns
where table_name=v_table_name and column_name='JGBM';
if v_cnt<>0 then
v_sql_str:='select count(*) from '||v_table_name||' where jgbm='''||v_jgbm||'''';
else
v_sql_str:='select count(*) from '||v_table_name;
end if;
execute immediate v_sql_str into v_cnt;
if v_cnt=0 then
insert into tmp_hfsc_sy40_chk(ywms) values(v_table_name||'表记录为空');
end if;
commit;
end loop;
close tmp_cur;
end;
二>财务业务核对
业务关于资金的结算都是自动生成凭证，财务和业务应是保持一致的。如存在特殊业务可能会有不平衡的情况。如果有人调整数据也会造成不平。主要检查 归集余额、单位未分配金额、贷款余额、逾期贷款、业务流水金额和财务凭证金额是否相等。直接影响到系统的报表业务和财务的一致性。
PL/SQL代码块:
declare
v_fpzd smallint;
v_ztbh number(20);
v_cwnd smallint;
v_kmye number(18,2);
v_gjye number(18,2);
v_jgbm varchar(10):='01%';
v_msg varchar(100);
v_ret smallint:=0;
begin
select nvl(max(value1),0) into v_fpzd from bm_xtcs where bm1=v_jgbm and bm2='0301' and bm3='03010903' and bm='04' and sfqy=1;
for a in(select id from cw_ztml where ztxz='01' and jgbm=v_jgbm) loop
select max(nd) into v_cwnd from cw_nd where ztbh=a.id;
select nvl(sum(nce+ljdf-ljjf),0) into v_kmye from cw_kmbh where kmbh='201' and ztbh=a.id and nd=v_cwnd;
select v_kmye+nvl(sum(fl.dffse-fl.jffse),0) into v_kmye from cw_pz_fl fl inner join cw_pz_ml b on fl.pzid=b.pzid where kmbh like '201%' and b.ztbh=a.id and b.nd=v_cwnd and b.zfbz=0;
if v_fpzd<>0 then
select nvl(sum(dwzhye),0) into v_gjye from gjzf_dw_zz where jgbm=v_jgbm;
else
select nvl(sum(zckye+dwzhye),0) into v_gjye from gjzf_dw_zz where jgbm=v_jgbm;
end if;
if v_kmye<>v_gjye then
insert into tmp_hfsc_sy40_chk(ywms) values(to_char(a.id)||'账套'||'201科目余额与业务归集余额不一致');
end if;
commit;
end loop;
end;
三>跟新im_zhsz_dw中的yhmc使得同一银行账号和grdk_xm_zhlr中的skyhmc相等.
declare
v_yhmc varchar2(40) := ' ';
v_skyhzh varchar2(50) := ' ';
v_count number(20):=0;
cursor tmp_cur is
select distinct a.yhmc,b.skyhmc, b.skyhzh
from im_zhsz_dw a
inner join grdk_xm_zhlr b
on a.yhzhhm = b.skyhzh
and a.yhmc=b.skyhmc;
begin
open tmp_cur;
loop
fetch tmp_cur
into v_yhmc, v_skyhzh;
exit when tmp_cur%notfound;
update im_zhsz_dw a
set a.yhmc = v_yhmc
where a.yhzhhm = v_skyhzh
and a.yhmc <> v_yhmc;
v_count:=v_count+1;
end loop;
close tmp_cur;
dbms_output.put_line('一共更新了'||v_count||'行');
end;总结:PL/SQL编程解决问题的一般步骤:
1>创建存储空间表，要定义哪些字段以及类型以及约束;
2>如果这些表里面没有数据，要向这些表里面准备数据；
3>理清业务逻辑，操作这些表里面的数据逻辑；
4>获得结果数据；
