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 from into
where [group by ]
[having ][order by ].
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 fields of
wa_spfli where cityform='singapore' and into cityto='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] in seltab.... 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_carrname where 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 spfli where 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 ,order by同時使用時，也
會自動忽略緩衝。
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 ].
update target from table itab.(從內表)
新增或更新單行
modify dbtab from wa.(已存在則更新，不存在則插入)
新增或更新多行
modify dbtab from table itab.(從內表)
刪除單行資料
delete from dbtab where .
delete from dbtab from wa.
刪除多行資料
delete from dbtab where .
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 'tmpmyfile'.
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:tempmyfile.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.<source>

thank you

come from :