ABAP Help Document(23):11.1 OPEN SQL
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.
本文来自博客园,作者:渔歌晚唱,转载请注明原文链接:https://www.cnblogs.com/tangToms/p/14696738.html