转:ABAP数据库操作
1、abap语言使用的数据库语言:open sql ,Native sql(特定数据库自身sql)
2、使用OPen SQL注意的原则:
3、使用Native sql有两个前提:
4、ABAP的数据定义由数据字典创建。
5、提取数据方式:内表,工作区,变量。
6、select语句:
select <result> from<source> into<target>
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
读取时:
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
检查选择表:where ...f [not] inseltab....
通过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'.
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 *
...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,
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.
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' //锁定
mode_sflight = 'X'
carrid
connid
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
size = leng * lins.
call function 'WS_DOWNLOAD'
exporting
tables
exceptions
...
if sy-subrc <>0
message e888(sabapdocu) with 'sy-subrc =' sy-subrc.
endif.
call function
exporting
tables
exceptions
...
if sy-subrc <> 0
message e888(sabapdocu) with 'sy-subrc =' sy-subrc.
endif.