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

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

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

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字段

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

• 本文已收录于以下专栏：

## 我和交警打官司的前前后后 (zz)

• is2120
• 2012年04月16日 21:10
• 21360

## Android处理token失效的处理方法

• xulike1990
• 2017年03月06日 16:51
• 6087

## Tomcat处理过期Session

• shi2huang
• 2016年09月08日 16:27
• 871

## 大型Web 网站 Asp.net Session过期你怎么办

• dz45693
• 2015年08月13日 10:51
• 2169

## 为什么农民宁可被拘留也要烧秸秆？对烧秸秆农民拘留、罚款，非解决问题之道

• zkl99999
• 2015年10月23日 09:40
• 3173

## 表单过期的处理

• huijiabahaizi
• 2014年10月21日 19:23
• 290

## ajax请求时session已过期处理方案

ajax请求时session已过期处理方案 web系统中常常使用拦截器或过滤器实现权限拦截，判断用户是否登陆，若未登陆，跳转到登陆页，但当浏览器发的是ajax请求时，浏览器不会正常跳...
• zhaoshuliok
• 2017年01月24日 18:18
• 1437

## memcache 的过期数据删除机制

memcache 的过期数据删除机制        1.stats 命令可以查看memcache 的当前状态，一共存过多少记录   total_item，以及当前有效的记录 curr_items，当...
• liang135238
• 2016年01月30日 16:38
• 810

## 今后，若你的公众号还按老方式发广告，罚款高的可达百万。有公众号已经被惩处了。

• tsr106
• 2016年08月04日 18:34
• 1493

## Vue 拦截器对token过期处理

• qq_36947128
• 2018年01月15日 11:03
• 139

举报原因： 您举报文章：“书籍过期不显示罚款”处理概要 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)