教你如何通过Oracle游标直接操作Oracle系统表
4 T* X% _% {/ ~5 d 在ABAP编程中,有时候需要利用Native SQL操作数据库表(自定义和非业务数据的表,业务数据表我的建议还是应该通过SAP提供的类、函数,BAPI等技术操作)。下面的例子是通过游标操作Oracle的系统表的代码,供大家参考。
$ S! _0 o) P. t% G1 `: S9 y *--------------------------------------------* - b3 M- q6 X% [' |" f- x( E$ W+ _
* Read information about partitions of a table
9 Q; N: k, g# F# B *-------------------------------------------* * l! I$ `( f0 E E9 |
FORM get_partition_info & _* }3 _( k; |+ d% n4 g
USING & ^' @, ~3 z8 c2 e H
i_tablnm TYPE rsd_tablnm & `( {+ [4 S y2 l
CHANGING 5 m6 H2 _# M' c% z" ]0 W' j
e_ts_part_info TYPE rsdu_ts_part_info.
" K$ J; F1 [; H w! X& F% A( F DATA: $ I/ Y9 M( d1 ~" i1 `; A9 F' k
l_s_part_info TYPE rsdu_s_part_info. 5 C# M( L. ^ {6 r* o6 j
* this works only for Oracle
0 h/ Q; c0 M0 P; _* R- f3 t CHECK sy-dbsys = ’ORACLE’.
A# f! j3 z) C& E EXEC SQL.
; c% X, h7 V9 o! J v. A+ _ open csr for ' w8 [ x9 E# a, x3 @
select partition_name, high_value 4 @% s) `5 A5 ^
from user_tab_partitions N; u* W6 q1 [4 n; q
where table_name = :i_tablnm
9 u* @6 f, [9 _: h8 @ ENDEXEC.
7 Z8 B3 n4 Y Q7 X3 z5 } DO.
. U2 ? {& E4 |/ F EXEC SQL. " b0 S6 \; S2 [7 N* ^1 Q
fetch next csr into :l_s_part_info-partition_name, 4 j0 c# W) U8 Z7 T8 {5 `! e1 [
:l_s_part_info-high_value : w& K9 B1 L: T6 |1 w1 e0 T
ENDEXEC.
% A6 e" |" D t3 Z- G IF sy-subrc 0.
& z6 f2 o) r6 N. R$ [3 f. T EXIT.
4 J8 a; D4 X- F, J4 G0 X6 [2 d ENDIF.
2 r5 D1 Q7 U3 S3 u9 _ * Special treatment of upper limit (highvalue) in Oracle
) C. @7 h0 q( F- d6 }1 l IF l_s_part_info-high_value EQ ’MAXVALUE’. # x( @3 Y/ Z* [' S+ Q4 Z/ a& j
l_s_part_info-high_value = rsdu_c_max_value.
0 `% `' P' U, Y# G2 r$ Y8 K- i ENDIF. ( v' |4 a6 r& a/ P( q. r4 n
INSERT l_s_part_info INTO TABLE e_ts_part_info.
5 T* f% ]" u) k0 t) e ENDDO. ' C5 X6 ~$ q) L" u$ m8 w7 Y" R! j6 C
EXEC SQL. ! Y' ]. W4 b7 a, z+ g8 z) m- ~- [& G
close csr
2 n, m l" x9 K) T. S% T ENDEXEC. 1 t( o6 a" Q* G, {/ F- N
ENDFORM. " GET_PARTITION_INFO |