SAP ABAP学习代码笔记05

*&---------------------------------------------------------------------*
*& Report ZMMD0005_TEST_DL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zmmd0005_test_dl.
*---OpenSQL基本语法和结构---*
"---定义透明表---"
TABLES: ztmm0045,marc,vbak,vbap,makt.

*---1、读取数据---*
"定义内表，参考透明表"
DATA: lv_tmb TYPE string,lv_jg TYPE string."定义变量
*lv_tmb = 'ZTMM0045'."-----问题1，如何根据变量确定透明表的定义。
DATA ls_filing LIKE TABLE OF ztmm0045 WITH HEADER LINE.
DATA ls_marc   LIKE TABLE OF marc     WITH HEADER LINE.
*lv_mandt     TYPE ztmm0045-mandt,
*      lv_zzbaxh    TYPE ztmm0045-zzbaxh,
*      lv_zzhs_code TYPE ztmm0045-zzhs_code,
*      lv_zzsppm    TYPE ztmm0045-zzsppm,
*      lv_zzmeins   TYPE ztmm0045-zzmeins,
*      lv_zzgg      TYPE ztmm0045-zzgg,
*      lv_zzout     TYPE ztmm0045-zzout,
*      lv_zzinput   TYPE ztmm0045-zzinput,
*      lv_zoaid     TYPE ztmm0045-zoaid,
*      lv_matnr     TYPE marc-matnr,
*      lv_werks     TYPE marc-werks,


SELECT SINGLE"单行查询
  mandt zzbaxh zzhs_code zzsppm zzmeins zzgg
*  zzout zzinput zoaid
  FROM ztmm0045
  INTO CORRESPONDING FIELDS OF ls_filing"SQL查询数据，存入到表头，工作区
  WHERE zoaid = '66'.
IF sy-subrc = '0'."判断执行结果
  lv_jg = '执行成功'.
ELSEIF sy-subrc = '4'.
  lv_jg = '执行失败'.
ENDIF.
"输出执行结果和内表查询数据。
WRITE:/'是否查询成功：',sy-subrc,lv_jg.
WRITE:/'集团：',ls_filing-mandt,
      /'备案序号：',ls_filing-zzbaxh,
      /'HS-CODE：',ls_filing-zzhs_code,
      /'商品品名：',ls_filing-zzsppm,
      /'基本单位：',ls_filing-zzmeins,
      /'规格：',ls_filing-zzgg,
      /'出口退税率%：',ls_filing-zzout,
      /'进口关税率%：',ls_filing-zzinput,
      /'OA单号：',ls_filing-zoaid.
ULINE.
*把查询的数据插入内表，通过LOOP循环输出内表数据。*
SELECT
  mandt zzbaxh zzhs_code zzsppm zzmeins zzgg
  zzout zzinput zoaid
  FROM ztmm0045
  INTO CORRESPONDING FIELDS OF TABLE ls_filing."OF TABLE，存入到内表。
CHECK sy-subrc = 0.
IF sy-subrc = '0'."判断执行结果
  lv_jg = '执行成功'.
ELSEIF sy-subrc = '4'.
  lv_jg = '执行失败'.
ENDIF.
LOOP AT ls_filing."LOOP只是用来循环输出内表数据。
  WRITE:/'是否查询成功：',sy-subrc,lv_jg,
         '执行到第',sy-dbcnt,'行',
        /'|集团：',ls_filing-mandt,
         '|备案序号：',ls_filing-zzbaxh,
         '|HS-CODE：',ls_filing-zzhs_code,
         '|商品品名：',ls_filing-zzsppm,
         '|基本单位：',ls_filing-zzmeins,
        /'|规格：',ls_filing-zzgg,
         '|出口退税率%：',ls_filing-zzout,
         '|进口关税率%：',ls_filing-zzinput,
         '|OA单号：',ls_filing-zoaid.
  ULINE.
ENDLOOP.
ULINE.
ULINE.
*循环查询多条记录*
SELECT "单行查询,语法：count()，sum()，avg()，max()，min()。
  mandt zzbaxh zzhs_code zzsppm zzmeins zzgg
  zzout zzinput zoaid
  INTO CORRESPONDING FIELDS OF ls_filing"SQL查询数据，存入到表头，工作区
  FROM ztmm0045.
  IF sy-subrc = '0'."判断执行结果
    lv_jg = '执行成功'.
  ELSEIF sy-subrc = '4'.
    lv_jg = '执行失败'.
  ENDIF.
*"输出执行结果和内表查询数据。
  WRITE:/'是否查询成功：',sy-subrc,lv_jg.
  WRITE:/ '执行到第',sy-dbcnt,'行',
         /'|集团：',ls_filing-mandt,
          '|备案序号：',ls_filing-zzbaxh,
          '|HS-CODE：',ls_filing-zzhs_code,
          '|商品品名：',ls_filing-zzsppm,
          '|基本单位：',ls_filing-zzmeins,
         /'|规格：',ls_filing-zzgg,
          '|出口退税率%：',ls_filing-zzout,
          '|进口关税率%：',ls_filing-zzinput,
          '|OA单号：',ls_filing-zoaid.
  ULINE.
ENDSELECT.
*---SELECT+标准函数---*
DATA: counts  TYPE i,
      total   TYPE p DECIMALS 2,
      average TYPE f,
      zzsppm  LIKE ls_filing-zzsppm.
SELECT "单行查询,语法：count()，sum()，avg()，max()，min()。
  zzsppm COUNT(*) SUM( zzout ) AVG( zzout )
  INTO ( zzsppm,counts,total,average )
  FROM ztmm0045
  GROUP BY zzsppm.
  IF sy-subrc = '0'."判断执行结果
    lv_jg = '执行成功'.
  ELSEIF sy-subrc = '4'.
    lv_jg = '执行失败'.
  ENDIF.
*"输出执行结果和内表查询数据。
  WRITE:/'是否查询成功：',sy-subrc,lv_jg.
  WRITE:/ '执行到第',sy-dbcnt,'行',
         /'|备案品名：',zzsppm,
          '|counts：',counts,
          '|total：',total,
          '|average：',average.
  ULINE.
  CHECK total > 10.
ENDSELECT.
*多表数据连接查询*
"------CDS视图
"在ABAP字典中创建数据库视图，查询时用这个视图作为数据源。
"------直接连接
"使用JOIN语句，进行多表连接
DATA:BEGIN OF customs OCCURS 0,"建立连表数据存储的内表。
       mandt     LIKE ztmm0045-mandt,
       zzbaxh    LIKE ztmm0045-zzbaxh,
       zzhs_code LIKE ztmm0045-zzhs_code,
       zzsppm    LIKE ztmm0045-zzsppm,
       zzmeins   LIKE ztmm0045-zzmeins,
       zzgg      LIKE ztmm0045-zzgg,
       zzout     LIKE ztmm0045-zzout,
       zzinput   LIKE ztmm0045-zzinput,
       zoaid     LIKE ztmm0045-zoaid,
       matnr     LIKE marc-matnr,
       gc01      LIKE marc-werks, "需要查询时重命名字段名
     END OF customs.
BREAK-POINT.
SELECT
  b~mandt b~zzbaxh b~zzhs_code b~zzsppm b~zzmeins b~zzgg b~zzout b~zzinput b~zoaid
  m~matnr m~werks AS gc01"通过AS重命名字段
  INTO CORRESPONDING FIELDS OF TABLE customs"自动匹配，需要字段名相同。
  FROM ztmm0045 AS b INNER JOIN marc AS m
  ON b~zzbaxh = m~zzbaxh "AND b~mandt = m~mandt
  PACKAGE SIZE 2"读取限定，限制循环次数。
  UP TO 4 ROWS "SQL一次性读取限定的条数、输出四条，循环两次输出，增加ENDSELECT语句。
  ORDER BY b~zzbaxh.
  BREAK-POINT.
  ULINE.
  ULINE.
  LOOP AT customs.
    WRITE: /'集团：',customs-mandt,
            '备案序号：',customs-zzbaxh,
            'HS-CODE：',customs-zzhs_code,
            '商品品名：',customs-zzsppm,
            '基本单位：',customs-zzmeins,
           /'规格：',customs-zzgg,
            '出口退税率%：',customs-zzout,
            '进口关税率%：',customs-zzinput,
           /'OA单号：',customs-zoaid,
            '物料料号：',customs-matnr,
            '工厂：',customs-gc01."重命名后的字段直接输出。
    ULINE.
  ENDLOOP.
ENDSELECT."嵌套循环需要增加ENDSELECT来避免死循环！
ULINE.
*----示例---
*TABLES:spfli,sflight.
*DATA:BEGIN OF xflight OCCURS 0,
*airpfrom LIKE spfli-airpfrom,
*airpto   LIKE spfli-airpto,
*fldate   LIKE sflight-fldate,
*deptime  LIKE spfli-deptime,
*END OF xflight.
*SELECT a~airpfrom a~airpto b~fldate a~deptime
*INTO CORRESPONDING FIELDS OF TABLE xflight
*FROM spfli AS a INNER JOIN sflight AS b
*ON a~carrid = b~carrid AND a~connid = b~connid
*PACKAGE SIZE 5
*UP TO 15 ROWS.
*"ORDER BY a~airpfrom a~airpto b~fldate a~deptime.
*ULINE.
*LOOP AT xflight.
*WRITE: / 'Airpfrom=', xflight-airpfrom, 'to=', xflight-airpto,'Flydate=',xflight-fldate.
*ENDLOOP.
*ENDSELECT.
*---2、更新数据---*
*字段更新---update<数据表>set<字段=值>where<条件>---*
BREAK-POINT."把006（BUI）改成PC(ST)。
UPDATE ztmm0045 SET zzmeins = 'BUI' WHERE zzmeins = 'ST'. "AND ZZMEINS = '007'.
IF sy-subrc = 0.
  WRITE:/ '字段更新成功'.
ELSEIF sy-subrc = 1.
  WRITE:/ '字段更新失败'.
ELSEIF sy-subrc = 4.
  WRITE:/ '字段更新异常'.
ENDIF.
*内表更新---update<数据表>from table<内表>---*
UPDATE ztmm0045 FROM TABLE customs.
IF sy-subrc = 0.
  WRITE:/ '内表更新成功'.
ELSEIF sy-subrc = 1.
  WRITE:/ '内表更新失败'.
ENDIF.
*工作区更新---update<数据表>from<工作区>---*
UPDATE ztmm0045 FROM customs.
IF sy-subrc = 0.
  WRITE:/ 'WA更新成功'.
ELSEIF sy-subrc = 1.
  WRITE:/ 'WA更新失败'.
ENDIF.
"WHERE zzbaxh ='1' AND zzbaxh ='AAA'.
"WHERE customs-zzbaxh ='1' AND customs-zzbaxh ='AAA'.
*---3、新增数据---*
*通过工作区新增---insert<数据表>from<工作区>---*
*清空工作区*
CLEAR customs.
CLEAR customs[].
*工作区赋值*
customs-mandt     = 'XXX'.
customs-zzbaxh    = '001-1314N'.
customs-zzhs_code = '9019'.
customs-zzsppm    = '测试用件'.
customs-zzmeins   = 'UI'.
customs-zzgg      = '用于测试'.
customs-zzout     = '7'.
customs-zzinput   = '13'.
customs-zoaid     = '1314'.
DATA: lv_zzbaxh TYPE string.
BREAK-POINT.
SELECT zzbaxh
  INTO lv_zzbaxh
  FROM ztmm0045
  WHERE zzbaxh = customs-zzbaxh.
ENDSELECT.
IF lv_zzbaxh IS NOT INITIAL.
  WRITE:/ '数据重复'.
ELSE.
  INSERT ztmm0045 FROM customs.
  IF sy-subrc = 0.
    WRITE:/ 'WA新增成功'.
  ELSEIF sy-subrc = 1.
    WRITE:/ 'WA新增失败'.
  ENDIF.
ENDIF.

*通过内表新增---insert<数据表>from table<内表>---*

*---4、删除数据---*
*条件判断删除---delete from<数据表>where<条件>---*
DELETE FROM ztmm0045 WHERE zzbaxh = '001-1314'.
IF sy-subrc = 0.
  WRITE:/ '数据删除成功'.
ELSEIF sy-subrc = 1.
  WRITE:/ '数据删除失败'.
ENDIF.
*条件判断删除---delete<数据表>from table<内表>---*
*条件判断删除---delete from<数据表>---*
*---5、更新数据---*
*单条数据更新---modify<数据表>from<工作区>---*
MODIFY ztmm0045 FROM customs."表中无数据则插入，表中有数据则更改。
IF sy-subrc = 0.
  WRITE:/ '数据更新成功'.
ELSEIF sy-subrc = 1.
  WRITE:/ '数据更新失败'.
ENDIF.
ULINE.
CLEAR customs.
*---6、利用内表进行多表联查---*
DATA:BEGIN OF it_vbak OCCURS 0,"建立连表数据存储的内表。
       mandt LIKE vbak-mandt,
       vbeln LIKE vbak-vbeln,
       matnr LIKE vbap-matnr,
     END OF it_vbak.
DATA it_makt LIKE TABLE OF makt WITH HEADER LINE."目标内表。
SELECT k~mandt k~vbeln p~matnr
  INTO CORRESPONDING FIELDS OF TABLE it_vbak"先用左联插入内表。
  FROM vbak AS k INNER JOIN vbap AS p
  ON k~vbeln = p~vbeln.
BREAK-POINT.
  IF it_vbak IS NOT INITIAL."判断内表非空，不然会取值错误。
    SELECT * FROM makt INTO it_makt
      FOR ALL ENTRIES IN it_vbak"关联第三个表和内表，存入目标内表。
      WHERE matnr = it_vbak-matnr.
    ENDSELECT.
  ENDIF.
  BREAK-POINT.
IF it_makt-matnr IS NOT INITIAL.
 LOOP AT it_makt.
    WRITE: /'集团：',it_makt-mandt,
            '物料编号：',it_makt-matnr,
            '物料描述：',it_makt-maktx.
    ULINE.
ENDLOOP.
ELSE.
   WRITE: /'无数据'.
ENDIF.