数据表的增改查

接到一个任务，是对数据库表的增改查，增和查现在已经比较熟练了，关键是改，还有一个要求是要求系统能自动更新修改人和修改日期以及时间。

找到数据库表，新增这三个字段

这里另提一句，一般数据库表不建议自己在不确定的情况下修改的，什么数据类型还是数据元素啥的，因为你不知道这个表关联了哪些程序，改了数据类型的话会很麻烦。

数据的查找：

很简单，就是一个最基本的报表的写法

1.定义一个内表用来装数据

2.从数据库表里取数据装入内表

3.alv显示数据

数据的新增

这里我的思路是通过excel上传数据新增数据，那种类似于点个按钮增加的还没研究会（sad）

1.SMW0上传模版

注意要选二进制数据，有一次不小心上传到HTML了结果找不到模版

上传模版之前有提过，就不详细写了

2.显示下载模版按钮

3.显示上传文件的输入框

4.读取excel

我在这里给时间日期以及用户赋了值，记录每次上传的数据

5.ALV显示

这里status = 'X'. 要加上，不然显示不了按钮

6.数据的保存

数据的修改

1.alv选择可以更改

这里第二个值为X的时候表示能够更改

2.获取修改后alv的数据

关键是这段代码，能够及时获取alv更改后的数据，不然，每次都要按一下ctrl s 才能更新gt_itab里的数据。

3.数据的保存

这样就结束辽

遇到了一个小问题，就是在alv界面修改的时候，小数点总是自动往前移，解决方法就是在要修改的字段前加上FIELDCAT_LN-INTTYPE = 'C'.

完整代码：

*&---------------------------------------------------------------------*
*& Report  ZLXY007
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZLXY007.

INCLUDE zpub_lvc_func."包含功能模块
INCLUDE <icon>."包含图标定义
TABLES: sscrfields,zboxsize.

* 定义类型池
TYPE-POOLS: vrm.
TYPE-POOLS: slis.

DATA:BEGIN OF gt_itab OCCURS 0.
INCLUDE TYPE ZBOXSIZE.
DATA:
END OF gt_itab.
DATA gt_itab1 LIKE TABLE OF gt_itab WITH HEADER LINE.

DATA ztext1 TYPE string."提示是否保存信息
DATA: functxt TYPE smp_dyntxt."定义动态文本类型变量
* 选择屏幕定义
PARAMETERS p1 RADIOBUTTON GROUP grp1 USER-COMMAND cmd1 DEFAULT 'X'."查询数据
PARAMETERS p2 RADIOBUTTON GROUP grp1   ."修改数据
PARAMETERS p3 RADIOBUTTON GROUP grp1   ."新增数据

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS: s_zcode1  FOR zboxsize-zcode MODIF ID M1,
                s_zsize1  FOR zboxsize-zsize MODIF ID M1.
SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS: s_zcode2  FOR zboxsize-zcode MODIF ID M2,
                s_zsize2  FOR zboxsize-zsize MODIF ID M2.
SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE TEXT-001.
PARAMETERS:  p_file  LIKE rlgrap-filename OBLIGATORY DEFAULT '.XLS'  MODIF ID M3.
SELECTION-SCREEN END OF BLOCK b3.

INITIALIZATION.
  sscrfields-ucomm  = 'FC01' .
  functxt-icon_id   = icon_export.  "文本字
  functxt-icon_text = '下载导入模版'.  "菜单制作器：
  sscrfields-functxt_01 = functxt.
  SELECTION-SCREEN: FUNCTION KEY 1.

AT SELECTION-SCREEN .
  CASE sscrfields-ucomm.
    WHEN 'FC01'."下载模版
      CALL FUNCTION 'ZDOWNLOAD_TEMPLE_FILE'
        EXPORTING
          p_templename = 'ZMMR120'.
    WHEN OTHERS .
  ENDCASE.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
    EXPORTING
      mask          = ',*.xls,*.xls;,*.xlsx,*.xlsx.'
    CHANGING
      file_name     = p_file
    EXCEPTIONS
      mask_too_long = 1
      OTHERS        = 2.


AT SELECTION-SCREEN OUTPUT.  " 指定在选择屏幕输出之前执行以下代码。
  LOOP AT SCREEN. "screen-active = 0.   0 隐藏，默认1 显示
    IF p1 <> 'X' AND screen-group1 = 'M1'.
      screen-active = 0.
    ENDIF.
    IF p2 <> 'X' AND screen-group1 = 'M2'.
      screen-active = 0.
    ENDIF.
    IF p3 <> 'X' AND screen-group1 = 'M3'.
      screen-active = 0.
    ENDIF.
    MODIFY SCREEN.
  ENDLOOP.

START-OF-SELECTION.
  IF p1 = 'X'.
    PERFORM get_data.
    PERFORM alv_data.
  ENDIF.
  IF p2 = 'X'.
    PERFORM get_data1.
    PERFORM deal_data.
  ENDIF.
  IF p3 = 'X'.
    PERFORM excel_data.
    PERFORM add_data.
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM GET_DATA.
  SELECT *
    FROM zboxsize
    INTO TABLE gt_itab
    WHERE zcode  IN s_zcode1
      AND zsize  IN s_zsize1.

ENDFORM.                    "GET_DATA

*&---------------------------------------------------------------------*
*&      Form  GET_DATA1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM GET_DATA1.
  SELECT *
    FROM zboxsize
    INTO TABLE gt_itab
    WHERE zcode  IN s_zcode2
      AND zsize  IN s_zsize2.

  SELECT *
    FROM zboxsize
    INTO TABLE gt_itab1
    WHERE zcode  IN s_zcode2
      AND zsize  IN s_zsize2.

ENDFORM.                    "GET_DATA


*&---------------------------------------------------------------------*
*&      Form  deal_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM deal_data.
  alv_clear_data.
  status = 'X'.        "保存按钮



  alv_add_data 'GT_ITAB' '' 'X' '' '' '' '' 'ZSIZE' '尺寸代码'.
  alv_add_data 'GT_ITAB' '' 'X' '' '' '' '' 'ZCODE' '代码'.
  FIELDCAT_LN-INTTYPE = 'C'.
  alv_add_data 'GT_ITAB' '' 'X' '' '' '' '' 'MENGE' '数量'.
  alv_add_data 'GT_ITAB' '' 'X' '' '' '' '' 'MEINS' '基本计量单位'.
  FIELDCAT_LN-INTTYPE = 'C'.
  alv_add_data 'GT_ITAB' '' 'X' '' '' '' '' 'ZBOXSIZE' '数量'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'CPUTM' '输入时间'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'AEDAT' '更改日期'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'USNAM' '用户名'.
  PERFORM get_leng TABLES  GT_ITAB i_fieldcat . "使ALV长度默认为其本身的字段
  alv_show_data   GT_ITAB.

ENDFORM.                    "deal_data

*&---------------------------------------------------------------------*
*&      Form  alv_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM alv_data.
*三、ALV显示
  alv_clear_data.
*  status = 'X'.        "

  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZSIZE' '尺寸代码'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZCODE' '代码'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'MENGE' '数量'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'MEINS' '基本计量单位'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZBOXSIZE' '数量'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'CPUTM' '输入时间'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'AEDAT' '更改日期'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'USNAM' '用户名'.
  PERFORM get_leng TABLES  GT_ITAB i_fieldcat . "使ALV长度默认为其本身的字段
  alv_show_data   GT_ITAB.
ENDFORM.                    "alv_data
*&---------------------------------------------------------------------*
*&      Form  ADD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM ADD_DATA.
  alv_clear_data.
  status = 'X'. "

  "设置可编辑字段
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZSIZE' '尺寸代码'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZCODE' '代码'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'MENGE' '数量'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'MEINS' '基本计量单位'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'ZBOXSIZE' '数量'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'CPUTM' '输入时间'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'AEDAT' '更改日期'.
  alv_add_data 'GT_ITAB' '' '' '' '' '' '' 'USNAM' '用户名'.

  PERFORM get_leng TABLES gt_itab i_fieldcat.
  alv_show_data gt_itab.

ENDFORM.                    "ADD_DATA
*&---------------------------------------------------------------------*
*&      Form  set_pf_status
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->RT_EXTAB   text
*----------------------------------------------------------------------*
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
  SET PF-STATUS 'ZSAVE'.
ENDFORM.                    "set_pf_status
*&---------------------------------------------------------------------*
*&      Form  user_command
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->R_UCOMM      text
*      -->RS_SELFIELD  text
*----------------------------------------------------------------------*
FORM user_command  USING r_ucomm LIKE sy-ucomm
                          rs_selfield TYPE slis_selfield.
  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      e_grid = l_o_grid1.
  CALL METHOD l_o_grid1->check_changed_data.



  TYPES: BEGIN OF ty_extab,
   row_id TYPE i,
 END OF ty_extab.
  DATA: et_index_rows TYPE STANDARD TABLE OF ty_extab.

  CASE r_ucomm.
    WHEN '&ZPY'.
      IF sy-subrc = 0.
*        MESSAGE 'E:数据有误，请检查' TYPE 'S' DISPLAY LIKE 'E'.
*        EXIT.
      ENDIF.
      ztext1 = '是否确定保存数据'.
*      PERFORM do_message.
      PERFORM save_data .
      rs_selfield-refresh = 'X'.
    WHEN OTHERS.
  ENDCASE.
ENDFORM.                    "user_command
*&---------------------------------------------------------------------*
*&      Form  sava_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM save_data.
*  LOOP AT gt_itab.
*    INSERT zboxsize FROM TABLE gt_itab.
*  ENDLOOP.
*  IF sy-subrc = 0.
*    MESSAGE '数据更新成功' TYPE 'S'.
*  ELSE.
*    MESSAGE '数据更新失败' TYPE 'S' DISPLAY LIKE 'E'.
*  ENDIF.

  IF p3 = 'X'.
    modify zboxsize FROM TABLE gt_itab.

  IF sy-subrc = 0.
    MESSAGE '数据更新成功' TYPE 'S'.
  ELSE.
    MESSAGE '数据更新失败' TYPE 'S' DISPLAY LIKE 'E'.
  ENDIF.

  ENDIF.

  IF p2 = 'X'.
    LOOP AT GT_ITAB.
    loop at gt_itab1 WHERE zcode = gt_itab-zcode and zsize = gt_itab-zsize.
      if ( gt_itab1-MENGE <> gt_itab-MENGE ) or ( gt_itab1-MEINS <> gt_itab-MEINS ) or ( gt_itab1-ZBOXSIZE <> gt_itab-ZBOXSIZE ).
        gt_itab-usnam = sy-uname. "自动记录用户
        gt_itab-aedat = sy-datum. "自动记录日期
        gt_itab-cputm = sy-uzeit. "自动记录时间
        MODIFY gt_itab.
      else.
        CONTINUE.
      ENDIF.
    ENDLOOP.
  ENDLOOP.
 update zboxsize FROM TABLE gt_itab.

  IF sy-subrc = 0.
    MESSAGE '数据更新成功' TYPE 'S'.
  ELSE.
    MESSAGE '数据更新失败' TYPE 'S' DISPLAY LIKE 'E'.
  ENDIF.

  ENDIF.






ENDFORM.                    "sava_data
*&---------------------------------------------------------------------*
*&      Form  save_data1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*FORM save_data1.
*
*  LOOP AT GT_ITAB.
*    loop at gt_itab1 WHERE zcode = gt_itab-zcode and zsize = gt_itab-zsize.
*      if ( gt_itab1-MENGE <> gt_itab-MENGE ) or ( gt_itab1-MEINS <> gt_itab-MEINS ) or ( gt_itab1-ZBOXSIZE <> gt_itab-ZBOXSIZE ).
*        gt_itab-usnam = sy-uname. "自动记录用户
*        gt_itab-aedat = sy-datum. "自动记录日期
*        gt_itab-cputm = sy-uzeit. "自动记录时间
*        MODIFY gt_itab.
*      else.
*        CONTINUE.
*      ENDIF.
*    ENDLOOP.
*  ENDLOOP.
* update zboxsize FROM TABLE gt_itab.
*
*  IF sy-subrc = 0.
*    MESSAGE '数据更新成功' TYPE 'S'.
*  ELSE.
*    MESSAGE '数据更新失败' TYPE 'S' DISPLAY LIKE 'E'.
*  ENDIF.
*
*ENDFORM.                    "sava_data

*&---------------------------------------------------------------------*
*&      Form  excel_data
*&---------------------------------------------------------------------*
*       excel取数，读取excel导入的目标数据
*----------------------------------------------------------------------*
FORM excel_data.
  DATA: i_excel TYPE TABLE OF alsmex_tabline .
  DATA w_excel TYPE alsmex_tabline.
  DATA: zitem TYPE i.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = '1'"从第一列开始读取
      i_begin_row             = '2'                         "从第1行开始读取
      i_end_col               = '5' "Excel的列数，一共读取八列
      i_end_row               = '9999'
    TABLES
      intern                  = i_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE e208(00) WITH 'EXCEL读取错误' RAISING error_msg.
  ENDIF.
  FIELD-SYMBOLS : <fs> .
  SORT i_excel BY row col .
  DATA num_col TYPE i .
  LOOP AT i_excel INTO w_excel.
    CASE w_excel-col.
      WHEN 1.
        gt_itab-ZSIZE = w_excel-value.
      WHEN 2.
        gt_itab-ZCODE = w_excel-value.
      WHEN 3.
        gt_itab-MENGE = w_excel-value.
      WHEN 4.
        gt_itab-MEINS = w_excel-value.
      WHEN 5.
        gt_itab-ZBOXSIZE = w_excel-value.
      WHEN OTHERS.
    ENDCASE.
    AT END OF row.
      gt_itab-usnam = sy-uname. "自动记录用户
      gt_itab-aedat = sy-datum. "自动记录日期
      gt_itab-cputm = sy-uzeit. "自动记录时间
      APPEND gt_itab.
      CLEAR:gt_itab.
    ENDAT .
    CLEAR w_excel.
  ENDLOOP.
ENDFORM.                    "excel_data