SAP SQL Explorer工具增强版(也写个BLOG自己给自己纪念一下)-2

最新推荐文章于 2023-03-28 13:36:42 发布

cuangengzhi0895

最新推荐文章于 2023-03-28 13:36:42 发布

阅读量398

点赞数

文章标签：数据库

Table-Screen(通过动态的模式来生成)->其实现方式'FREE_SELECTIONS*的函数来实现的

可以看到SAP生成屏幕样式如下，如果是TABLES的话，那么需要改写该函数最后返回的结果才能被使用

Table-For All Entries-No Screen

都了一个配置For的选项，用来下载数据格式和上载数据

Join-Screen

通过上述按钮实现了TABLE信息，ON的信息，字段信息的控制

如上述分析的那样，需要改写SQL的语句的信息

3）Explain(Native_SQL)-ST05&ST04(这是SAP标准自己的功能，用于编写NATIVE SQL)

有兴趣的人，可以去看ST04中的功能，就是调用该函数，缺点就是需要辩解NATIVE SQL 语句，

4)SAP_SQL_Explorer(增强版)->增加以下几个功能，比如就是可以配置是否启动SQL TRACE等，可以使用SAP的模板工具等，

缺点就是，如果是自己的编写的模板，需要自己在后面添加，后续这边给个实现说明，

所有的代码都不需要通过INSERT REPORT的等方式来实现，完全是通过解释SQL STATEMENT 的方式来实现的

可以实现count(*)等类似的语句的解释，

测试代码如下:

我这边自己可以通过SQL语句生成器，来自己这边需要执行，尤其是NATIVE SQL（这边只适合ORACLE）

测试代码如下：

SELECT

支持复合函数(因为之前模式通过动态程序的方式，不需要，在RTTS模式，需要指定AS语句)

select count(*) from mara where matnr = 1.

select count(*) as count1 from mara where matnr = 1.

SELECT COUNT(*) AS count1 COUNT(*) as count2

INTO (g_count, g_count2)

FROM mara WHERE matnr = 1.

select SINGLE

max( MATNR ) as matnr into g_matnr

from mara where matnr = 1.

支持SINGLE语句

select SINGLE
MATNR
from mara .

支持 UP TO **ROWS语句

SELECT
  matnr
  ersda
  ernam
  laeda
  aenam
  vpsta
  pstat
  lvorm
  mtart
  mbrsh
  matkl
  bismt
  meins
  bstme
  INTO TABLE git_it_out
  FROM mara UP TO 100 rows.

支持UP TO ** ROWS +WHERE

SELECT
  matnr
  ersda
  ernam
  laeda
  aenam
  vpsta
  pstat
  lvorm
  mtart
  mbrsh
  matkl
  bismt
  meins
  bstme
  INTO TABLE git_it_out
  FROM mara UP TO 100 rows
  where mtart eq 'G001'.

支持INTO CORRESPONDING FIELDS OF TABLE或INTO TABLE或APPENDING语句

直接INNER JOIN 或OUTER JOIN

SELECT

A~z_no

A~bukrs

A~werks

A~lgort

A~z_data_type

A~matnr

A~maktx

A~charg

A~menge

A~meins

A~dmbtr

A~posid

A~post1

A~p_post1

A~z_class_a

A~z_class_adesc

A~z_class_b

A~z_class_bdesc

A~z_class_c

A~z_class_cdesc

A~z_factory_type

A~z_stock_type

A~z_source_dept

A~z_stock_qty

A~z_stock_age

A~z_overstock

A~z_overstock_type

A~z_desc1

A~z_mdf_dat

A~z_mdf_user

A~tz_fact_qty

A_d~z_item

A_d~z_chk_qty

A_d~z_chk_view

A_d~z_plan_date

A_d~z_plan_qty

A_d~z_plan_ret

A_d~z_fact_date

A_d~z_fact_qty

A_d~z_fact_ret

A_d~z_unfinish_desc

A_d~z_overstock_dept

A_d~z_mat_pro_sort

A_d~z_pro_over

A_d~z_instock_dat

A_d~z_stock_dat

A_d~z_discard_res_ty

A_d~z_discard_sum_ty

A_d~z_plan_sale_val

A_d~z_manage_date

A_d~z_outstock_obj

A_d~z_outstock_sort

A_d~z_desc2

APPENDING CORRESPONDING FIELDS OF TABLE it_out02 FROM A

LEFT OUTER JOIN A_d

ON A~z_no = A_d~z_no AND

A~bukrs = A_d~bukrs AND

A~werks = A_d~werks AND

A~lgort = A_d~lgort

WHERE

A~tz_fact_qty > 0 AND

A~tz_fact_qty < A~menge .

支持sql trace

Update & Delete

UPDATE ztestmass SET ernam = 'John'
WHERE ebeln = '0000000011' AND ebelp = '00002'.

可以在这边直接执行更改

或者不需要查看数据就直接修改

DELETE FROM ztestmass
WHERE ebeln = '0000000011' AND ebelp = '00003'.

5)Native SQL Explorer(目前这边的功能支持SELECT/UPDATE/DELETE/INSERT)->目前只是ORACLE测试

缺点：完全通过分析SQL STATEMENT来实现改进（候选可以把这边的功能与现在编写NATIVE SQL 的封装类进行集成的话，可不考虑很多细节）有时间的修正

可以支持DBCO的信息：

目前只是测试本地的SAP ORACLE的数据

Insert

INSERT INTO ZTESTMASS VALUES ( '140' , '0000000013' , '00001' , '000000000500000099' ,
'Jonh3' , '20130701' , '30' , 'A4' , '100101' )

INSERT INTO ZTESTMASS ( MANDT,EBELN,EBELP,ERNAM ) VALUES('140','0000000013','00002','Jonh3')

Update

UPDATE ztestmass SET ernam = 'JohnNav'
WHERE ebeln = '0000000011' AND ebelp = '00002'

DELETE

DELETE FROM ztestmass
WHERE ebeln = '0000000013' AND ebelp = '00002'

SELECT

select * from ztestmass

SELECT

matnr,

ersda,

ernam,

laeda,

aenam,

vpsta,

pstat,

lvorm,

mtart,

mbrsh,

matkl,

bismt,

meins,

bstme

FROM mara

where mtart = 'G001'

如果是SAP数据，那么也可以启用DDIC的信息的处理

SELECT
s.z_no,
s.bukrs,
s.werks,
s.lgort,
s.z_data_type,
s.matnr,
s.maktx,
s.charg,
s.menge,
s.meins,
s.dmbtr,
s.posid,
s.post1,
s.p_post1,
s.z_class_a,
s.z_class_adesc,
s.z_class_b,
s.z_class_bdesc,
s.z_class_c,
s.z_class_cdesc,
s.z_factory_type,
s.z_stock_type,
s.z_source_dept,
s.z_stock_qty,
s.z_stock_age,
s.z_overstock,
s.z_overstock_type,
s.z_desc1,
s.z_mdf_dat,
s.z_mdf_user,
s.tz_fact_qty,
s_d.z_item,
s_d.z_chk_qty,
s_d.z_chk_view,
s_d.z_plan_date,
s_d.z_plan_qty,
s_d.z_plan_ret,
s_d.z_fact_date,
s_d.z_fact_qty,
s_d.z_fact_ret,
s_d.z_unfinish_desc,
s_d.z_overstock_dept,
s_d.z_mat_pro_sort,
s_d.z_pro_over,
s_d.z_instock_dat,
s_d.z_stock_dat,
s_d.z_discard_res_ty,
s_d.z_discard_sum_ty,
s_d.z_plan_sale_val,
s_d.z_manage_date,
s_d.z_outstock_obj,
s_d.z_outstock_sort,
s_d.z_desc2
FROM s
, s_d
WHERE s.z_no = s_d.z_no AND
s.bukrs = s_d.bukrs AND
s.werks = s_d.werks AND
s.lgort = s_d.lgort