自数据库读取记录
语法:
SELECT <result> FROM <source> [INTO <target>] [WHERE <condition>]
[ GROUP BY <fields>] [ORDER BY <sort order>]
1.以迥圈方式读取所有记录
语法:
SELECT [DISTINCT] * …
…..
ENDSELECT.
加上[DISTINCT]会自动去除重复的记录
Example:
TABLES SPFLI.
SELECT * FROM SPFLI WHERE COMPANY=’DELTA’.
WRITE: / PLANT,TEL.
ENDSELECT.
会以迥圈的方式逐笔印出符合条件的记录
2.读取单笔记录
语法:
SELECT SINGLE * FROM …. WHERE….
Example:
TABLES SPFLI.
SELECT SINGLE * FROM SPFLI
WHERE PLANT =’CHUNGLI’ AND TEL=’4526174’.
WRITE: / SPFLI-COMPANY,SPFLI-PLANT,SPFLI-TEL.
3.将读取的记录存放至 Work Area
语法:
SELECT ….. INTO <wa>
Example:
TABLES SPFLI.
DATA WA LIKE TABLES.
SELECT * FROM SPFLI INTO WA.
WRITE: / WA-COMPANY,WA-PLANT.
ENDSELECT.
逐笔写入WA 工作区中
4.将读取的资料写入 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 笔记录
5.条件叙述
语法:
WHERE <condition>
(1).BETWEEN <g1> AND <g2>
在 <g1> 至 <g2> 之间的条件范围
Example:
WHERE YEAR BETWEEN 1995 AND 2000.
(2).LIKE <g>
表示条件包含的字符串
<1>._:表示一个字符
<2> % : 表示一个字符串
Example:
..WHERE NAME LIKE ‘LEE%’.
条件为NAME字段前3个字符为 LEE
..WHERE MODEL LIKE ‘‘%SPS%’.
条件为MODEL字段包含 SPS的记录
(3).IN (<g1>….<g2>)
包含在 <g1>…<g2>的条件
Example:
..WHERE PLANT IN (‘TAOYUAN’,’CHUNGLI’,’LIUTU’).
条件为 PLANT 是 TAOYUAN,CHUNGLI或LIUTU的记录
6.ORDER BY 叙述
指定排序的字段或顺序
(1). ..ORDER BY PRIMARY KEY.
根据 PRIMARY KEY 递增排序
(2)…ORDER BY <f1> [DESCENDING] <f2> [DESCENDING]
Example:
SELECT * FROM IM ORDER BY PART .