教你如何通过Oracle游标直接操作Oracle系统表
- n+ S; [6 x% X! ^6 h9 A 在ABAP编程中,有时候需要利用Native SQL操作数据库表(自定义和非业务数据的表,业务数据表我的建议还是应该通过SAP提供的类、函数,BAPI等技术操作)。下面的例子是通过游标操作Oracle的系统表的代码,供大家参考。
% K5 s' n6 g; ^1 j; z4 v/ W *--------------------------------------------* 5 v; n! C7 _& N: R% _
* Read information about partitions of a table 1 z/ g" t ~/ y. e7 n- v0 C
*-------------------------------------------* ' f5 M# I5 B% ^+ e$ v+ b
FORM get_partition_info
1 Q3 e9 l# _) W) A USING ~8 l" V2 o) }) t3 a$ k8 z
i_tablnm TYPE rsd_tablnm & D) |7 D7 v1 b, j( _7 F0 w
CHANGING , p9 @2 c6 ?8 S! E6 H" A% [: H) S- s% o
e_ts_part_info TYPE rsdu_ts_part_info.
: O7 @" Q F; t$ o9 k o DATA:
2 b4 _' k' s: ^' T l_s_part_info TYPE rsdu_s_part_info.
5 w Q$ o% m7 O( _ * this works only for Oracle
) O8 |! Z% F6 S% R CHECK sy-dbsys = ’ORACLE’. - E: [4 n5 m) [! @- ?0 F
EXEC SQL. c; F S* `: @/ Q: }- r' }9 K7 g
open csr for 6 N4 M( i' M& r V- R
select partition_name, high_value
6 m) b# P: R3 p: z! t4 d: } from user_tab_partitions - q$ r6 L: Y$ s! M+ a
where table_name = :i_tablnm % }4 G, ~! }* h* j' \; y9 v v4 \: m
ENDEXEC.
! |6 `& f# m# D/ ?; @- ^ DO.
! {) D/ d" g* T: ]! S4 x/ E. X4 R EXEC SQL. # M$ T U w3 l# E3 e8 g% l4 I7 Q
fetch next csr into :l_s_part_info-partition_name,
; |+ D* {& q/ y, D. g5 u; w :l_s_part_info-high_value 6 L4 y. b" `* Z4 |. y, @
ENDEXEC.
8 y( ?7 D4 O& c$ { IF sy-subrc 0.
g) p* L/ u% Z2 `" r% t4 j& w EXIT.
$ }" n4 ~" m. t/ V ENDIF. ) O* F/ ^9 x* c: x2 F. ?3 `' C
* Special treatment of upper limit (highvalue) in Oracle 7 K- e& `1 Z; e& u g7 A
IF l_s_part_info-high_value EQ ’MAXVALUE’.
1 `/ V0 c6 X8 z2 m& I l_s_part_info-high_value = rsdu_c_max_value.
* t- \1 Y" A( Q1 D# G. g ENDIF. / {; v0 q; l. `8 Q
INSERT l_s_part_info INTO TABLE e_ts_part_info. ! S5 ~$ {+ B1 g+ `
ENDDO. 6 K7 E# y. L0 B" b. a
EXEC SQL. * e0 m# B9 _$ p& P" k1 y
close csr
6 F& _9 _; }8 D ENDEXEC. ! d5 L4 w+ I6 g P, A
ENDFORM. " GET_PARTITION_INFO |