SAP ABAP OPEN SQL实例

1.SQL执行错误类 CX_SQL_EXCEPTION

   获取错误消息：l_text = cx_sql_exception->get_text( ).

2.数据插入

  A.单条插入：INSERT <tabname> FROM <wa>

  B.多条插入: itab 内表的行结构也必须和数据库表的行结构一致； ACCEPTING DUPLICATE KEYS ：如果现出关键字相同条目，       系统将 SY-SUBRC 返回 4 ，并跳过该条目，但其他数据会插入进去

  INSERT <tabname> FROM TABLE < itab > [ ACCEPTING DUPLICATE KEYS ]

3.数据更新：

   A.单条更新:UPDATE dbtab FROM wa

   B.多条更新:UPDATE dbtab SET f1 = g1 … fi = gi WHERE <conditions>

                      UPDATE dbtab FROM TABLE itab

4.数据删除:

   A.单条删除:DELETE dbtab FROM wa

   B.多条删除:DELETE dbtab FROM TABLE itab

                      DELETE FROM dbtab WHERE <conditions>

5.插入或更新

   A.单行操作:MODIFY dbtab FROM wa

   B.多行操作:MODIFY dbtab FROM TABLE itab

6.数据查询

  A.CASE用法

  SELECT EBELN,
         EBELP,
         VGABE,
         CASE VGABE
           WHEN  '1'  THEN '物料凭证'  "此处也可以返回表中字段 
           ELSE  '发票凭证'
         END AS VGABT ,
         BELNR,
         BUZEI
    FROM EKBE
   WHERE EBELN IN @S_EBELN
    INTO TABLE @GT_EKBE.

 

B.SUBSTRING ,LPAD,LEFT

  SELECT EBELN,
         EBELP,
         VGABE,
         CASE VGABE
           WHEN  '1'  THEN '物料凭证'
           ELSE  '发票凭证'
         END AS VGABT ,
         BELNR,
         SUBSTRING( BELNR,1,5 ) AS BELNRT,  "字符转换截取
         LEFT( BELNR,5 ) AS BELNR1,         "字符截取
         LPAD( BELNR,20,'X' ) AS BELNR2,    "字符补位填值
         BUZEI
*         @SPACE,                           ”为字段填空值
    FROM EKBE
   WHERE EBELN IN @S_EBELN
    INTO TABLE @GT_EKBE.

C.CONCAT,字段连接

 SELECT CONCAT( CONCAT( LPAD( carrid,2,'A' ),

                       LPAD( carrname,21,'B' ) ),

               LPAD( url,40,'C' ) ) AS line

       FROM scarr

       INTO TABLE @DATA(result).


 cl_demo_output=>display( result ).

D.LPAD在关联条件中使用填充位数,解决内外部造成无法匹配的原因

  SELECT EKBE~EBELN,
         EKBE~EBELP,
         EKBE~GJAHR,
         EKBE~BELNR,
         RSEG~BUZEI,
         RSEG~SHKZG
    FROM EKBE
   INNER JOIN RSEG
      ON LPAD( EKBE~BUZEI,6,'0' ) = RSEG~BUZEI "EKBE~BUZEI N型 4位 ，RSEG~BUZEI N型 6位
     AND EKBE~BELNR = RSEG~BELNR
     AND EKBE~GJAHR = RSEG~GJAHR
   WHERE EKBE~EBELN IN @S_EBELN
    INTO TABLE @DATA(result1).

 cl_demo_output=>display( result1 ).

动态OPEN SQL

SELECT ( column ) FROM ( dbtab )  WHERE (cond)
       INTO CORRESPONDING   FIELDS OF TABLE itab .

 TABLES:MKPF.

  SELECT-OPTIONS:
      S_MBLNR FOR MKPF-MBLNR .
  PARAMETERS L_TYPE TYPE C.

  START-OF-SELECTION.

TYPES:
  BEGIN OF TY_MKPF,

    MBLNR TYPE MKPF-MBLNR,
    MJAHR TYPE MKPF-MJAHR,
    VGART TYPE MKPF-VGART,
    BLART TYPE MKPF-BLART,
    ZEILE TYPE MSEG-ZEILE,
    MATNR TYPE MSEG-MATNR,
    WERKS TYPE MSEG-WERKS,
  END OF TY_MKPF,
  TT_MKPF TYPE STANDARD TABLE OF TY_MKPF,
  line_type TYPE c LENGTH 72.

  DATA:
    LT_MKPF TYPE TT_MKPF,
    L_COND TYPE STRING,
    L_DBTAB TYPE STRING,
    L_SUBRC TYPE SY-SUBRC,
    LT_COLUMN TYPE STANDARD TABLE OF line_type.

  CLEAR:LT_COLUMN,
        L_DBTAB,
        L_COND.

  IF L_TYPE = '1'.
*   多表关联查询
*  抽取元素编辑
   APPEND 'MKPF~MBLNR,' TO LT_COLUMN.
   APPEND 'MKPF~MJAHR,' TO LT_COLUMN.
   APPEND 'MKPF~VGART,' TO LT_COLUMN.
   APPEND 'MKPF~BLART,' TO LT_COLUMN.
   APPEND 'MSEG~ZEILE,' TO LT_COLUMN.
   APPEND 'MSEG~MATNR,' TO LT_COLUMN.
   APPEND 'MSEG~WERKS' TO LT_COLUMN.
*   关联条件编辑
   L_DBTAB = 'MKPF INNER JOIN MSEG '
   & 'ON MKPF~MBLNR = MSEG~MBLNR AND '
   & 'MKPF~MJAHR = MSEG~MJAHR '.
   L_COND = 'MKPF~MBLNR IN @S_MBLNR'.

  ELSE.
*    单表查询
   APPEND '*' TO LT_COLUMN.
   L_DBTAB = 'MKPF'.
   L_COND = 'MBLNR IN @S_MBLNR'.

  ENDIF.

  PERFORM FRM_DB_SELECT USING LT_COLUMN
                              L_DBTAB
                              L_COND
                        CHANGING LT_MKPF
                                 L_SUBRC.

 cl_demo_output=>display( LT_MKPF ).

FORM FRM_DB_SELECT  USING    UT_COLUMN TYPE STANDARD TABLE
                             U_DBTAB TYPE STRING
                             U_COND TYPE STRING
                    CHANGING CT_DATA TYPE STANDARD TABLE
                             C_SUBRC TYPE SY-SUBRC.

  SELECT (UT_COLUMN)   FROM (U_DBTAB)
    WHERE (U_COND)
    INTO CORRESPONDING FIELDS OF TABLE @CT_DATA.
.
  C_SUBRC = SY-SUBRC.

ENDFORM.

单表查询

多表查询