abap open sql 增删改查

*&---------------------------------------------------------------------*
*& Report ZPCYOPENSQL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zpcyopensql.

DATA: ls_flight TYPE sflight.

SELECT SINGLE carrid, connid, fldate, price
  FROM sflight
  INTO CORRESPONDING FIELDS OF @ls_flight
  WHERE carrid = 'LH' AND connid = '0400'.
IF sy-subrc = 0.
  WRITE: / 'carrid：', ls_flight-carrid.
  WRITE: / 'connid：', ls_flight-connid.
  WRITE: / 'fldate：', ls_flight-fldate.
  WRITE: / 'price：', ls_flight-price.
ENDIF.
SKIP.
SKIP.
WRITE: sy-datum.


DATA: lt_flights TYPE TABLE OF sflight.

SELECT *
  FROM sflight
  INTO TABLE @lt_flights
  UP TO 20 ROWS
  WHERE carrid = 'LH'.
IF sy-subrc = 0.
  LOOP AT lt_flights INTO DATA(ls_flight1).
    WRITE: / ls_flight1-connid, ls_flight1-fldate, ls_flight1-price.
  ENDLOOP.
ENDIF.

SELECT carrid, connid, fldate, price, seatsmax
  FROM sflight
  INTO TABLE @DATA(lt_flights2)
  UP TO 15 ROWS
  WHERE carrid = 'LH'.

" 遍历输出
LOOP AT lt_flights2 INTO DATA(ls_flight2).
  WRITE: / ls_flight2-carrid, ls_flight2-connid, ls_flight2-fldate, ls_flight2-price.
ENDLOOP.

" 带多个条件，按日期降序排序，仅取前 10 条
SELECT carrid, connid, fldate, price
  FROM sflight
  INTO TABLE @DATA(lt_top_flights)
  UP TO 10 ROWS
  WHERE carrid IN ('LH', 'UA')
    AND seatsmax > 200
  ORDER BY fldate DESCENDING.

WRITE: / '带多个条件，按日期降序排序，仅取前 10 条'.
LOOP AT lt_top_flights INTO DATA(lt_tops).
    WRITE:/ lt_tops-price.
  ENDLOOP.

" 聚合函数：查询每个航空公司的平均票价
SELECT carrid,  AVG( price ) AS avg_price
  FROM sflight
  INTO TABLE @DATA(lt_avg)
  GROUP BY carrid
  ORDER BY carrid.

  LOOP AT lt_avg INTO DATA(avgs).
      WRITE:/ avgs-carrid, avgs-avg_price.
    ENDLOOP.

SELECT a~carrid, a~connid, a~fldate, a~price, b~carrname
  FROM sflight AS a
  INNER JOIN scarr AS b ON a~carrid = b~carrid
  INTO TABLE @DATA(lt_join)
  UP TO 10 ROWS
  WHERE a~carrid = 'LH'.

LOOP AT lt_join INTO DATA(lt_join_row).
  WRITE:/ lt_join_row-carrid,lt_join_row-carrname.
  ENDLOOP.


  DATA: BEGIN OF it_makt OCCURS 0,
        matnr(18),
        maktx(40),
      END OF it_makt.

DATA: BEGIN OF it_makt_01 OCCURS 0,
        matnr(18),
        maktx(40),
      END OF it_makt_01.

DATA: BEGIN OF it_makt_02 OCCURS 0,
        matnr(18),
        maktx(40),
      END OF it_makt_02.

it_makt-matnr = '899'.
it_makt-maktx = '899 abandon'.
APPEND it_makt TO it_makt[].

it_makt-matnr = '809'.
it_makt-maktx = '809 abundant'.
APPEND it_makt TO it_makt[].

APPEND LINES OF it_makt[] TO it_makt_01[].

LOOP AT it_makt[] INTO DATA(itdata).
    WRITE: / itdata-matnr, itdata-maktx.
  ENDLOOP.

DATA num TYPE i.
DESCRIBE TABLE   it_makt[] LINES num.
WRITE:/ num.

IF it_makt[] IS INITIAL.
  WRITE: / sy-datum.
ELSE.
  WRITE:/ sy-uzeit.
ENDIF.

it_makt-matnr = 'case'.
it_makt-maktx = 'chain because'.
APPEND it_makt TO it_makt[].

move it_makt[] to it_makt_02[].

LOOP AT it_makt_02[] INTO DATA(itdata2).
    WRITE: / itdata2-matnr, itdata2-maktx.
  ENDLOOP.

  DATA: user_addr TYPE user_addr,
      system_id TYPE sysysid,
      user_para LIKE TABLE OF usr05 WITH HEADER LINE,
      msg_text(128).

CALL FUNCTION 'ZRFC_USER_READ'
    STARTING NEW TASK 'B1'
    DESTINATION 'GS4CLNT100'
    EXPORTING
      user_name    = 'JIAH'
    TABLES
      user_para    = user_para
    EXCEPTIONS
      communication_failure = 1    MESSAGE msg_text
      system_failure         = 2    MESSAGE msg_text.

IF user_para IS INITIAL.
    WRITE: 'No output from aRFC.'.
ELSE.
    LOOP AT user_para.
        WRITE: / user_para-bname,
                 user_para-parid,
                 user_para-parva.
    ENDLOOP.
ENDIF.