ABAP数据库操作
数据库操作,ABAP有自己封装的SQL语句操作数据库表。SQL语句分为DDL数据库定义语句,DML数据库操作语句。
程序宣告使用数据库表
语法:tables <数据库表名>
示例:
"宣告程序使用table
TABLES spfli.
1插入数据库表记录
单行数据插入:
语法:insert into <数据库表> values <工作区>.
语法:insert <数据库表> from <工作区>.
示例:
*"数据库插入语句insert
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'AB'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'cq'.
"单行数据
"插入数据库表,
INSERT INTO spfli VALUES t_spfli.
"插入数据库表
INSERT spfli FROM t_spfli.
多行插入:
语法:insert <数据库表> from table <内表>.
示例:
"多行数据
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'ad'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'cq'.
append t_spfli.
t_spfli-carrid = 'bd'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'cq'.
append t_spfli.
"将内部数据插入数据库表
INSERT spfli FROM TABLE t_spfli.
插入时禁止插入关键字重复记录,如果不禁止插入关键字重复记录程式会崩溃!
示例:
"插入数据,禁止插入关键字重复记录
INSERT spfli FROM TABLE t_spfli ACCEPTING DUPLICATE KEYS.
2更新数据库表记录
更新单行数据:
语法:update <数据库表> from <工作区>
示例:
"***************************************更新数据库表
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
"carrid,connid是数据库表键值,必须有值才能查找更新
t_spfli-carrid = 'ad'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'dps'.
"更新数据库表数据,表的键值必须都赋值
UPDATE spfli FROM t_spfli.
更新多行数据:
语法:update <数据库表> from table <内表>
语法:update <数据库表> set <字段> = <值> [<字段> = <值> ] [where 子句]
更新多条满足条件记录。
示例:
"更新多条数据
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'ad'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'dps'.
APPEND t_spfli.
t_spfli-carrid = 'bd'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'dps'.
APPEND t_spfli.
"更新内表数据到数据库表
UPDATE spfli FROM TABLE t_spfli.
"更新多条满足条件记录
UPDATE spfli SET carrid = 'bd' connid = '113' WHERE carrid = 'bd'.
Modify关键词,更新或者添加记录。当记录存在则更新,不存在则插入。
语法:modify <数据库表> from <工作区>.
语法:modify <数据库表> from table <内表>.
示例:
"modify更新或插入
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'cc'.
t_spfli-connid = '113'.
t_spfli-cityfrom = 'dg'.
"更新或插入单笔
MODIFY spfli FROM t_spfli.
t_spfli-carrid = 'cc'.
t_spfli-connid = '113'.
t_spfli-cityfrom = 'dgs'.
APPEND t_spfli.
t_spfli-carrid = 'cc'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'dgs'.
APPEND t_spfli.
"更新插入多笔
MODIFY spfli FROM TABLE t_spfli.
3删除数据库表记录
删除数据库表记录,单行删除:
语法:delete from <数据库表> [where 条件].
可以删除一条或多条。删除符合条件记录。
语法:delete from <数据库表> from wa.
根据工作区删除记录,必须指定所有关键词域值,其他域值忽略,即使不匹配,只要所有关键词字段匹配,就会删除。
示例:
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'cc'.
t_spfli-connid = '113'.
t_spfli-cityfrom = 'dgs'.
"删除符合条件记录,删除一条或多条
"可以指定key字段,删除一条
DELETE FROM spfli WHERE carrid = 'cc'.
"根据工作区,必须指定关键词字段,其他字段可以忽略
DELETE spfli FROM t_spfli.
删除多笔记录,可以使用where条件,删除多笔符合条件记录也可使用内表。
语法:delete <数据库表> from table <内表>.
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
t_spfli-carrid = 'cc'.
t_spfli-connid = '112'.
t_spfli-cityfrom = 'cqs'.
APPEND t_spfli.
t_spfli-carrid = 'cc'.
t_spfli-connid = '113'.
t_spfli-cityfrom = 'cqs'.
APPEND t_spfli.
"删除多笔记录,同样只比较关键词字段
DELETE spfli FROM TABLE t_spfli.
4查询数据库表记录
查询数据库表到内表,内表结构必须和查询的数据库表相匹配,查询数据库表部分字段元出来,如果定义内表字段多余查询数据库表字段,值是依次赋给内表字段,可能会错位。
循环查询语句:select …… endselect,循环查询并处理数据库每一笔符合条件记录。
语法:select * into <内表> from <数据库表>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
“将工作区内容加入内表
“其他逻辑
endselect.
查询所有字段字段到内表工作区。
语法:select <字段名> [<字段名>] into <内表> from <数据库表>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
”将工作区内容加入内表
”其他逻辑
endselect.
查询指定字段到内表工作区,如果定义内表字段名不等于数据库表字段名,那么查询出字段顺序应该和内表定义字段顺序保持一致,以防错位。
示例:
"宣告程序使用table
TABLES spfli.
"select * 查询所有字段
DATA:t_testCol LIKE TABLE OF spfli WITH HEADER LINE.
"查询所有字段
SELECT * INTO t_testCol FROM spfli .
endselect.
"数据库查询语句select
data: BEGIN OF testCol OCCURS 0,
col1 like spfli-carrid,
col2 like spfli-connid,
END OF testCol.
"select ………… endselect 相当于循环数据库表数据,一次一条
"注:这种方式不推荐使用,考虑到系统效能
"查询carrid, connid,必须要有into
SELECT CARRID CONNID INTO testCol FROM SPFLI
WHERE CARRID = 'AA'.
"将查询到记录添加到内表
APPEND testCol.
"其他逻辑
WRITE: / SPFLI-CARRID, SPFLI-CONNID.
ENDSELECT.
查询where条件语句
1、字段 和 值 比较,比较运算符:=,<,>,<=,>=;
2、字段 between 值 and 值;
3、字段元 in ( 字符串,数字,select-option内表);
4、字段元 like “匹配字符串” ,‘_’代表一个字符,‘%’代表一个字符串;
5、not关键词,将条件取反;
所有比较的where条件都可使用( 条件 )使用 and 或者 or 连接,构成多个筛选条件查询。有的条件必须使用括号分隔才正确。
"查询条件
DATA:testTable LIKE TABLE OF spfli WITH HEADER LINE.
"直接where条件比较
select * FROM spfli INTO testTable WHERE carrid = 'AA' AND connid = '0001'.
WRITE:/ 'where条件:',testTable-carrid,testTable-connid.
ENDSELECT.
"between and
select * FROM SPFLI INTO testTable where period BETWEEN 1 and 2.
WRITE:/ 'between:',testTable-carrid,testTable-connid.
ENDSELECT.
"like _一个字符,%一个字符串
select * from spfli INTO testTable where cityfrom like '%new%'.
WRITE:/ 'like:',testTable-carrid,testTable-connid.
ENDSELECT.
*
*"in 在一个范围
select * from spfli INTO testTable where carrid in ('AA','LH').
WRITE:/ 'in字符串:',testTable-carrid,testTable-connid.
ENDSELECT.
select * from spfli INTO testTable where period in (1,2).
WRITE:/ 'in数字:',testTable-carrid,testTable-connid.
ENDSELECT.
*”使用range内表作为条件
DATA:testTable LIKE TABLE OF spfli WITH HEADER LINE.
"定义一个range类型
DATA:t_range LIKE RANGE OF spfli-carrid WITH HEADER LINE.
DATA:wa_range LIKE LINE OF t_range.
* 宏定义
DEFINE set_range.
wa_range-sign = 'I'.
wa_range-option = 'EQ'.
wa_range-low = &1.
append wa_range to t_range.
END-OF-DEFINITION.
set_range 'DL'.
SELECT * FROM spfli INTO table testTable WHERE carrid in t_range.
"混合条件,有些条件可以不使用括号括起来,有些必须括号分割
SELECT * FROM spfli INTO testTable WHERE ( period in (1,2) ) AND ( carrid = 'AA' ).
WRITE:/ '多条件:',testTable-carrid,testTable-connid.
ENDSELECT.
"not关键词,可以和比较条件,like,in,between and使用,将条件取反
select * from spfli INTO testTable where carrid not in ('AA','LH').
WRITE:/ 'in字符串:',testTable-carrid,testTable-connid.
ENDSELECT.
查询单行数据到工作区
语法:select single <字段>[<字段>……] from <数据库表> into <wa>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
查询符合条件的一条记录到工作区中。
示例:
"*****************查询单行数据
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
"查询单行数据到工作区
SELECT SINGLE * from spfli INTO t_spfli WHERE carrid = 'QF'.
WRITE:/ t_spfli-carrid.
这里t_spfli是带表头行的内表,查询语句select single 是查询出一条记录到t_spfli的表头行(工作区)。
DATA:BEGIN OF wa_spfli,
carrid LIKE spfli-carrid,
connid LIKE spfli-connid,
END OF wa_spfli.
"查询指定字段,内表结构需要对应查询的字段顺序,不然错位
SELECT SINGLE carrid connid FROM spfli INTO wa_spfli WHERE carrid = 'QF'.
WRITE:/ wa_spfli-carrid , wa_spfli-connid.
这里查询部分字段的一条记录到指定内表。
"使用内表工作区挨个赋值对应
SELECT SINGLE carrid connid FROM spfli INTO (t_spfli-carrid,t_spfli-connid) where carrid = 'QF'.
WRITE:/ t_spfli-carrid,t_spfli-connid.
使用内表工作区,挨个赋值给工作区字段
查询数据库表多条记录到内表,一次性查询到内存中。
语法:select * from <数据库表> into corresponding fields of table <内表>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
示例:
"*******************一次性查询数据库记录到内表中
"查询多条记录到内表
DATA:t_spfli LIKE TABLE OF spfli WITH HEADER LINE.
SELECT * FROM spfli INTO CORRESPONDING FIELDS OF TABLE t_spfli WHERE carrid = 'DL'.
语法:select * from <数据库表> into table <内表>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
示例:
"查询所有符合条件记录到内表
SELECT * FROM spfli INTO TABLE t_spfli WHERE carrid = 'DL'.
语法:select <字段> [<字段>……] from <数据库表> into ( table | corresponding fields of table ) <内表>
[where条件]
[group by <字段>]
[having <条件>]
[order by <字段>].
示例:
DATA:BEGIN OF wa_spfli OCCURS 0,
carrid LIKE spfli-carrid,
connid LIKE spfli-connid,
END OF wa_spfli.
"查询部分字段元,也要对应顺序,不然错位
SELECT carrid connid FROM spfli INTO TABLE wa_spfli WHERE carrid = 'DL'.
LOOP AT wa_spfli.
WRITE:/ wa_spfli-carrid,wa_spfli-connid.
ENDLOOP.
"这种方式查询部分字段元,可以不需要对应顺序,查询结果直接对应到对应字段
SELECT carrid connid FROM spfli INTO CORRESPONDING FIELDS OF TABLE t_spfli WHERE carrid = 'DL'.
LOOP AT t_spfli.
WRITE:/ t_spfli-carrid,t_spfli-connid,t_spfli-cityfrom.
ENDLOOP.
Order by子句排序条件
使用示例:
SELECT * INTO TABLE test_spfli1 FROM spfli
UP TO 1 ROWS
WHERE COUNTRYFR = 'CN'
ORDER BY DATE_CR ASCENDING TIME_CR ASCENDING.
多表连接查询
语法:select <字段> [<字段>……] into ( table | corresponding fields of table ) <内表>
from <数据库表> AS A INNER JOIN <数据库表> AS B
ON A~<相同栏位> = B~<相同栏位>
[where子句]
[其他子句].
三张表链接查询
语法:select <字段> [<字段>……] into ( table | corresponding fields of table ) <内表>
from (<数据库表> AS A INNER JOIN <数据库表> AS B ON A~<相同栏位> = B~<相同栏位>)
[INNER JOIN <数据库表> AS C ON A or B~<相同栏位> = C~<相同栏位>]
[where子句]
[其他子句].
必须使用括号将前两个表的inner join括起来再inner join 新表。
示例:
"ABAP测试表
TABLES:spfli,sflight,scarr.
DATA:BEGIN OF test_info OCCURS 0,
carrid LIKE spfli-carrid,
carrname like scarr-carrname,
END OF test_info.
"内连接查询
"逐条遍历
*SELECT A~carrid B~carrname INTO test_info
* FROM spfli AS A INNER JOIN scarr AS B ON A~carrid = B~carrid
* WHERE connid = 1.
* "将查询记录加入内表
* APPEND test_info.
*ENDSELECT.
"逐条遍历
*SELECT A~carrid B~carrname INTO CORRESPONDING FIELDS OF test_info
* FROM spfli AS A INNER JOIN scarr AS B ON A~carrid = B~carrid
* WHERE connid = 1.
* "将查询记录加入内表
* APPEND test_info.
*ENDSELECT.
"一次性查询到内表
SELECT A~carrid B~carrname INTO CORRESPONDING FIELDS OF TABLE test_info
FROM spfli AS A INNER JOIN scarr AS B ON A~carrid = B~carrid
WHERE connid = 1.
左连接:
"左连接
"A表记录全部显示,即使B表没有对应记录
SELECT A~carrid B~carrname INTO CORRESPONDING FIELDS OF TABLE test_info
FROM spfli AS A LEFT JOIN scarr AS B ON A~carrid = B~carrid.
"左外连接
SELECT A~carrid B~carrname INTO CORRESPONDING FIELDS OF TABLE test_info
FROM spfli AS A LEFT OUTER JOIN scarr AS B ON A~carrid = B~carrid.
右连接:
好像不支持这个语法了。
"右连接,RIGHT OUTER JOIN 报错??
*SELECT A~carrid B~carrname INTO CORRESPONDING FIELDS OF TABLE test_info
* FROM spfli AS A RIGHT JOIN scarr AS B ON A~carrid = B~carrid.