當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

ABAP-SQL基础知识

發布時間：2025/3/20 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 ABAP-SQL基础知识小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SQL語法

?? 我們在編寫ABAP4程序的時候,經常需要從TABLE中根據某些條件讀取數據,讀取數據最常用的方法就是通過SQL語法實現的。ABAP/4中可以利用SQL語法創建或讀取TABLE，SQL語法分為DDL(DATA DEFINE LANGUAGE)語言和DML(DATA MULTIPULATION LANGUAGE)語言，DDL語言是指數據定義語言，例如CREATE等, DML語言是數據操作語言，例如SELECT, INSERT等語句。SQL語句有OPEN SQL語句和NATIVE SQL語句。 OPEN SQL語句不是標準SQL語句，是ABAP/4語言，利用OPEN SQL語句能在Databases 和 Command 之間產生一個BUFFER，所以它有一個語言轉換的過程。而NATIVE SQL語句則是標準的SQL語句, 它直接針對Databases操作。

OPEN SQL

SELECT語句
語法格式:
SELECT <result> [INTO <target>] [FROM <source>] [WHERE <condition>]
?????????????? [GROUP BY <fields>] [ORDER BY <sort order>]
其中: <result>指定要抓取的欄位
????? <target>將讀取的記錄存放在work area中
????? <source>指定從那個TABLE中讀取資料
????? <condition>抓取資料的條件
????? <fields>指定按那些欄位分組
????? <sort order>排序的欄位及方式
相關的系統變量:
???? SY-SUBRC = 0 表示讀取數據成功
????????????? <> 0 表示未找到符合條件的記錄
???? SY-DBLNT: 被處理過的記錄的筆數.
相關的命令:
???? EXIT. 退出循環.
???? CHECK <logistic statement>.如果邏輯表達式成立，則繼續執行，否則，開始下一次循環。
???
利用循環方式讀取所有記錄
SELECT ….ENDSELECT.是循環方式讀取記錄的。
???? 例如:
TABLES MARD. ''聲明要使用的工作區
SELECT [DISTINCT] * FROM MARD WHERE MATNR = '3520421700'.
??? <Statements>.
ENDSELECT.
(從MARD中抓取所有料號=3520421700的資料)
◆讀取一筆資料
????? Example:
?????????????? TABLES SPFLI.
?????????????? SELECT SINGLE * FROM SPFLI????
???????????????????? WHERE PLANT ='CHUNGLI' AND TEL='4526174'.
?????????????? WRITE: / SPFLI-COMPANY,SPFLI-PLANT,SPFLI-TEL.
◆將讀取的記錄放在work area中,并且加入Internal table 中.
格式有:
?? ... INTO <work area>
?? ... INTO CORRESPONDING FIELDS OF <work area> ''匹配賦值
?? ... INTO (f1, ..., fn) 變量組.
?? ... INTO TABLE <internal table>
?? ... INTO CORRESPONDING FIELDS OF TABLE <internal table>
?? ... APPENDING TABLE <internal table>
?? ... APPENDING CORRESPONDING FIELDS OF TABLE <internal table>
舉例一:
TABLES MARD.?
DATA: BEGIN OF ITAB OCCURS 10,
????????????? MATNR LIKE MARD-MATNR,
????????????? WERKS LIKE MARD-WERKS,
????????????? LGORT LIKE MARD-LGORT,
????????????? LABST LIKE MARD-LABST,
??????? END OF ITAB.
SELECT MATNR WERKS LGORT LABST?
????????????? INTO CORRESPONDING FIELDS OF ITAB?
????????????? FROM MARD
????????????? WHERE MATNR = '3520421700'.
?????? APPEND ITAB.
?????? CLEAR ITAB.
ENDSELECT.
(將讀取的結果放在Internal table ITAB中)

DATA: BEGIN OF WA,?
??????? LINE(240),
????? END OF WA.
DATA NAME(10).
NAME = 'SPFLI'.
SELECT * FROM (NAME) INTO WA.
WRITE: / WA-LINE.?
ENDSELECT.
數據庫表名稱 SPFLI 被賦給字符字段 NAME。SELECT 語句將所有的行從 SPFLI 中讀到目標區 WA 中。在該示例中，WA 與 SPFLI 的結構并不相同，每一行都將自動地轉換成字符字段.

舉例二.
TABLES MARD.
SELECT MATNR MTART MAKTX INTO (t_matnr, t_mtart, maktx)?
?????????????? FROM MARD
?????????????? WHERE MATNR = '3520421700'.
?????? <Statements>.
ENDSELECT.
(從MARD中抓取料號=3520421700的料號、類型和描述,放在變量t_matnr, t_mtart, maktx中)。

??????? Example:
???????????????? TABLES SPFLI.
???????????????? DATA WA LIKE TABLES.
???????????????? SELECT * FROM SPFLI INTO WA.
??????????????????? WRITE: / WA-COMPANY,WA-PLANT.
???????????????? ENDSELECT.
???????????????? 逐筆寫入WA 工作區中
舉例三.
將讀取的資料寫入 Initial Table 中
語法:
??????? SELECT .. INTO TABLE <itab>???
??????? Example:
??????? TABLES SPFLI.
??????? DATA ITAB LIKE SPFLI OCCURS 10 WITH HEADER LINE.
??????? SELECT * FROM SPFLI INTO ITAB.
??????? 一次讀10筆(Initial Table的長度)記錄存入 ITAB 中
????????
??????? SELECT .. INTO TABLE <itab> PACKAGE SIZE <n>
??????? 一次讀取 <n> 筆記錄至 <itab>中
??????? Example:
?????????? TABLES SPFLI.
?????????? DATA ITAB LIKE SPFLI OCCURS 10 WITH HEADER LINE.
?????????? SELECT * FROM SPFLI INTO ITAB PACKAGE SIZE 5.?
?????????? 一次讀取 5 筆記錄

exp :

TABLES SPFLI.
DATA: BEGIN OF WA,
???????? NUMBER TYPE I VALUE 1,
???????? CITYFROM LIKE SPFLI-CITYFROM,
???????? CITYTO?? LIKE SPFLI-CITYTO,
????? END OF WA.
SELECT * FROM SPFLI INTO CORRESPONDING FIELDS OF WA.
?? WRITE: / WA-NUMBER, WA-CITYFROM, WA-CITYTO.
ENDSELECT.
輸出如下所示：

在該示例中，系統只將數據庫表 SPFLI 中選定行的列 CITYFROM 和 CITYTO 傳送到 WA 中。WA 中的組件 NUMBER 保持不變。
*-------------------------------------------------------------------------------------*

按指定的欄位排序
TABLES SBOOK.
SELECT * FROM SBOOK?? WHERE CARRID = 'LH' AND
??????????????????????????? CONNID = '0400' AND
??????????????????????????? FLDATE = '19950228'
??????????????????????????? ORDER BY BOOKID ASCENDING.
?? WRITE: / SBOOK-BOOKID,?? SBOOK-CUSTOMID,
?????????? SBOOK-CUSTTYPE, SBOOK-SMOKER,
?????????? SBOOK-LUGGWEIGHT, SBOOK-WUNIT,
?????????? SBOOK-INVOICE.
ENDSELECT.
(利用參數ORDER BY所指定的欄位排序)
*-------------------------------------------------------------------------------------*

◆ 抓取數據的條件關鍵字
(1) BETWEEN <g1> AND <g2>
例如: WHERE YEAR BETWEEN 1995 AND 2000.
(2) LIKE <g>
例如: WHERE NAME LIKE 'MIKE%'.
('%'是通配符號)
(3) IN (<g1>…<gn>)
是<g1>…<gn>里面的任意一個值即可.
例如: WHERE PLANT IN ('CHUNGLI', 'TAOYUAN','LIUTU').
(表示PLANT 只要是'CHUNGLI'或'TAOYUAN'或'LIUTU'都可以).
(4) ORDER BY 關鍵字
???? 指定排序的欄位或順序
???? (1). ..ORDER BY PRIMARY KEY.
??????? 根據 PRIMARY KEY 遞增排序
???? (2)…ORDER BY <f1> [DESCENDING] <f2> [DESCENDING]
??????? Example:
????????????????? SELECT * FROM IM ORDER BY PART .
*-------------------------------------------------------------------------------------*

INSERT 語句
◆從work area 加入到Internal Table中
格式: INSERT INTO <database> VALUES <work area>
例如:?
DATA: BEGIN OF WA,
????????????? CODE(6) TYPE C,
????????????? NAME(30) TYPE C,
??????? END OF WA.
DATA: VEN LIKE WA OCCURS 10.
…
WA-CODE = '530120'.
WA-NAME = 'XINGDA ELECTRONICS CO.,LTD'.
INSERT INTO VEN VALUES WA .
如果work area的名稱就是internal table的名稱,可以直接寫成:
????? INSERT <internal table>
例如:
DATA: BEGIN OF WA OCCURS 10,
????????????? CODE(6) TYPE C,
????????????? NAME(30) TYPE C,
??????? END OF WA.
…
WA-CODE = '530120'.
WA-NAME = 'XINGDA ELECTRONICS CO., LTD'.
INSERT WA.
◆從另外一個Internal table中INSERT 資料
格式:
INSERT <itab1> FROM TABLE <itab2> [ACCEPTING DUPLICATE KEY]
將<itab2>中非NULL的資料加入<itab1>中,加上[ACCEPTING DUPLICATE KEY]能限制相同PRIMARY KEY不重復加入.

?? 加入一筆記錄至資料庫

?? 1.自 Work Area 工作區
?? 語法:
??????? INSERT INTO <database> VALUES <wa>
??????? Example:
???????????????? TABLES SPFLI.
???????????????? DATA WA?? LIKE SPFLI.
???????????????? WA-NO = '34051920'.
???????????????? WA-COMPANY='DELTA'.
???????????????? INSERT SPFLI VALUES WA.
???????????????? 將 ITAB 資料加入 SPFLI中, 也可寫成 INSERT SPFLI FROM ITAB.
???????????
????????????????? SPFLI-NO='34299876'.
????????????????? SPFLI-COMPANY='HP'.
????????????????? INSERT SPFLI FROM SPFLI.
????????????????? 將Work Area SPFLI中的資料加入資料庫檔案 SPFLI中
????????????????? 因Work Area SPFLI的結構與資料檔 SPFLI一樣, 所以也可
????????????????? 寫成 INSERT SPFLI.

?? 2.自 Internal Table
???? 語法:
?????????? INSERT <database> FROM TABLE <itab> [ACCEPTING DUPLICATE KEY]
?????????? 將 <itab>中非 NULL的資料加入 <database>中, 加上 [ACCEPTING DUPLICATE
?????????? KEY]能檢查不加入有重覆primary key, 若有重覆則 SY-SUBRC 會傳回 4
?????????? Example:
??????????????????? TABLES SPFLI.
??????????????????? DATA ITAB LIKE SPFLI OCCURS 10 WITH HEADER LINE.
??????????????????? ITAB-NO = '34051920'.
??????????????????? ITAB-COMPANY = 'DELTA'.
??????????????????? APPEND ITAB.
??????????????????? ….
??????????????????? INSERT SPFLI FROM TABLE ITAB?
???????????????????????????? ACCEPTING DUPLICATE KEY.
*-------------------------------------------------------------------------------------*

UPDATE 指令
?? 異動已存在的記錄內容

1.使用 Primary Key
語法:?
??????????? UPDATE <database> FROM <wa>
??????????? Example:
???????????????????? TABLES SPFLI.
???????????????????? DATA WA LIKE SPFLI.
???????????????????? WA-NO='34051920'.
???????????????????? WA-COMPANY='DELTA'.
??????????????????? UPDATE SPFLI FROM WA.
??????????????????? 如 SPFLI 的 Primary Key是 NO, 則會找到 NO='34051920'的記錄, 將其 COMPANY欄位異動為 DELTA

2.使用條件式
語法:
??????? UPDATE <database> SET < f1>=<values>… WHERE <condition>
??????? 根據條件式異動符合條件式的記錄
??????? Example:
???????????????? UPDATE SPFLI SET NO ='34051920'
???????????????????????????????????? COMPANY = 'DELTA'
???????????????????????? WHERE TEL = '4526107'.
*-------------------------------------------------------------------------------------*

3.?? MODIFY 語法
???? MODIFY <internal table> [FROM <work area>].
根據 Primary Key 尋找資料檔中符合的記錄, 若找到則更新異動, 若找不到則新增記錄
語法:
?????? MODIFY <database> FROM <wa>
?????? Example:
??????????????? WA-NO='34051920'.
??????????????? WA-COMPANY='DELTA'.
??????????????? MODIFY SPFLI FROM WA.
?????
4.?? DELETE 語法
???? DELETE <internal table> [FROM <work area>].
或: DELETE <internal table> [WHERE <conditions>]
?? 刪除資料檔的記錄

1.使用 Primary Key
語法:?
??????????? DELETE <database> FROM <wa>
??????????? Example:
???????????????????? TABLES SPFLI.
???????????????????? DATA WA LIKE SPFLI.
???????????????????? WA-NO='34051920'.
???????????????????? WA-COMPANY='DELTA'.
???????????????????? DELETE SPFLI FROM WA.
???????????????????? 如 SPFLI 的 Primary Key是 NO, 則會找到 NO='34051920'的記錄, 找到后將此筆刪除

2.使用條件式
語法:
??????? DELETE FROM <database> WHERE <condition>
??????? 根據條件式刪除符合條件式的記錄
??????? Example:
???????????????? DELETE FROM SPFLI WHERE AREA = 'AMERICAN'.
????????????????????????????????????????
*-------------------------------------------------------------------------------------*

5. DATABASE CURSOR
?? Database Cursor是一個資料庫暫存區, 將經SELECT指令讀取的記錄存放至此暫存區, 再由此暫存區放至Work Area中, 可減少資料庫讀取的次數.
1.開啟 Database Cursor
語法:
??????? OPEN CURSOR <c> FOR SELECT … WHERE <condition>
??????? Example:
??????????????? TABLES SPFLI.
??????????????? DATA: WA LIKE SPFLI,
??????????????????????? C1 TYPE CURSOR.
??????????????? OPEN CURSOR C1 FOR SELECT * FROM SPFLI?
?????????????????????? WHERE AREA ='TAIWAN'.
2.讀取 Database Cursor的資料存入 Work Area
語法:
????? FETCH NEXT CURSOR <c> INTO <wa>
????????? Example:
?????????????????? FETCH NEXT CURSOR C1 INTO WA.
讀取下一筆Cursor位置的資料存入WA, 如果已無資料可讀, SY-SUBRC <>0.?
關閉 Database Cursor
語法:
????? CLOSE CURSOR <c>
????? Example:
?????????????? CLOSE CURSOR C1.

*-------------------------------------------------------------------------------------*

COMMIT WORK & ROLLBACK WORK
要確定資料成功寫入資料庫，可使用COMMIT WORK指令，如:?
?? COMMIT WORK.
相反的，如果反悔要復原，可使用 ROLLBACK WORK, 可復原在上個COMMIT WORK指令之后的資料, 如:
?? ROLLBACK WORK.
*-------------------------------------------------------------------------------------*
使用NATIVE SQL指令
語法格式:
EXEC SQL [PERFORMING <form>].
???? <statements>
ENDEXEC.
舉例一.
EXEC SQL.
CREATE TABLE AVERI_CLNT (
???????? CLIENT?? CHAR(3) NOT NULL,
???????? ARG1???? CHAR(3) NOT NULL,
???????? ARG2???? CHAR(3) NOT NULL,
???????? FUNCTION CHAR(10) NOT NULL,
???????? PRIMARY KEY (CLIENT, ARG1, ARG2)
????????????????????????? )
ENDEXEC.
舉例二.
DATA: F1(3), F2(3), F3(3).
F3 = ' 1 '
EXEC SQL.
SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
???????? WHERE ARG2 = :F3
ENDEXEC

PERFORMING <form name>的使用:
如果NATIVE SQL的SELECT命令執行結果是抓到多筆記錄，我們想要逐筆記錄處理時，就用PERFORMING 參數，這個FORM能被逐次調用。如果想中止調用，就用EXIT FORM SQL結束調用。
例如:
DATA: F1(3), F2(3), F3(3).

F3 = '010'
EXEC SQL PERFORMING WRITE_AVERI_CLNT.
SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
???????? WHERE ARG2 = :F3
ENDEXEC.
FORM WRITE_AVERI_CLNT.
WRITE: / F1, F2.
ENDFORM.

???????????????? DATA: BEGIN OF WA,
??????????????????????? NAME(8),
??????????????????????? AGE TYPE I,
??????????????????????? END?? OF WA.
???????????????? DATA F1 TYPE I.
???????????????? FI = 20.
???????????????? EXEC SQL PERFORMING OUTPUT.
??????????????????? SELECT NAME,AGE INTO :WA FROM NAME_TABLE?
???????????????????????????? WHERE AGE >= :F1.
???????????????? ENDEXEC..
???????????????? FORM OUTPUT.
?????????????????? WRITE: / WA-NAME,WA-AGE.
???????????????? ENDFORM.

注意:
a. NATIVE SQL把TABLE中的MANDT(client)欄位當作一般欄位使用,所以在抓取資料時必須指定特定的Client;
b. NATIVE SQL中的SELECT語句沒有CHECK權限的功能;
c. 在登入SAP R/3系統時,我們已經自動與Database連接,所以在執行NATIVE SQL時并不需要CONNECT語句;
d. 一條NATIVE SQL語句可以以分號;結束,一般情況下是以句號.結束.
e. 某些數據庫系統對TABLE名字和FIELD名字有大小寫區別,要正確書寫.
f. 在NATIVE SQL中,雙引號"不表示注釋.

總結

以上是生活随笔為你收集整理的ABAP-SQL基础知识的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。