ABAP New Features - DB Access

New Commands in OpenSQL

CASE Statements Inserted into SQL Queries

普通的Open SQL如下所示：

SELECT monster_number monste r_name
  FROM zt_monsters
  INTO CORRESPONDING FIELDS OF TABLE monster_table
  WHERE monster_number = monster_number .

新特性中提供了case语句

    SELECT name, monster_number,
      CASE
        WHEN sanity <= 10 AND strength_num >= 75 THEN @lc_scary2
        WHEN sanity <= 25 AND strength_num >= 50 THEN @lc_scary1
        ELSE @lc_scary3
      END AS scariness_string
      FROM ztsm_monsters
      WHERE monster_number = @monster_number
      INTO CORRESPONDING FIELDS OF TABLE @table_of_monsters.

上面代码中，case中的@符号让编译器知道后面跟的不是数据库表字段，新特性中字段间用逗号隔开，INTO放在最后。@及逗号将告诉编译器采用新的严格语法检查，换句话说如果语句中用了case而不用@ 逗号等新特性符号，将会报错。

Calculations within SQL Statements

以前我们要一些统计计算字段时，首先通过SELECT取到内表，然后在LOOP中进行计算。如下所示

    SELECT name monster_number strength_num sanity ##too_many_itab_fields "in the world
      FROM ztsm_monsters
      INTO CORRESPONDING FIELDS OF TABLE table_of_monsters.

    LOOP AT table_of_monsters ASSIGNING <monster_details>.
      converted_strength = <monster_details>-strength_num.
      converted_sanity   = <monster_details>-sanity.
      <monster_details>-scary_ratio =
      converted_strength / converted_sanity.
    ENDLOOP.

新特性中，可以在select中直接进行计算，使用CAST进行强制类型转换

    SELECT name, monster_number,
      CAST( strength_num AS FLTP ) / CAST( sanity AS FLTP )
      AS scary_ratio
      FROM ztsm_monsters
      WHERE monster_number = @monster_number
      INTO CORRESPONDING FIELDS OF TABLE @table_of_monsters.

Improvements in WHERE Clause and Friends

在where条件中，也进行了改进，可以进行计算及等于调用返回值。@DATA(table)可以直接定义结果内表。

    SELECT monster_number, tax_number
      FROM ztmonster_header
      WHERE age + sanity > 100
      INTO TABLE @DATA(old_sane_monster_list).

    SELECT monster_number, tax_number
      FROM ztmonster_header
      WHERE hat_size = @( hat_size_of_the_day( ) )
      INTO TABLE @DATA(fashionable_monster_list).

String Functions in SELECT Statements

其他可以用LPAD添加前导0，CONCAT拼接字符串等操作。

    SELECT CONCAT( monster_number, REPLACE( 'EVIL', 'VERY_EVIL' , name ) ) AS monster_description,
           LENGTH( tax_number )                                            AS tax_type
     FROM ztmonster_header
     WHERE hat_size = @( hat_size_of_the_day( ) )
     INTO TABLE @DATA(evilized_monster_tax_types).

Creating While Reading

以前我们先定义好内表的结构再进行读取工作。新特性中，使用@DATA在读取数据库表时同时定义好了内表。

    SELECT name, monster_number, sanity
      FROM ztsm_monsters
      WHERE name = 'FRED'
      INTO TABLE @DATA(table_of_monsters2).

Buffering Improvements

定义Buffer的表，普通SQL中，当用主键number查询时，不进行数据库交互，当用select single with name查询时，会提示没有使用buffer。7.4开始，定义name为second index时，使用select single也将使用buffer。
Buffer表使用 FOR ALL ENTRIES时，将其看作Loop的select single语句，将跳过直接的数据库表访问。
7.4开始可以用@ABAP_TRUE来确认表中是否有该条记录，代码如下。

    SELECT SINGLE @abap_true
      FROM ztsm_monsters
      WHERE monster_number = @id_monster_number
      INTO @DATA(result).

    IF result = abap_true.
      MESSAGE 'Monster Exists!' TYPE 'I'.
    ENDIF.

Inner Join Improvements

以前我们首先定义好内表，然后通过如下代码来读取记录

    SELECT ztsm_monsters~name     ztsm_monsters~monster_number
           ztsm_monsters~sanity   ztsm_monsters~strength_num
           ztsm_monsters~hat_size ztsm_monsters~no_of_heads
           ztmo_hats~bar_code
      FROM ztsm_monsters
      INNER JOIN ztmo_hats
      ON ztsm_monsters~hat_size = ztmo_hats~hat_size
      INTO CORRESPONDING FIELDS OF TABLE table_of_monsters
      WHERE name = 'FRED'.

新特性中，可以用如下代码，其中*表示该表所有字段，通过@DATA直接定义了输出内表的结构。

    SELECT ztsm_monsters~*,
           ztmo_hats~bar_code
      FROM ztsm_monsters
      INNER JOIN ztmo_hats
      ON ztsm_monsters~hat_size = ztmo_hats~hat_size
      WHERE name = 'FRED'
      INTO TABLE @DATA(t_monsters2).

新特性中可以像如下代码，添加更多的匹配条件。

INNER JOIN zt_monster_pets
  ON zt_monster_pets-owner EQ zt_monsters-monster_number
  AND zt_monster_pets-type LIKE 'GIGANTIC%'
  AND zt_monster_pets-species IN ( ' SHARK ', ' CROCODI LE', ' DINOSAUR ' )

Union

读取到内表时，没有的字段可以用' ' AS tax_number这种方式来替代。

    SELECT monster_number AS monster_number, tax_number AS tax_number
      FROM ztmonster_header
      UNION DISTINCT
    SELECT monster_number AS monster_number, ' ' AS tax_number
      FROM ztmonster_op_itm
      UNION DISTINCT
    SELECT monster_number AS monster_number, ' ' AS tax_number
      FROM ztmonster_cl_itm
    INTO TABLE @DATA(monster_tax_no_list).

Filling a Database Table with Summarized Data

通常对于聚合的数据，首先搜索相关数据库表，将其读到application server一系列内表中，通过loop内表建造符合条件的结果表，在7.5中，可以通过查询出数据聚合后直接保存回一张独立的数据库表，不用在application server层面进行交互。

    INSERT ztscared_totals FROM
    ( SELECT
    FROM ztsm_monsters
    FIELDS monster_number,
           SUM( people_scared ) AS people_scared
    GROUP BY mandt, monster_number ).