【IT168 技术文章】
一、SQL Interface
1.Select ... Where vs. Select + Check
用Select … Where语句效率比Select + Check语句要高,例如:
SELECT * FROM SBOOK INTO SBOOK_WA
WHERE CARRID = 'LH' AND
CONNID = '0400'.
ENDSELECT.
SELECT * FROM SBOOK INTO SBOOK_WA.
CHECK: SBOOK_WA-CARRID = 'LH' AND
SBOOK_WA-CONNID = '0400'.
ENDSELECT.
2.Test Existence
检查满足某个(些)条件的记录是否存在时,使用Select Single效率最高;例如:
SELECT SINGLE * FROM SBOOK
WHERE CARRID = 'LH'.
SELECT * FROM SBOOK INTO SBOOK_WA
WHERE CARRID = 'LH'.
EXIT.
ENDSELECT.
3.Select aggregates
需要对数据库某列求最大,最小,和,平均值或者记录数量时,请使用聚集函数来代替Select … Where + Check的方法,这样效率高而且网络流量小。
DATA: MAX_MSGNR type t100-msgnr.
SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr
WHERE SPRSL = 'D' AND
ARBGB = '00'.
4.Select with select list
取数时请使用Select + 指定的列名称 into,而不要使用Select * into。显示的指定列名称只取出需要的列,不会像Select *会取出相应表的所有的列。
SELECT DOMNAME FROM DD01L
INTO DD01L_WA-DOMNAME
WHERE DOMNAME LIKE 'CHAR%'
AND AS4LOCAL = 'A'.
ENDSELECT.
SELECT * FROM DD01L INTO DD01L_WA
WHERE DOMNAME LIKE 'CHAR%'
AND AS4LOCAL = 'A'.
ENDSELECT.
5.Column Update
尽可能的使用字段(column updates)更新来代替行记录(single-row updates)更新数据库表,这样可以减少网络负载。
UPDATE SFLIGHT
SET SEATSOCC = SEATSOCC - 1.