注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

黑山老妖的博客

 
 
 

日志

 
 
关于我

主要用来存放收集的资料,几乎都是转贴的东西!感谢原作者!

网易考拉推荐

ABAP数据库操作(学习SAP程序设计的整理-数据库)  

2008-06-17 16:33:23|  分类: ABAP |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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 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 <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.

  评论这张
 
阅读(1575)| 评论(4)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018