“书籍过期不显示罚款”处理概要
1.随机抽取数据,查看要处理数据的格式
select z31_rec_key,z31_description,z31_sum from z31 where z31_credit_debit='D' and z31_rec_key like '1061400225%';
select z31_description,z31_sum from z31 where z31_credit_debit='C' and z31_rec_key like '1061400225%';
select z31_description from z31 where z31_rec_key like '1061400225%' and z31_status='C';
select z31_description from z31 where z31_rec_key like '1061400225%' and z31_status='O' and z31_type=3;
select z31_description from z31 where z31_rec_key like '1061400225%' and z31_status='W';
select z31_sum,z31_description from z31 where z31_rec_key='1061400225 200405221414401';
2.更新z31表的一条记录的z31_sum字段
update z31 set z31_sum='00000000000250' where z31_rec_key='1061400225 200405221414401';
3.确定Z31表的结构与数据存储方式
select z31_sum from z31 where z31_credit_debit='D' and z31_rec_key like '4369%';
select z31_rec_key,z31_status,z31_sum from z31 where z31_rec_key like '4369%';
4.统计要处理的数据量
select count(*) from z31 where z31_status='O' and z31_credit_debit='D' and z31_sum='00000000000000';
5.创建备份数据表结构
create table z31_bak
(
Z31_REC_KEY CHAR(27),
Z31_DATE NUMBER(8),
Z31_STATUS CHAR(1),
Z31_SUB_LIBRARY CHAR(5),
Z31_ALPHA CHAR(1),
Z31_TYPE NUMBER(4),
Z31_CREDIT_DEBIT CHAR(1),
Z31_SUM CHAR(14),
Z31_PAYMENT_DATE_KEY CHAR(12),
Z31_PAYMENT_CATALOGER CHAR(10),
Z31_PAYMENT_TARGET VARCHAR2(20),
Z31_PAYMENT_IP VARCHAR2(20),
Z31_PAYMENT_RECEIPT_NUMBER VARCHAR2(20),
Z31_DESCRIPTION VARCHAR2(100),
Z31_KEY VARCHAR2(100),
Z31_KEY_TYPE CHAR(10)
)
6.备份Z31表的数据到备份表中
insert into z31_bak
(
Z31_REC_KEY,
Z31_DATE ,
Z31_STATUS ,
Z31_SUB_LIBRARY,
Z31_ALPHA ,Z31_TYPE,
Z31_CREDIT_DEBIT,
Z31_SUM,
Z31_PAYMENT_DATE_KEY,
Z31_PAYMENT_CATALOGER,
Z31_PAYMENT_TARGET,
Z31_PAYMENT_IP,
Z31_PAYMENT_RECEIPT_NUMBER,
Z31_DESCRIPTION ,
Z31_KEY,
Z31_KEY_TYPE
)
select
Z31_REC_KEY,
Z31_DATE ,
Z31_STATUS ,
Z31_SUB_LIBRARY,
Z31_ALPHA ,
Z31_TYPE,
Z31_CREDIT_DEBIT,
Z31_SUM,
Z31_PAYMENT_DATE_KEY,
Z31_PAYMENT_CATALOGER,
Z31_PAYMENT_TARGET,
Z31_PAYMENT_IP,
Z31_PAYMENT_RECEIPT_NUMBER,
Z31_DESCRIPTION ,
Z31_KEY,
Z31_KEY_TYPE
from z31;
7.使用左填充函数,将char2字符补充到char1的左侧,使char1的长度为n
lpad(char1,n,char2)
8.创建关键字段数据表
create table z31_source
(
Z31_REC_KEY CHAR(27),
Z31_STATUS CHAR(1),
Z31_SUM CHAR(14),
Z31_DESCRIPTION VARCHAR2(100)
);
9.将要处理的数据字段复制到z31_source表中
insert into z31_source
(
Z31_REC_KEY,Z31_STATUS ,Z31_SUM,Z31_DESCRIPTION
)
select
Z31_REC_KEY,Z31_STATUS ,Z31_SUM,Z31_DESCRIPTION
from
z31
where
z31_status='O' and z31_type=3 and z31_credit_debit='D';
10.再次统计要处理的数据条数
select count(*) from z31 where z31_status='O' and z31_type=3 and z31_credit_debit='D';
11.实验字符串截取函数substr找到相应的位置
select substr(z31_description,8,4) from z31_source where z31_rec_key='1011200007 200305060947702';
12.字符串转换成数字函数
select to_number(substr(z31_description,8,4))*50 from z31_source where z31_rec_key='1011200007 200305060947702';
13.为z31_source表增加字段money来存储罚款金额
ALTER TABLE z31_source ADD money char(10);
14.实验一条数据更新money字段
update z31_source set money=to_char(to_number(substr(z31_description,8,4))*50) where z31_rec_key='1011200007 200305060947702';
15.更新所有记录的money字段
update z31_source set money=to_char(to_number(substr(z31_description,8,4))*50);
16.更新一条记录的z31_sum字段
update z31_source set z31_sum=lpad(trim(money),14,'0') where z31_rec_key='1011200007 200305060947702';
17.更新所有记录的z31_sum字段
update z31_source set z31_sum=lpad(trim(money),14,'0');
18.同步一条记录
update z31 set z31_sum = (
select z31_sum from z31_source where z31_rec_key='1011200007 200305060947702')
where z31_rec_key='1011200007 200305060947702';
19.查看结果
select z31_sum from z31 where z31_rec_key='1011200007 200305060947702';
20.同步所有记录
update z31 set z31_sum= (select z31_sum from z31_source where z31_rec_key=z31.z31_rec_key) where z31_status='O';
21.同照备份数据库,查看更新结果
select z31_rec_key,z31_sum,money from z31_source where to_number(money)=0;
select z31_rec_key,z31_sum,money from z31_source where to_number(z31_sum)=0;
select count(*) from z31_bak where z31_status='O';
select count(*) from z31_bak where to_number(z31_sum)>0;
select count(*) from z31 where z31_status='O';
11444
select count(*) from z31 where z31_status='C';
7573
select count(*) from z31 where z31_status='W';
31
select count(*) from z31;
19051
22.发现有空记录
select count(*) from z31 where z31_sum is NULL;
3086
23.从备份表中将空记录恢复
update z31 set z31_sum=(select z31_sum from z31_bak where z31_rec_key =z31.z31_rec_key) where z31_sum is NULL;
24.查看是否还有空记录
select z31_rec_key,z31_description from z31 where z31_sum is NULL;
25.单独处理空记录(空记录的出现是因为在处理数据过程中有读者还书,所以以后再次处理这些事情需要将数据库停止服务)
update z31 set z31_sum='00000000000100' where z31_rec_key='1231103004 200406201916473';
update z31 set z31_sum='00000000000200' where z31_rec_key='1291402050 200406201938783';
update z31 set z31_sum='00000000000100' where z31_rec_key='1252203031 200406201957736';
update z31 set z31_sum='00000000000100' where z31_rec_key='1252203031 200406201958477';
26.查看是否有不合理的数据,即单笔过期罚款金额大小50元
select z31_rec_key,z31_sum from z31 where to_number(z31_sum)>5000 and z31_type=3 and z31_STATUS='O';
27.找出记录并处理掉
select count(*) from z31 where z31_type=3 and z31_status='O' and z31_key like '__________________________________________________________20030109%';
select z31_rec_key from z31 where z31_type=3 and z31_status='O' and z31_key like '__________________________________________________________20030109%';
delete from z31 where z31_rec_key='1272402065 200308040904954';
delete from z31 where z31_rec_key='1272402065 200308040904995';
delete from z31 where z31_rec_key='1272402065 200308040905024';
delete from z31 where z31_rec_key='1272402065 200308040905451';
delete from z31 where z31_rec_key='1044401008 200308041026870';
delete from z31 where z31_rec_key='1044401008 200308041026909';
delete from z31 where z31_rec_key='1044401008 200308041026951';
28.统计是否还有相似的记录
select count(*) from z31 where z31_type=3 and z31_key like '__________________________________________________________20030109%';
29.查找GUI未显示付款记录(过期2天)
select z31_rec_key,z31_sum from z31 where substr(z31_description,8,4) ='0002' and substr(z31_rec_key,13,8)='20040622' and z31_status='O';