"Book expire does not display fine" processing summary
1. The random data, view data format to be processed 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 = '106140025 200405221414401';
2. Update Z31_SUM field Update Z31_SUM field Update Z31 set z31_sum = '00000000000250' Where z31_rec_key = '106140025 200405221414401';
3. Determine the structure and data storage method of the Z31 table
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. Statistical data to be processed SELECT COUNT (*) from Z31 WHERE Z31_STATUS = 'o' and z31_credit_debit = 'd' and z31_sum = '00000000000000';
5. Create a backup structure of the table 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 Ch AR (10)) 6. Data backup Z31 table to the backup table 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_TYPEfrom z31;
7. Use the left filling function to supplement the char2 characters to the left side of Char1, so that the length of CHAR1 is NLPAD (Char1, N, Char2)
8. Create a key field Data table Create Table Z31_Source (z31_rc_key char (27), z31_status char (1), z31_sum char (14), z31_description varcha2 (100)); 9. Copy the data field to be processed into the z31_source table
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. Re-statistically to process data number SELECT Count (*) from z31 where z31_status = 'o' and z31_type = 3 and z31_credit_debit = 'd';
11. Experimental string intercept function SUBSTR found SELECT SUBSTR (Z31_Description, 8, 4) from z31_source where z31_rec_key = '1011200007 200305060947702';
12. String conversion into digital functions Select TO_NUMBER (Substr (Z31_Description, 8, 4)) * 50 from z31_source where z31_rec_key = '1011200007 200305060947702';
13. Add a field Money to the z31_source table to store a fine amount ALTER TABLE Z31_Source Add Money Char (10);
14. Experimental One Data Update Money Field Update Z31_Source Set Money = TO_CHAR (TO_NUMBER (SUBSTR (Z31_Description, 8, 4)) * 50) Where z31_rec_key = '1011200007 200305060947702';
15. Update all recorded MONEY fields Update Z31_Source Set Money = To_Char (to_Number (Substr (Z31_Description, 8, 4)) * 50);
16. Update a record Z31_SUM field Update Z31_Source Set Z31_Sum = LPAD (Trim (Money), 14, '0') Where z31_rec_key = '1011200007 200305060947702'
17. Update all recorded Z31_SUM fields Update Z31_Source Set Z31_Sum = LPAD (Trim (Money), 14, '0');
18. Synchronize a record UPDATE Z31 SET Z31_SUM = (SELECT Z31_SUM from Z31_Source Where Z31_Rec_key = '1011200007 20030506094702') Where z31_rec_key = '1011200007 200305060947702';
19. See Results select z31_sum from z31 where z31_rec_key = '1011200007 200305060947702';. 20 synchronize all records update z31 set z31_sum = (select z31_sum from z31_source where z31_rec_key = z31.z31_rec_key) where z31_status = 'O';
21. At the same database as a backup for updated result 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'; 11444select count (*) from z31 Where z31_status = 'c'; 7573SELECT Count (*) from z31 where z31_status = 'w'; 31SELECT Count (*) from Z31; 19051
22. Discover a space record select count (*) from z31 where z31_sum is null; 3086
23. Recovered empty records from the backup table 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. Check if there is also a space record select z31_rec_key, z31_description from z31 where z31_sum is null;
25. Empty recording (empty recording is because there is a reader in the process of processing the data, so it needs to stop the database again later)
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. Check if there is unreasonable data, ie single expiration fine amount size 50 yuan SELECT Z31_RC_KEY, Z31_SUM from Z31 WHERE to_NUMBER (Z31_SUM)> 5000 and z31_type = 3 and z31_status = 'o';
27. The recording and disposed identify 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. Does statistics have similar records Select count (*) from z31 where z31_type = 3 and z31_key like '__________________________________ 20030109%';
29. Find the GUI did not show a payment record (expiration 2 days)
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';
