ABAP数据库操作

转：ABAP数据库操作

——学习SAP程序设计的整理-数据库

1、abap语言使用的数据库语言：open sql ,Native sql(特定数据库自身sql)
2、使用OPen SQL注意的原则：
a、尽可能减少满足条件的数据条目数量。
b、减少数据的传输量，以减少网络流量。
c、减少访问的数据库表量。
d、减少查询难度，可以通过整理选择标准来实现。
e、减少数据库负载。
3、使用Native sql有两个前提：
a、知道使用数据库的类型。
b、了解该数据库的SQL语法。
4、ABAP的数据定义由数据字典创建。
5、提取数据方式：内表，工作区，变量。
6、select语句：
select <result> from<source> into<target>
where <condition> [group by<field>]
[having <cond>][order by<field>].
7、选择单行全部数据：
select single * from spfli into wa_spfli where cityform='singapore'and into
cityto='beijing'.
8、选择单行指定字段：
select single carrid connid from spfli into (wa_carrid,wa_connid)where cityform='singapore'
and into cityto='beijing'.
9、选择相关字段：
select single carrid connid *from spfli into corresponding fieldsof
wa_spfli where cityform='singapore' and intocityto='beijing'.
10、循环选择：
select *
from spfli into wa_spfli.
write:/ wa_spfli-carrid,wa_spfli-connid.
endselect.
11、选择至内表：
select *
from spfli into table ta_spfli.
读取时：
loop at ta_spfli.
write:/ta_spfli-carrid ta_spfli-connid.
end loop.
12、指定查询条件
比较运算符:= < ><><=>=
范围限定运算符: [not] between
字符比较运算符：[not] like'_'替代单个字符，'%'任意字符
忽略符号：
select....where func like 'EDIT#_%' escape '#'.escape是指忽略'#'。
检查值列表：
select .....where city in('Berlin','Rome','London').指定城市'Berlin','Rome','London'。
检查空值：where ...f is [not] null.....
检查选择表：where ...f [not] inseltab....seltab是选择标准表，是具有特定格式的内表，可以
通过select-options语句添加到程序和报表选择屏幕，并由报表用户填充，在可以在程序中创建（如使用
range语句）
13、动态指定查询条件：
report Z_test.
data:cond(72) type c,
itab like table of cond,
city1(10) value 'BEIJING',
city1(10) value 'SINGAPORE',
itab_spfli like talbe of spfli with header line...
concatenate 'cityfrom = '''city1'''' into cond.
append cond to itab.
concatenate 'cityfto' ='''city2'''' into cond.
append cond to itab.
select * into table itab_spfli from spfli
where (itab).
14、多表结合查询(嵌套，效率较低)：
reprot z_test.
data: wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_carrname type scarr-carrname.
select carrid connid
from spfli into (wa_carrid,wa_connid)
where cityform='singapore' and into cityto='beijing'.
select carrname from scarr into wa_carrnamewhere carrid = wa_carrid.
write wa_carrname.
endselect.
endselect.
15、for all entries选项
reprot z_test.
data: begin of wa_spfli,
carrid type spfli-carrid,
connid type spfli-connid,
end of wa_spfli,
begin of wa_scarr,
carrid type scarr-carrid,
carrname type scarr-carrname,
end of wa_scarr,
spfli_tab like table of wa_spfli.
select carrid connid
from spfli
into table spfli_tab
where cityfrom ='Singapore'.
select carrid carrname
from scarr
into wa_scarr
for all entires in spfli_tab
where carrid = spfli_tab-carrid.
...
endselect.
16、使用视图
reprot z_test.
data: wa_carrid type scarrspfli-carrid,
wa_connid type scarrspfli-connid,
wa_carrname type scarrspfli-carrname.
select carrid carrname connid
from scarrspfli
into (wa_carrid,wa_carrname,wa_connid)
where cityfrom = 'Singapore'.
...
endselect.
17、结合查询
内连接：inner join 主表和结合表都满足on的条件
左连接：left join主选择表的数据，即使在结合表中不存在，也会查询出，以空白显示。
report z_test.
data:wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_carrname type scarr-carrname.
select spfli-carrid scarr-carrname spfli-connid
from spfli
inner join scarr on spfli-carrid =scarr-carrid
into (wa_carrid,wa_carrname,wa_connid)
where spfli-cityfrom = 'Singapore'
..-
endselect.
18、子查询（没有into子句）
select ....
from scarr
into
where exist (select *
from spfli
where carrid = scraa-carrid and cityfrom='Singapore').
...where city in (select cityform from spfli where carrid =scarr-carrid...)
...where city = (select cityform from spfli where carrid =scarr-carrid...)
...where city > all (select cityform from spfliwhere carrid = scarr-carrid...)
19、组合结果查询
总计功能
select carrid connid sum(seatsocc)
from sflight
into (wa_carrid,wa_connid,sum_seatsocc)
where spfli-cityfrom ='Singaport'.
分组统计：
select carrid min (price) max(price)
into (carrid,minnum,maxnum)
from sflight
group by carrid
write:/ carrid,minnum,maxnum.
endselect.
指定分组条件：
select carrid min(price) max(price)
into(carrid,minnum,maxnum)
from sflight
group by carrid
having min(minnum)>1000.
指定行的顺序：
select carrid connid max(seatsocc) as max
into (wa_carrid,wa_connid,sum_seatsocc)
from sflight
group by carrid
order by carrid ascending max descending.
20、使用表工作区：
声明：tables dbtab.
tables spfli.
...
select single * from spfli wherer cityfrom ='Singapore'.
write:/ spfli-corrid..
21、动态指定数据库表
dbname='spfli'.
select carrid connid
from (dbname) into (carr_id,conn_id)
where cityfrom = 'Singapore'.
22、指定数据区域
select * from spfli client specified into ....
where mandt between '100' and '103'.
//从表spfli中读取集团100到103中存储的所有数据。
23、设置缓冲机制
select....from dbtab bypassing buffer...取消在数据字典中对该表设定的缓冲。
使用distinct与结合选择，总计选择，is null条件，子查询，以及group by ,orderby同时使用时，也
会自动忽略缓冲。
24、限定选择的行数
select ...from dbtab up to n rows....
25、操作性能分析
report z_test.
data:wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_carrname type scarr-carrname.
data:t1 type i,t2 type i,time type i,n type i value 1000.
do n times.
get run time field t1.
select carrid connid from spfli
into (wa_carrid,wa_connid) where cityfrom = 'Singapore'.
select carrname from scarr
into wa_carrname where carrid = wa_carrid.
...
endselect.
endselect.
get run time field t2.
time = t2-t1.
enddo.
write :/ 'Runtime:',time.
26、使用数据库光标（就是游标）
report z_test.
data: cur type cursor,
wa_carrid type spfli-carrid,
wa_connid type spfli-connid,
wa_cityfrom type spfli-cityfrom,
wa_cityto type spfli-cityto.
start-of-selection.
open cursor cur for
select carrid connid cityfrom cityto
from spfli
where carrid= 'AA'
order by carrid.
...
do.
fetch next cursor cur
into (wa_carrid,wa_connid,wa_cityfrom,wa_cityto).
...
if sy-subrc <> 0.
close cursor cur.
exit.
endif.
enddo.
27、更新数据
插入单行数据
insert into dbtab values wa.
insert into dbtab form wa.
插入多行数据
insert dbtab from table itab.
更新单行数据
update dbtab from wa.
更新多行数据
update dbtab set f1=g1...fi=gi [where<conditions>].
update target from table itab.(从内表)
添加或更新单行
modify dbtab from wa.(已存在则更新，不存在则插入)
添加或更新多行
modify dbtab from table itab.(从内表)
删除单行数据
delete from dbtab where<fix_key>.
delete from dbtab from wa.
删除多行数据
delete from dbtab where<conditions>.
delete from [client specified] table itab.(从内表)
删除所有数据
.在通过内表删除多行数据条目的过程中将内表置为空。
.使用where field like '%' 作为where子句中的唯一条件。
28、数据库表的锁定
report z_test.
data:wa_sflight like sflight.
wa_sflight = 'CA'.
...
call function 'ENQUEUE_ENEMOFLHT' //锁定
EXPORTING
mode_sflight = 'X'
carrid= wa_sflight-carrid
connid= wa_sflight-connid
fldate= wa_sflight-fldate
EXCEPTIONS
foreign_lock =1
system_failure =2
OTHERS =3.
if sy-subrc <>0.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endif.
update sflight set carrid = wa_sflight-carrid. //数据处理
call function ''DEQUEUE_EDEMOFLHT. //解除锁定
29、程序中的授权检查
report z_test.
parameters p_carrid type sflight-carrid.
authority-check object 's_carrid'
id 'CRRID' field p_carrid
id 'ACTVT' field '03'.
if sy-subrc = 4.
message e045(sabapdocu) with p_carrid.
elseif sy-subrc <>0.
message a888(sabapdocu) with 'Error!'.
endif.
30、应用服务器文件操作
report z_test.
parameters file(30) type c default '\tmp\myfile'.
data: wa_sflight type sflight,
sflight_tab_1 like table of wa_sflight,
sflight_tab_2 like table of wa_sflight.
open dataset file for output in binary mode.
select * from sflight into wa_sflight.
transfer wa_sflight to file.
append wa_sflight to sflight_tab_1.
endselect.
close dataset file.
open dataset file for input in binary mode.
do.
read dataset file into wa_sflight.
if sy-subrc <> 0.
exit.
endif.
append wa_sflight to sflight_tab_2.
enddo.
close dataset file.
if sfilght_tab_1 = sflight_tab_2.
message i888(sabapdocu) with 'ok'.
endif.
31、展示服务器文件操作
report z_test.
parameters: fname type rlgra-filename default'c:\temp\myfile.dat',
ftype type rlgra-filetype default 'BIN',
data:
sflight_tab_1 like table of sflight,
sflight_tab_2 like table of sflight,
tab_line like line of sflight_tab_1,
leng type i,
lins type i,
size type i.
select * from sflight into table sflight_tab_1.
describe field tab_line lenght leng.
describe table sflight_tab_1 lines lins.
size = leng * lins.
call function 'WS_DOWNLOAD'
exporting
filename=fname
filetype=ftype
bin_filesize=size
tables
data_tab=sflight_tab1
exceptions
...
if sy-subrc <>0
message e888(sabapdocu) with 'sy-subrc =' sy-subrc.
endif.
call function 'WS_UPLOAD'
exporting
filename =fname
filetype=ftype
tables
data_tab=sflight_tab_2
exceptions
...
if sy-subrc <> 0
message e888(sabapdocu) with 'sy-subrc =' sy-subrc.
endif.