11.Processing External Data
11.1DataBase Access
SQL Trace Tool(ST05),SQL跟踪分析工具。Index可以有效提高效率,但是仅正向查询where条件,例如:=,like;如果是<>,index没有优化效果。index包含字段1~4个关键字段。
示例:OR没有优化
SELECT * FROM spfli WHERE carrid = 'LH' AND
( CITYFROM = 'FRANKFURT' OR cityfrom = 'NEW YORK' ).
改善:
SELECT * FROM spfli
WHERE ( carrid = 'LH' AND cityfrom = 'FRANKFURT' ) OR
( carrid = 'LH' AND cityfrom = 'NEW YORK' ).
11.2Open SQL
1.SELECT查询语句
语法:
SELECT result
FROM source
INTO|APPENDING target
[[FOR ALL ENTRIES IN itab] WHERE sql_cond]
[GROUP BY group] [HAVING group_cond]
[ORDER BY sort_key].
[ENDSELECT].
result
单笔,去重字段
{ SINGLE [FOR UPDATE] } | { [DISTINCT] { } }
查询列,*所有列,column_syntax动态列
* | { {col1|aggregate( [DISTINCT] col1 )} [AS a1]
{col2|aggregate( [DISTINCT] col2 )} [AS a2] ... }
| (column_syntax) ... .
聚合函数
{ MAX( [DISTINCT] col )
| MIN( [DISTINCT] col )
| AVG( [DISTINCT] col )
| SUM( [DISTINCT] col )
| COUNT( DISTINCT col )
| COUNT( * )
| COUNT(*) } ... .
示例:
FORM f_open_sql_sel.
DATA:lt_sflight TYPE TABLE OF sflight.
DATA:ls_sflight LIKE LINE OF lt_sflight.
"查询语句
"single
SELECT SINGLE * FROM sflight
INTO ls_sflight
WHERE carrid = 'AA'.
"distinct,去重
SELECT DISTINCT carrid connid planetype FROM sflight
INTO CORRESPONDING FIELDS OF TABLE lt_sflight
WHERE carrid = 'LH'.
"查询所有列
SELECT * FROM sflight INTO TABLE lt_sflight.
"查询指定列,
"当列和结构,内表不匹配,需要INTO CORRESPONDING FIELDS OF TABLE
"当查询列匹配时,直接INTO,INTO TABLE
SELECT carrid connid planetype FROM sflight INTO CORRESPONDING FIELDS OF TABLE lt_sflight.
"聚合函数
TYPES:BEGIN OF s_flight,
carrid TYPE sflight-carrid,
connid TYPE sflight-connid,
seats_max TYPE I,
seats_min TYPE I,
seats_avg TYPE I,
seats_sum TYPE I,
seats_count TYPE I,
counts TYPE I,
counts1 TYPE I,
END OF s_flight.
DATA:ls_flight TYPE s_flight.
DATA:lv_count TYPE I.
SELECT SINGLE
carrid
connid
max( seatsmax ) as seats_max
min( seatsmax ) as seats_min
avg( seatsmax ) as seats_avg
sum( seatsmax ) as seats_sum
"distinct必须
count( DISTINCT seatsmax ) as seats_count
"两个count相同
count( * ) as counts
count(*) as counts1
FROM sflight
INTO ls_flight
GROUP BY carrid connid.
"动态查询字段
DATA:t_comp TYPE TABLE OF string.
APPEND 'carrid' TO t_comp.
APPEND 'connid' TO t_comp.
SELECT (t_comp) FROM sflight
INTO CORRESPONDING FIELDS OF TABLE lt_sflight.
ENDFORM.
from
FROM { {dbtab [AS tabalias]}
| join
| {(dbtab_syntax) [AS tabalias]} }
[CLIENT SPECIFIED]
[UP TO n ROWS]
[BYPASSING BUFFER]
[CONNECTION {con|(con_syntax)}] ... .
[CLIENT SPECIFIED]:
关闭自动client handle,client字段可以用于where,order by条件;
[UP TO n ROWS]:
限制返回结果集条数;
[BYPASSING BUFFER] :
直接从数据库读取数据,不查询SAP buffering;
join语法:
[(] {dbtab_left [AS tabalias_left]} | join
{[INNER] JOIN}|{LEFT [OUTER] JOIN}
{dbtab_right [AS tabalias_right] ON join_cond} [)]
CONNECTION {con|(con_syntax)}
连接第二数据库,Con链接配置表:DBCAN;
示例:
"from
FORM f_open_sql_from.
DATA:lt_sflight TYPE TABLE OF sflight.
DATA:ls_sflight LIKE LINE OF lt_sflight.
"取消自动client
SELECT * FROM sflight
CLIENT SPECIFIED
INTO TABLE lt_sflight
WHERE mandt = '000'.
"取消使用sap buffer
SELECT * FROM sflight
BYPASSING BUFFER
INTO TABLE lt_sflight.
"查询限定条数结果
SELECT * FROM sflight
UP TO 2 ROWS
INTO TABLE lt_sflight.
"join
TYPES:BEGIN OF s_flight,
carrid TYPE sflight-carrid,
connid TYPE sflight-connid,
carrname TYPE scarr-carrname,
url TYPE scarr-url,
fldate TYPE sflight-fldate,
END OF s_flight.
DATA:lt_flight TYPE TABLE OF s_flight.
DATA:ls_flight LIKE LINE OF lt_flight.
"默认内连接JOIN = INNER JOIN
SELECT a~carrid a~carrname a~url b~connid b~fldate
FROM scarr AS a JOIN sflight AS b
ON a~carrid = b~carrid
INTO CORRESPONDING FIELDS OF TABLE lt_flight
WHERE a~carrid = 'AA'
OR a~carrid = 'AZ'.
"左外连接LEFT JOIN = LEFT OUTER JOIN
SELECT a~carrid a~carrname a~url b~connid b~fldate
FROM scarr AS a LEFT JOIN sflight AS b
ON a~carrid = b~carrid
INTO CORRESPONDING FIELDS OF TABLE lt_flight
WHERE a~carrid = 'AA'
OR a~carrid = 'AZ'.
ENDFORM.
into
语法:
{ INTO { {[CORRESPONDING FIELDS OF] wa}|(dobj1, dobj2, ...)} }
| { INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n] }
[ creating ] ... .
数据库类型到SAP内部类型转换:
Data Type of Column in Result Set | ABAP Data Type |
CHAR, CLNT, CUKY, LANG, SSTRING, STRING, UNIT, VARC | c, string |
ACCP, NUMC | c, n |
LCHR | c |
RAW, RAWSTRING | x, xstring |
LRAW | x |
DF16_DEC | decfloat16, decfloat34 |
DF16_RAW, DF16_SCL | decfloat16 |
DF34_DEC, DF34_RAW, DF34_SCL | decfloat34 |
CURR, DEC, INT1, INT2, INT4, PREC, QUAN | (b, s),i, p, f |
FLTP | f |
DATS | d |
TIMS | t |
示例:
"into
FORM f_open_sql_into.
DATA:lt_spfli TYPE TABLE OF spfli.
DATA:ls_spfli LIKE LINE OF lt_spfli.
"INTO查询插入内表,删除之前内表记录
"APPENDING查询插入内表,不删除,追加记录
SELECT * FROM spfli INTO CORRESPONDING FIELDS OF TABLE lt_spfli.
SELECT * FROM spfli APPENDING CORRESPONDING FIELDS OF TABLE lt_spfli.
SELECT * FROM spfli INTO TABLE lt_spfli.
SELECT * FROM spfli APPENDING TABLE lt_spfli.
"creating
"CREATING {READER|LOCATOR FOR { COLUMNS blob1 blob2 ... clob1 clob2 ... }
"| { ALL [OTHER] [BLOB|CLOB] COLUMNS } [READER|LOCATOR FOR ...] }
"| {(crea_syntax)} ...
"查询blob,clob字段
"对应类:
"CL_ABAP_DB_C_READER or CL_ABAP_DB_C_LOCATOR in the case of CLOBs.
"CL_ABAP_DB_X_READER or CL_ABAP_DB_X_LOCATOR in the case of BLOBs.
ENDFORM.
where
语法:
[FOR ALL ENTRIES IN itab] WHERE sql_cond ... .
sql_cond:
{ {col1 {=|EQ|<>|NE|>|GT|<|LT|>=|GE|<=|LE} { {dobj} | {col2} | {[ALL|ANY|SOME] subquery} }}
| {col [NOT] BETWEEN dobj1 AND dobj2}
| {col [NOT] LIKE dobj [ESCAPE esc]}
| {col [NOT] IN (dobj1, dobj2 ...)}
| {col [NOT] IN seltab}
| {col IS [NOT] NULL}
| {(cond_syntax)}
| {[NOT] EXISTS subquery}
| {col [NOT] IN subquery} } ... .
示例:
"where
FORM f_open_sql_where.
DATA:lt_sflight TYPE TABLE OF sflight.
DATA:ls_sflight LIKE LINE OF lt_sflight.
DATA:lt_carr TYPE TABLE OF scarr.
DATA:ls_carr LIKE LINE OF lt_carr.
"比较条件
"=EQ,<>NE,>GT,<LT,>=GE,<=LE
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE carrid EQ 'AA'
AND connid NE '0120'.
"between,not between a and b
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE seatsmax BETWEEN 500 AND 1000.
"like,not like,_代表一个字符,%代表任意字符
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE carrid LIKE '%Z'.
"in,not in,
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE carrid NOT IN ('AA','AZ').
"in seltab,是否在range table
DATA:lr_car TYPE RANGE OF sflight-carrid.
DATA:lrs_car LIKE LINE OF lr_car.
lrs_car-low = 'AA'.
lrs_car-high = 'AZ'.
lrs_car-option = 'BT'.
lrs_car-sign = 'I'.
APPEND lrs_car TO lr_car.
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE carrid IN lr_car.
"is null,is not null是否为空
"动态条件
DATA:lv_con_syntax TYPE string.
DATA:lt_con_syntax TYPE TABLE OF string.
lv_con_syntax = 'carrid = ''AA'' AND connid = ''0017'''.
APPEND lv_con_syntax TO lt_con_syntax.
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE (lv_con_syntax).
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE (lt_con_syntax).
"子查询
"col operator [ALL|ANY|SOME] subquery
"[NOT] EXISTS subquery
"[NOT] IN subquery
"= SOME/ANY只要只查询中一笔记录匹配即可
"相等于IN subquery
"= ALL需要所有结果匹配
"子查询只能返回一条记录,ALL,ANY,SOME可省略
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE carrid = SOME
( SELECT carrid FROM scarr
WHERE carrid = 'AA').
"exists subquery,子查询至少返回一条记录为真
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE EXISTS
( SELECT * FROM sflight
WHERE carrid = 'SQ').
"AND,OR,NOT连接
SELECT * FROM sflight INTO TABLE lt_sflight
WHERE NOT carrid = 'AA'
AND seatsmax > 100
OR seatsocc < 100.
"for all entry
"会自动去除重复记录
SELECT * FROM scarr INTO TABLE lt_carr.
SELECT * FROM sflight INTO TABLE lt_sflight
FOR ALL ENTRIES IN lt_carr
WHERE carrid = lt_carr-carrid.
ENDFORM.
group by
语法:
GROUP BY { {col1 col2 ...} | (column_syntax) } ... .
分组。
语法:
HAVING sql_cond ... .
筛选条件,和分组一起使用。
示例:
"group
FORM f_open_sql_group.
TYPES:BEGIN OF s_flight,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
count1 TYPE I,
END OF s_flight.
DATA:lt_flight TYPE TABLE OF s_flight.
DATA:ls_flight LIKE LINE OF lt_flight.
"group by
SELECT carrid count(*) as count1
FROM spfli
INTO (ls_flight-carrid,ls_flight-count1)
GROUP BY carrid.
WRITE:/ ls_flight-carrid,ls_flight-count1.
ENDSELECT.
"having筛选结果
SELECT SINGLE carrid count(*) as count1
FROM spfli
INTO (ls_flight-carrid,ls_flight-count1)
GROUP BY carrid
HAVING carrid = 'AA'.
WRITE:/ ls_flight-carrid,ls_flight-count1.
ENDFORM.
order by
语法:
ORDER BY { {PRIMARY KEY}
| { {col1|a1} [ASCENDING|DESCENDING] {col2|a2} [ASCENDING|DESCENDING] ...}
| (column_syntax) } ... .
排序。
示例:
FORM f_open_sql_order.
DATA:lt_spfli TYPE TABLE OF spfli.
DATA:ls_spfli LIKE LINE OF lt_spfli.
"指定排序字段
SELECT * FROM spfli INTO TABLE lt_spfli
ORDER BY carrid ASCENDING connid DESCENDING.
"primary key排序,默认升序
SELECT * FROM spfli INTO TABLE lt_spfli
ORDER BY PRIMARY KEY.
"动态条件
DATA:lv_dyn_order TYPE string.
lv_dyn_order = 'carrid ascending connid descending'.
SELECT * FROM spfli INTO TABLE lt_spfli
ORDER BY (lv_dyn_order).
ENDFORM.
2.OPEN CURSOR查询语句
语法:
OPEN CURSOR [WITH HOLD] dbcur FOR
SELECT result FROM source
[[FOR ALL ENTRIES IN itab] WHERE sql_cond]
[GROUP BY group] [HAVING group_cond]
[ORDER BY sort_key].
[WITH HOLD]:当使用native sql commit work时,不关闭CURSOR。
语法:
FETCH NEXT CURSOR dbcur {INTO|APPENDING} target.
语法:
CLOSE CURSOR dbcur.
示例:
"cursor
FORM f_open_sql_cursor.
DATA:c1 TYPE cursor.
DATA:c2 TYPE cursor.
DATA:lt_spfli TYPE TABLE OF spfli.
DATA:ls_spfli LIKE LINE OF lt_spfli.
DATA:lt_scarr TYPE TABLE OF scarr.
DATA:ls_scarr LIKE LINE OF lt_scarr.
"开启cursor
OPEN CURSOR c1 FOR
SELECT * FROM spfli.
OPEN CURSOR c2 FOR
SELECT * FROM scarr.
"读取cursor到工作区
FETCH NEXT CURSOR c1 INTO ls_spfli.
"读取cursor到table,into或append
FETCH NEXT CURSOR c1 INTO TABLE lt_spfli.
"FETCH NEXT CURSOR c1 APPENDING TABLE lt_spfli.
CLOSE CURSOR c1.
"package size,读取数据条数
FETCH NEXT CURSOR c2 INTO TABLE lt_scarr PACKAGE SIZE 2.
CLOSE CURSOR c2.
ENDFORM.
3.INSERT插入语句
插入成功条数sy-dbcnt,插入成功sy-subrc = 0。
语法:
INSERT { {INTO target VALUES wa } | { target FROM wa|{TABLE itab} } }.
语法:target
{dbtab|(dbtab_syntax)} [CLIENT SPECIFIED] [CONNECTION {con|(con_syntax)}]
语法:source
wa | {TABLE itab [ACCEPTING DUPLICATE KEYS] }.
示例:
"insert语句
FORM f_open_sql_insert.
"INSERT
DATA:ls_scarr TYPE scarr.
DATA:lt_scarr TYPE TABLE OF scarr.
ls_scarr-carrid = 'FF'.
ls_scarr-carrname = 'Funny Flyers'.
ls_scarr-currcode = 'EUR'.
ls_scarr-url = 'http://www.funnyfly.com'.
APPEND ls_scarr TO lt_scarr.
"插入单行
INSERT INTO scarr VALUES ls_scarr.
"插入单行
INSERT scarr FROM ls_scarr.
"插入多行
INSERT scarr FROM TABLE lt_scarr.
"可重复key
INSERT scarr FROM TABLE lt_scarr ACCEPTING DUPLICATE KEYS.
ENDFORM.
4.UPDATE更新语句
语法:
UPDATE target source.
语法:target
{dbtab|(dbtab_syntax)} [CLIENT SPECIFIED] [CONNECTION {con|(con_syntax)}]
语法:source
{ {SET set_expression [WHERE sql_cond]} | {FROM wa|{TABLE itab}} }.
set_expression:
[col1 = f1 col2 = f2 ... ]
[col1 = col1 + f1 col2 = col2 + f2 ...]
[col1 = col1 - f1 col2 = col2 - f2 ...]
[(expr_syntax1) (expr_syntax2) ...]
示例:
"update语句
FORM f_open_sql_update.
"update
DATA:ls_scarr TYPE scarr.
DATA:lt_scarr TYPE TABLE OF scarr.
ls_scarr-carrid = 'FF'.
ls_scarr-carrname = 'Funny Flyers'.
ls_scarr-currcode = 'EUR'.
ls_scarr-url = 'http://www.funnyfly.com'.
APPEND ls_scarr TO lt_scarr.
"方式1:update set c1 = f1 where cond
UPDATE scarr
SET carrname = ls_scarr-carrname
currcode = ls_scarr-currcode
WHERE carrid = ls_scarr-carrid.
"方式2:update from wa
UPDATE scarr
FROM ls_scarr.
"方式3:update from itab
UPDATE scarr
FROM TABLE lt_scarr.
ENDFORM.
5.MODIFY更新语句
语法:
MODIFY target FROM source.
target:
{dbtab|(dbtab_syntax)} [CLIENT SPECIFIED] [CONNECTION {con|(con_syntax)}]
source:
FROM wa|{TABLE itab}.
示例:
"更新
FORM f_open_sql_modify.
"modify
DATA:ls_scarr TYPE scarr.
DATA:lt_scarr TYPE TABLE OF scarr.
ls_scarr-carrid = 'FF'.
ls_scarr-carrname = 'Funny Flyers'.
ls_scarr-currcode = 'USD'.
ls_scarr-url = 'http://www.funnyfly.com'.
APPEND ls_scarr TO lt_scarr.
"单笔更新
MODIFY scarr FROM ls_scarr.
"多笔更新
MODIFY scarr FROM TABLE lt_scarr.
ENDFORM.
6.DELETE删除语句
语法:
DELETE { {FROM target [WHERE sql_cond]} | {target FROM source} }.
target:
{dbtab|(dbtab_syntax)}[CLIENT SPECIFIED][CONNECTION{con|(con_syntax)}]
source:
FROM wa|{TABLE itab}
示例:
"删除
FORM f_open_sql_delete.
"modify
DATA:ls_scarr TYPE scarr.
DATA:lt_scarr TYPE TABLE OF scarr.
ls_scarr-carrid = 'FF'.
ls_scarr-carrname = 'Funny Flyers'.
ls_scarr-currcode = 'USD'.
ls_scarr-url = 'http://www.funnyfly.com'.
APPEND ls_scarr TO lt_scarr.
"删除,delete from target where
DELETE FROM scarr WHERE carrid = 'FF'.
"delete target from wa/itab
DELETE scarr FROM ls_scarr.
DELETE scarr FROM TABLE lt_scarr.
ENDFORM.
7.Streaming and Locators
Open SQL使用data stream,locator访问数据库LOB数据。最多可以打开1000个LOB Handle。最多同时16个LOB Open SQL Statement。
类:
CL_ABAP_DB_C_READER:字符读取流
CL_ABAP_DB_X_READER:字节读取流
CL_ABAP_DB_C_WRITER:字符写入流
CL_ABAP_DB_X_WRITER:字节写入流
Locator:
CL_ABAP_DB_C_LOCATOR;
CL_ABAP_DB_X_LOCATOR
接口:
IF_ABAP_DB_READER
IF_ABAP_DB_WRITER
上级接口:IF_ABAP_DB_LOB_HANDLE
IF_ABAP_DB_BLOB_HANDLE
IF_ABAP_DB_CLOB_HANDLE
示例:
"BLOB读写
FORM f_open_sql_lob_insert.
"写入流
DATA:lt_blob TYPE TABLE OF demo_blob_table.
DATA:ls_blob TYPE demo_blob_table WRITER FOR COLUMNS picture.
DATA:lo_stmnt TYPE REF TO cl_abap_sql_changing_stmnt.
"读取图片资源
"文件名,MIME
DATA:lv_file TYPE C LENGTH 255 VALUE '/SAP/PUBLIC/TOM_TEST/pic.jpg'.
DATA:lo_mime_api TYPE REF TO if_mr_api.
DATA:lv_xstring TYPE xstring.
DATA:lv_x1024 TYPE x LENGTH 1024.
DATA:lt_x1024 LIKE TABLE OF lv_x1024.
"状态
DATA:lv_subrc TYPE sy-subrc.
"mime repository api对象
lo_mime_api = cl_mime_repository_api=>get_api( ).
"mime中图片
lo_mime_api->get(
EXPORTING
i_url = lv_file
IMPORTING
e_content = lv_xstring
).
DO.
IF xstrlen( lv_xstring ) > 1024.
lv_x1024 = lv_xstring(1024).
APPEND lv_x1024 TO lt_x1024.
SHIFT lv_xstring BY 1024 PLACES LEFT IN BYTE MODE.
ELSE.
APPEND lv_xstring TO lt_x1024.
EXIT.
ENDIF.
ENDDO.
TRY.
ls_blob-name = lv_file.
INSERT demo_blob_table FROM ls_blob.
IF sy-subrc = 4.
lv_subrc = 4.
ELSE.
lo_stmnt = ls_blob-picture->get_statement_handle( ).
LOOP AT lt_x1024 INTO lv_x1024.
lv_xstring = lv_x1024.
ls_blob-picture->write( lv_xstring ).
ENDLOOP.
ls_blob-picture->close( ).
IF lo_stmnt->get_db_count( ) <> 1.
lv_subrc = 4.
ENDIF.
ENDIF.
CATCH cx_stream_io_exception cx_close_resource_error.
lv_subrc = 4.
ENDTRY.
IF lv_subrc <> 0.
MESSAGE 'insert error' TYPE 'E'.
ELSE.
MESSAGE 'insert success' TYPE 'S'.
ENDIF.
ENDFORM.
"读取图片
FORM f_open_sql_lob_read.
"字节读取流
DATA:lo_reader TYPE REF TO cl_abap_db_x_reader.
"文件名,MIME
DATA:lv_file TYPE C LENGTH 255 VALUE '/SAP/PUBLIC/TOM_TEST/pic.jpg'.
DATA:lv_xstring TYPE xstring.
DATA:lv_x1024 TYPE x LENGTH 1024.
DATA:lt_x1024 LIKE TABLE OF lv_x1024.
SELECT SINGLE picture FROM demo_blob_table INTO lo_reader
WHERE name = lv_file.
"读取数据
WHILE lo_reader->data_available( ) = 'X'.
APPEND lo_reader->read( 1024 ) TO lt_x1024.
ENDWHILE.
lo_reader->close( ).
"显示图片
DATA:html_str TYPE string.
DATA:ext_data TYPE cl_abap_browser=>load_tab.
DATA:ext_line TYPE cl_abap_browser=>load_tab_line.
html_str = '<html><body><img src="PIC.GIF" ></body></html>'.
ext_line-name = 'PIC.GIF'.
ext_line-type = 'image'.
GET REFERENCE OF lt_x1024 INTO ext_line-dref.
APPEND ext_line TO ext_data.
cl_abap_browser=>show_html(
EXPORTING
html_string = html_str
format = cl_abap_browser=>landscape
size = cl_abap_browser=>small
data_table = ext_data
check_html = ' ' ).
ENDFORM.