“书籍过期不显示罚款”处理概要

     “书籍过期不显示罚款”处理概要

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';