OPENSQL是ABAP中自带的一种SQL,它经过SAP解析器将OPEN SQL 语句转换成后台数据库能够识别的SQL语句。
常用语句
数据库选取 ----SELECT
数据库表插入 ---- INSERT
数据库表修改 ---- UPDATE/MODIFY
数据库表删除 ---- DELETE
提交至数据库 ---- COMMIT WORK/ROLLBACK WORK
数据库表选取
SELECT…INTO… 覆盖数据
数据库表循环语句
SELECT field1 field2 field3
FROM field1 field2 field3
INTO [CORRESPONDING FIELDS OF ]wa/
APPEND wa TO itab.
ENDSELECT.
*选取多条数据
SELECT field1 field2 field3
FROM database table
INTO [CORRESPONDING FIELDS OF] TABLE itab.
* 选取单条
SELECT SINGLE field1 field2 field3
FROM database table
INTO [CORRESPONDING FIELDS OF] TABLE itab.
SELECT … APPENDING … 追加数据
语法规则类似,不会覆盖内表中的数据,而是在原本的内表中追加数据
INSERT
-- 在数据库表里插入一条记录的语句用 INSERT 语句,如果数据表里已经存在了一条相同的记录,会出现两种情况,如果从结构插入,返回值为4,如果从内表插入,便会出现系统错误。
从结构中插入
GS_SCORE-ZINDEX = '20000'.
GS_SCORE-NAME = 'ABC'.
INSERT ZHQ_SCORE_02 FROM GS_SCORE.
GS_SCORE-ZINDEX = '20002'.
GS_SCORE-NAME = 'ABC'.
APPEND GS_SCORE TO GT_SCORE.
INSERT ZHQ_SCORE_02 FROM TABLE GT_SCORE.
数据库表修改
对于数据库表的变更一般只针对用户自定义的表(CBO TABLE),对于系统标准表不推荐编写ABAP程序更改,
而是使用SAP标准程序更改。
UPDATE
Update 语句只会更改数据库表里相关记录已经存在的数据,如果记录不存在,则显示系统错误
*从结构更新
GS_SCORE-NAME = 'TEST'.
UPDATE ZHQ_SCORE_02 FROM GS_SCORE.
* 从内表更新
GS_SCORE-ZINDEX = '30000'.
GS_SCORE-NAME = ''ABC'.
APPEND GS_SCORE TO GT_SCORE.
UPDATE ZHQ_SCORE_02 FROM TABLE GT_SCORE.
* 使用SET 更新
UPDATE ZHQ_SCORE_02 SET SEX = 'F' WHERE NAME = 'AAA'.
MODIFY
Modify语句会检查数据库表里相关的记录是否存在,如果存在就会更改记录,如果不存在则会在数据库里追加一条新的记录。
*从结构修改
GS_SCORE-ZINDEX = '20003'.
GS_SCORE-NAME = 'ABC'.
MODIFY ZHQ_SCORE_02 FROM GS_SCORE.
*从内表修改
GS_SCORE-ZINDEX = '20003'.
GS_SCORE-NAME = 'BBB'.
APPEND GS_SCORE TO GT_SCORE.
MODIFY ZHQ-SCORE_02 FROM TABLE GT_SCORE.
数据库表删除
DELETE
----删除主键相同的数据,如果数据库表中不存在,则删除失败
DELETE db FROM TABLE itab.
DELETE db FROM wa.
DELETE FROM db WHERE field = val.
* 从结构删除
gs_score-zindex = '20003'.
gs_SCORE-NAME = 'AAA'.
DELETE ZHQ_SCORE_02 FROM GS_SCORE.
* 从内表删除
gs_score-zindex = '20004'.
gs_SCORE-NAME = 'BBB'.
DELETE ZHQ_SCORE_02 FROM TABLE GS_SCORE.
* 删除
DELETE FROM ZHQ_SCORE_02
WHERE NAME = 'ABC'.