查找数据库表内包含的特殊字符
查找数据库表内包含的特殊字符
原创 ABAP老白 ABAP老白 2023-02-17 21:00 发表于北京
最近比较忙，稍微介绍一下。
本程序的作用是查找并删除数据库表内包含的特殊字符。
通过接口或者Excel上载创建的主数据描述，凭证数据的文本字段等，偶尔会有包含特殊字符的情况，且大部分时候是不可见字符，比如制表符，回车换行，0x0000等等。如果没有工具程序，只用SE16等查找或者更改非常困难，所以特意做了一个程序来干这个事情。
辅助功能1、显示可见字符的ASCII码
辅助功能2、自动创建日志表
辅助功能3、自定义的更新功能(有更改日志）
程序的运行界面：
下面是优美的代码~
下面是优美的代码~
*&---------------------------------------------------------------------*
*& Report ZCHECKSPECIALHEX
*& 检查/修正表内的CNDT类型字段值包含的特殊字符
*&---------------------------------------------------------------------*
*& Baitianzhen
*& 2022.04.16
*&---------------------------------------------------------------------*
REPORT zcheckspecialhex NO STANDARD PAGE HEADING .TYPE-POOLS: sscr,slis.
TABLES: dd02v,dd03l,tadir,sscrfields.DATA: fldct TYPE lvc_t_fcat WITH HEADER LINE,slayt TYPE lvc_s_layo,
repid TYPE sy-repid,
subrc TYPE sy-subrc,
char45 TYPE char45,
tabix TYPE sy-tabix,
fmname TYPE funcname,
whstr TYPE string,
tmpstr TYPE string,
numstr TYPE string,
keystr TYPE string,
cspace TYPE char4,
tmpint TYPE i,
percnt TYPE i,
cflag.
DATA: set_str TYPE string,con_str TYPE string.
DATA: cxroot TYPE REF TO cx_root,excmsg TYPE char255.
DATA: paraname TYPE char40,stru_type TYPE REF TO cl_abap_structdescr,
itab_type TYPE REF TO cl_abap_tabledescr,
comp_des TYPE abap_componentdescr,
comp_tab TYPE abap_component_tab WITH HEADER LINE.
DATA: logtname TYPE tablename VALUE 'ZCSL'.DATA: BEGIN OF wa_value ,fldname TYPE string,
fldtype TYPE string,
fldvalue TYPE string,
hexvalue TYPE string,
END OF wa_value.DATA: BEGIN OF it_flds OCCURS 0 ,fldname TYPE fieldname,
ref_tab TYPE tabname,
ref_fld TYPE fieldname,
convext TYPE convexit,
inttype TYPE inttype,
END OF it_flds.DATA: BEGIN OF wa_log,mandt TYPE mandt ,
tabname TYPE tabname,
objectid TYPE cdobjectv,
fieldname TYPE fieldname,
erdat TYPE erdat ,
erzet TYPE erzet ,
ernam TYPE ernam ,
value_old TYPE cdfldvalo,
value_new TYPE cdfldvaln,
END OF wa_log.DATA: it_fkey LIKE TABLE OF it_flds WITH HEADER LINE.DATA: it_fnok LIKE TABLE OF it_flds WITH HEADER LINE.DATA: it_dd03 TYPE TABLE OF dd03p WITH HEADER LINE.DATA: rf_tabl TYPE REF TO data,rf_twa TYPE REF TO data,
rf_out TYPE REF TO data,
rf_owa TYPE REF TO data.
FIELD-SYMBOLS: <fs_out> TYPE STANDARD TABLE,
<fs_tabl> TYPE STANDARD TABLE,
<fsx> TYPE x, <fs_owa>, <fs_twa>, <fs_fld>, <fs>.
SELECTION-SCREEN FUNCTION KEY 1.SELECTION-SCREEN FUNCTION KEY 2.SELECTION-SCREEN FUNCTION KEY 3.SELECTION-SCREEN FUNCTION KEY 4.SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE t01.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (31) ctxt0 FOR FIELD p_tname .PARAMETERS: p_tname TYPE tabname16 MEMORY ID dtb .SELECTION-SCREEN COMMENT 52(31) ctxtl .SELECTION-SCREEN END OF LINE.SELECT-OPTIONS s_type FOR cflag NO INTERVALS.SELECT-OPTIONS s_flds FOR dd03l-fieldname NO INTERVALS MATCHCODE OBJECT dd_fieldname.SELECT-OPTIONS s_conl FOR char45 NO INTERVALS LOWER CASE.SELECT-OPTIONS s_sfld FOR dd03l-fieldname NO INTERVALS MATCHCODE OBJECT dd_fieldname.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (31) ctxt1 FOR FIELD p_hchar .PARAMETERS: p_hchar TYPE char18 DEFAULT '0000'.SELECTION-SCREEN PUSHBUTTON 52(9) but03 USER-COMMAND space .SELECTION-SCREEN PUSHBUTTON 62(9) but04 USER-COMMAND newline .SELECTION-SCREEN PUSHBUTTON 72(9) but00 USER-COMMAND minchar .SELECTION-SCREEN END OF LINE.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN PUSHBUTTON 52(9) but02 USER-COMMAND tab .SELECTION-SCREEN PUSHBUTTON 62(9) but05 USER-COMMAND cr_lf .SELECTION-SCREEN PUSHBUTTON 72(9) but01 USER-COMMAND maxchar .SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK b1.SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE t02.PARAMETERS p_proind AS CHECKBOX .
PARAMETERS p_update AS CHECKBOX USER-COMMAND updchk.
SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN POSITION 4.PARAMETERS pr_rep RADIOBUTTON GROUP typ DEFAULT 'X' MODIF ID a.
SELECTION-SCREEN COMMENT (30) ctxt3 FOR FIELD pr_rep MODIF ID a.PARAMETERS p_conv AS CHECKBOX MODIF ID a.
SELECTION-SCREEN COMMENT (14) ctxt6 FOR FIELD p_conv MODIF ID a.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN POSITION 4.PARAMETERS pr_clr RADIOBUTTON GROUP typ MODIF ID a.
SELECTION-SCREEN COMMENT (12) ctxt5 FOR FIELD pr_clr MODIF ID a.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK b2.SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE t03.SELECTION-SCREEN COMMENT /1(79): txt1,txt2,txt3,txt4,txt5,txt6,txt7,txt8.SELECTION-SCREEN END OF BLOCK b3.SELECTION-SCREEN BEGIN OF SCREEN 1100 AS WINDOW .SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (8) ctxt7 FOR FIELD p_fval.PARAMETERS: p_fval TYPE char255 .SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF SCREEN 1100.SELECTION-SCREEN BEGIN OF SCREEN 1200 AS WINDOW .SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (6) ctxt8 FOR FIELD p_fval.PARAMETERS: p_uptab TYPE tabname16 OBLIGATORY.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (6) ctxt9 FOR FIELD p_fval.PARAMETERS: p_upfld TYPE fieldname VISIBLE LENGTH 10 OBLIGATORY .SELECTION-SCREEN COMMENT (3) ctxta FOR FIELD p_upval.PARAMETERS: p_upval TYPE string VISIBLE LENGTH 24 LOWER CASE.PARAMETERS: p_astxt AS CHECKBOX DEFAULT 'X'.SELECTION-SCREEN COMMENT (6) ctxtc FOR FIELD p_astxt.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT (6) ctxtb FOR FIELD p_fval.PARAMETERS: p_upwhe TYPE string LOWER CASE OBLIGATORY.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF SCREEN 1200.AT SELECTION-SCREEN OUTPUT.
sscrfields-functxt_01 = 'ASCII码'.
sscrfields-functxt_02 = '创建日志表'.
sscrfields-functxt_03 = '查看日志'.
sscrfields-functxt_04 = '单个字段更新'.
t01 = '数据'.
t02 = '选项'.
t03 = '使用说明'.
ctxt0 = '表名'.
ctxt1 = '特殊字符的HEX'.
ctxt3 = 'C类型过滤掉特殊字符，NDT类型改为初始值'.
ctxt5 = '字段数据清空为初始值'.
ctxt6 = '并将C类型数据转为内部格式'.
ctxt7 = '外部格式:'.
ctxt8 = 'UPDATE'.
ctxt9 = 'SET'.
ctxta = ' = '.
ctxtb = 'WHERE'.
ctxtc = '常量'.
but00 = 'MinChar'.
but01 = 'MaxChar'.
but02 = 'Tab'.
but03 = 'Space'.
but04 = 'NewLine'.
but05 = 'CR_LF'.
%_p_proind_%_app_%-text = '显示查找进度'.
%_p_update_%_app_%-text = '更改数据库数据'.
%_p_tname_%_app_%-text = '表名'.
%_p_hchar_%_app_%-text = '特殊字符的HEX'.
%_s_type_%_app_%-text = '字段类型(C/N/D/T)'.
%_s_flds_%_app_%-text = '字段名'.
%_s_conl_%_app_%-text = 'WHERE语句'.
%_s_sfld_%_app_%-text = '额外输出字段'.
txt1 = `1、如果不输入要检查字段，则检查表内所有C/N/D/T类型的非KEY字段`.
txt2 = `2、WHERE语句可以不填写，也可以填写多行，每行不要超过45字符`.
txt3 = `3、默认输出KEY字段和包含特殊字符的字段，如果想要输出其他字段，可以放到“额外输出字段”`.
txt4 = `4、如果不勾中"更新数据库数据"，则只列出数据，不更新数据库`.
txt5 = `5、更新数据库的时候，使用INITIAL值覆盖或者只去掉特殊字符`.
txt6 = `6、如果勾中数据转为内部格式，则先去掉特殊字符然后根据表内字段对应域的转换例程转换为内部格式`.
txt7 = `7、HEX为空会列出所有在数据库取出的CNDT类型数据，排查错误使用，不支持更新模式`.
txt8 = `8、HEX为Space有特殊用法，可以查出非INITIAL字段包含空格的情况，比如日期或时间值为空格的,不可以使用过滤模式`.
CLEAR ctxtl.
SELECT SINGLE ddtext INTO ctxtlFROM dd02t
WHERE tabname = p_tname AND
ddlanguage = sy-langu.
LOOP AT SCREEN.IF p_update = ''.IF screen-group1 CA 'A'.screen-active = '0'.
ENDIF.ENDIF.IF screen-name CS 'P_PROIND'.screen-input = '0'.
ENDIF.MODIFY SCREEN.ENDLOOP.IF sy-dynnr = '1100'.LOOP AT SCREEN.screen-input = '0'.
MODIFY SCREEN.ENDLOOP.SET CURSOR FIELD 'CTXT7'.
ENDIF.IF sy-dynnr = '1200'.PERFORM selscreen_ext USING 'GET SPOS'.
ENDIF.INITIALIZATION.
PERFORM get_hchar USING space CHANGING cspace.
AT SELECTION-SCREEN.
CASE sscrfields-ucomm.
WHEN 'FC01'.
LEAVE TO LIST-PROCESSING .
NEW-PAGE NO-HEADING NO-TITLE.
SET PF-STATUS space.
SUPPRESS DIALOG.
PERFORM ascii.
WHEN 'FC02'.
PERFORM fill_dd03 USING : 'MANDT ' 'MANDT' 'X',
'TABNAME ' 'TABNAME' 'X',
'OBJECTID ' 'CDOBJECTV' 'X',
'FIELDNAME' 'FIELDNAME' 'X',
'ERDAT ' 'ERDAT' 'X',
'ERZET ' 'ERZET' 'X',
'ERNAM ' 'ERNAM' 'X',
'VALUE_OLD' 'CDFLDVALO' '',
'VALUE_NEW' 'CDFLDVALN' ''.
SELECT SINGLE * FROM tadirWHERE pgmid = 'R3TR' AND
object = 'PROG' AND
obj_name = sy-repid.
PERFORM create_table TABLES it_dd03
USING 'ZCSL' 'ZCHECKSPECIALHEX日志' tadir-devclass .
WHEN 'FC03'.
CONCATENATE '/1BCDWB/DB' logtname INTO char45 .
SUBMIT (char45) VIA SELECTION-SCREEN AND RETURN .
WHEN 'FC04'.
CALL SELECTION-SCREEN 1200 STARTING AT 32 6 .CHECK sy-subrc = 0 .
PERFORM update_single_fld USING p_uptab p_upfld p_upval p_upwhe p_astxt .
WHEN 'MINCHAR'.
PERFORM get_hchar USING %_minchar CHANGING p_hchar.
WHEN 'MAXCHAR'.
PERFORM get_hchar USING %_maxchar CHANGING p_hchar.
WHEN 'TAB'.
PERFORM get_hchar USING %_horizontal_tab CHANGING p_hchar.
WHEN 'SPACE'.
PERFORM get_hchar USING space CHANGING p_hchar.
WHEN 'NEWLINE'.
PERFORM get_hchar USING %_newline CHANGING p_hchar.
WHEN 'CR_LF'.
PERFORM get_hchar USING %_cr_lf CHANGING p_hchar.
WHEN 'UPDCHK'.
CHECK p_update = 'X'.
CALL FUNCTION 'POPUP_TO_CONFIRM'EXPORTING
titlebar = '确认'
text_question = '本程序会直接更改数据库数据！请在专业人员指导下使用，继续选择更改吗？'
default_button = 'A'IMPORTING
answer = cflag.
IF cflag <> '1'.p_update = ''.
ENDIF.ENDCASE.START-OF-SELECTION.
PERFORM checkinput.
PERFORM savelog(zreplog) USING sy-repid '' IF FOUND.
PERFORM get_dd03 USING p_tname.
PERFORM create_itab.
PERFORM getdata.
PERFORM updatelog(zreplog) IF FOUND.
CHECK <fs_out> IS NOT INITIAL.
PERFORM outdata.
*&---------------------------------------------------------------------*
*& Form checkinput
*&---------------------------------------------------------------------*
FORM checkinput.SELECT SINGLE * FROM dd02v WHERE tabname = logtname.IF sy-subrc <> 0 AND p_update = 'X'.MESSAGE s000(oo) WITH '请先创建日志表'.
STOP.
ENDIF.IF p_tname IS INITIAL.MESSAGE s000(oo) WITH '表名必输'.
STOP.
ENDIF.tmpint = strlen( p_hchar ) MOD 4.
IF tmpint <> 0.MESSAGE s000(oo) WITH 'HEX长度必须是4的倍数'.
STOP.
ENDIF.IF p_hchar CN '0123456789ABCDEF '....
