a我考网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 481|回复: 4

[考试辅导] Oracle中提取和存储数据库对象的DDL

[复制链接]
发表于 2012-8-4 14:06:19 | 显示全部楼层 |阅读模式
从对象(例如数据库表、索引、约束、触发器等)中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据(metadata),并把这些数据存储在内存中。尽管目前有很多脚本可以实现这样的功能,但是它们通常都是不完整的或者过时的。幸运的是,Oracle 9.2提供了一个实现这样的功能的API:DBMS_METADATA程序包。
# p  w. A. {$ i# s
4 G2 f6 s$ l# {, V  在很多情况下,数据库中数据的维护操作要求我们提取多种对象(例如数据库表、索引、约束、触发器等)的DDL(Data Definition Language,数据定义语言)。
  D1 z: ^) U9 X" J( D6 d
" u! M. W/ ^- e) [- c  最近我承担了一个任务,我需要编写一组数据库程序包来执行高性能的大量的数据删除(DELETE)操作。这样的操作要求我拥有提取和存储数据库对象DDL的相关技术。
! M- o# G* v& Q' ]' @) l! U" @
  提取和存储数据库对象的DDL的方法如下:
1 ]3 O2 C( {9 o7 }  }8 g
4 S9 z6 Y' ?+ J" H* c  · 建立与源表结构相同的数据表,但是它不带主键、备用键和外部键约束。
& b! I+ b. \* J6 S$ f2 a% ^0 l) N/ m! O) i- a1 f
  · 例如,使用MyTable_X,其中MyTable是要被删除的目标数据表。0 h; l$ }. t: s% Y) o

1 O+ L2 u" }; e- s/ r/ G4 |7 K9 f* V  · 把需要保存的数据插入新建立的数据表(MyTable_X)中。
0 u( h& M6 [5 ^5 @0 I, W. @% r) e9 f& p2 i5 {
  · 使用NOLOGGING PARALLEL选项在新数据表上建立索引。; o2 K% y/ R' K4 ~) H8 @

% S, ^+ @( ~1 m  L/ u  C  · 在新数据表上建立约束。2 s9 ~2 f$ Q: A+ K

( J; X2 i( U& z2 X  · MyTable和MyTable_X数据表进行交换。把主表改名为MyTable_T,把MyTable_X改名为MyTable。
7 k: p* x1 h: e, n$ v/ @: N& L. a: z6 t
  · 验证结果并删除MyTable_T表。5 j: e; }; v7 ~# l& X$ T8 @
& |$ M5 @9 N* P3 t. G
  很明显,为了编写实现上面目标的代码,你必须提取数据库对象的元数据(定义和被选中的属性),并把它存储在内存中,这样在执行上面的操作的时候才能够使用它。
) _$ J' X, o" ]  s, H) H/ B
" m& F2 r1 b, C5 r/ p  在网上存在大量的脚本,它们可以从多种Oracle数据字典(user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等)中提取数据库对象的元数据,接着为特定的对象构造DDL命令。这些脚本的一个问题是,它们通常是SQL*Plus脚本,它会生成客户端文本文件,而这个文件不能被服务器端代码访问。它们的主要问题有:
) Z% A$ y5 d; Y+ R9 {& t) z/ W
# R  B  b' z3 p7 P0 |4 w  · 不完整:不能提取所有的选项,并组合进DDL语句中。- j. u2 _' f% g  d, T& k
4 D2 |( e/ o2 U5 F! U9 y+ b
  · 过时了:这些脚本通常不支持Oracle最新的数据库特性--分区(partitioning)、基于函数的索引、自动段空间管理(ASSM)等。这些脚本可能崩溃或生成错误的DDL语句。
0 N! G. @% O' B5 B, a
3 H, r# A( [. a3 T  问题总结:尽管有大量的从Oracle数据字典中提取数据库对象元数据的脚本,但是它们中的大多数要么不完整,要么过期了。* }" p: @/ b1 C  c; O% w
0 A1 i. p4 F- t% X  Q
  解决方案:使用DBMS_METADATA程序包,学习如何用最佳的、没有错误的和易于维护的方式执行上面的事务。
8 X4 @& k- j5 ?, q$ I- z# R! O5 S, p
  使用Oracle的本地API:DBMS_METADATA程序包# Q. _, k% p# P5 Y4 C8 Z9 H# M
5 @7 T  S# Q( g
  Oracle数据库采用补充PL/SQL程序包的形式提供了丰富的预先包装好的API。Oracle 9.2版本中引入的DBMS_METADATA程序包可能正好适合你的需求。它包含了用于检索数据库对象定义的API。
; ]. n: y% h* g: _2 c
& M9 D3 n3 [6 a$ k2 I  我们将使用的API主要是DBMS_METADATA.GET_DDL函数。这个函数返回的对象定义SQL字符串是CLOB。它拥有下面一些输入参数:, {1 P$ O0 I; C3 R
# Q+ Q  K# g* [) I7 m( z
  · object_type VARCHAR2 * S/ j' e) k7 n8 ^" a3 V6 t
( f& `- x1 ]' F$ _3 ~
  · name VARCHAR2 $ f8 x/ h" e# E. e! m; \& @

9 ^/ d: b- ]* T6 E  · schema VARCHAR2 DEFAULT NULL
' E; S) }# g0 [3 \* p8 J( P) c1 l0 ?; q) t) u; G1 R) j8 D) K  m
  · version VARCHAR2 DEFAULT ’COMPATIBLE’
! R  W/ p: T1 b* b2 [% ^0 g! I: N2 d& \. ?+ e8 l6 D8 B! x2 B
  · model VARCHAR2 DEFAULT ’Oracle’,
* z: B/ g* L$ W- v8 \/ g% p3 Z" V# C0 a' J# B. w
  · transform VARCHAR2 DEFAULT ’DDL’ * R8 T9 V9 j4 v% ?( X% r2 A

9 |8 _7 H6 v7 |! b; {8 I: ^  下面建立了一个用于测试的EmpTest数据表,它带有索引和约束:
% r$ d2 S+ g1 c% ~
+ e# O1 C* z2 Y8 Ocreate table EmpTest' c- @* s1 Y3 @) {( ~/ a0 w" A
( . g( h; w3 l- e( d
empNo integer not null,: L0 s7 X/ Y* _$ E
lastName varchar2(30) not null,8 C% Z: B) g# a1 L/ N* t. X5 `
firstName varchar2(20) not null,
' v& W( W& i/ J  ^2 [$ wjob varchar2(9) ’4 i0 s' @  x. o# k" _% n
hireDate date ’9 X& P1 ^) D" |# ^
isActive number(1)
8 V1 }7 D; N/ V0 f; Y1 y+ c9 ~constraint EmpTest_CK18 ~% o0 Q# g/ n) H- o  ^6 C0 K
check (isActive in (0,1)) ,, `/ d4 x0 {. i  g5 f
salary number(9,2) ,
; W3 i9 b. R* Z9 ?' Xcommision number(9,2) ,
  r7 l* @7 }1 L- L" FdeptNo number(2) ,
9 M1 _& U4 Z' k) @1 uconstraint EmpTest_PK
. z2 X' c  f- P8 u: t. [' eprimary key (empNo),
- @& _1 h; x+ ~" u& e5 iconstraint EmpTest_AK1
9 V+ b/ ^. S# v3 qunique (lastName, firstName)
# y& F4 x+ [8 C4 _5 z5 v" {);" j/ ^  |. E9 l! M

7 M: Y% A; r  b  q  j9 ^create index EmpTest_HireDate_Salary
9 ]7 |7 p& \, t$ bon EmpTest
7 Q. {" M4 Z& k; j) b1 u(
& V$ l& V, _( Z8 P  Usalary,
7 _4 E) `/ L1 zhireDate
  m3 y$ Q/ i3 E1 M0 I. |);, b; [2 v  B2 k+ R6 W# {  R' C
  运行上面的脚本之后,就建立了一个带有三个索引(两个唯一的和一个不唯一的索引)的EmpTest表:
. b0 n2 B5 R5 \7 }. y
9 g3 B2 a/ M$ hselect index_name, index_type, uniqueness9 `& {- x: W' O0 j3 c' h( _
from user_indexes
6 \6 U+ E; u7 X# gwhere table_name = ’EMPTEST’;' R7 p- f( H  ^) u
索引名称索引类型唯一性EMPTEST_AK1NORMALUNIQUEEMPTEST_HIREDATE_SALARYNORMALNONUNIQUEEMPTEST_PKNORMALUNIQUE
" R7 |9 Z  R# R+ b& A  EmpTest表还包括六个约束:
# A7 w5 p/ x; [  k) B# S0 l7 n" B
4 k1 p+ o1 P* s( p" B. n) v  · 一个主键-EmpTest_PK $ f0 s/ t0 i! i/ }

9 O/ A1 s  {+ H7 Y  · 一个备用键-EmpTest_AK ; `6 I7 v* u' b% \2 K, D. I

  k; ^4 y  L0 z  ~  · 一个检查约束-EmpTest_CK1 ) W' r  X' Q) M' h4 K8 v5 H! @/ N

- w1 w' ]* |) B+ B; t. i  · 系统生成的(SYS_*)三个非空的约束,名称如下:) r0 C0 [. y8 ~2 N* v) `+ t
$ N5 b/ T7 m- k( c, T
约束名称约束类型索引名称SYS_C002144065C  SYS_C002144066C  SYS_C002144067C  EMPTEST_CK1C  EMPTEST_PKP EMPTEST_PKEMPTEST_AK1U EMPTEST_AK1
* p- B" w- y. C9 D  现在我们执行匿名的PL/SQL代码块来调用DBMS_METADATA.GET_DDL函数,检索数据表的定义。" h- \+ z  C8 Z* ^$ M
. M* I2 n6 E" U( f/ a! R/ }# j
  DBMS_OUTPUT程序包只能输出最长为255个字符的字符串,由于在处理数据表的DDL字符串的时候太容易超过这个限制,所以这是一个问题。为了解决这个问题,我们使用了本地过程Show()(列表1所示)。
回复

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:20 | 显示全部楼层

Oracle中提取和存储数据库对象的DDL

列表1:调用DBMS_METADATA.GET_DDL()函数的PL/SQL代码块8 i) }# g( m' a

: e; n2 ]0 }" L, Jdeclare
. D8 @0 P  A) L6 dvClob clob;$ S. v2 r9 v5 [9 y) ~! N4 [( G& _
vLongString varchar2(32767);- ^) j6 a* M& R' Q% D" _: M
vOffSet pls_integer := 0;( X2 M$ C, \! Y6 e7 |. v8 g8 g
vLength pls_integer := 0;
7 ?% P: e3 W% d4 k$ b9 I. \* @+ kvTable varchar2(30) := ’EmpTest’;( Q; f! W- u% |( N2 v) w/ E3 l
% s  o: @( e/ G+ L  A' E
procedure Show (pVariable varchar2, pLineSize pls_integer := 80)
2 }' q2 @4 `' i$ J  F8 u5 C' Yis7 f( H7 X! ]% q8 d0 r) c7 @3 t" [
begin , [1 i/ J, V# _
dbms_output.enable(1000000); 0 M8 J/ Y# I# H! U! J; q* b
if (length(pVariable) > pLineSize)
9 x- e: w* g- p& K1 cthen
4 U. _  J0 t# J1 A9 Zdbms_output.put_line(substr(pVariable, 1, pLineSize));2 V! l' y" J8 U+ Z
Show(substr(pVariable, pLineSize + 1), pLineSize);) K! w$ A. F. d; Q% F
else ' u/ C/ g5 n4 t# f
dbms_output.put_line(pVariable);# z) o( `( I1 y, k
end if; , B& a2 h& V( Q6 U  @- W2 h
end Show;* H8 @/ K- Y+ T, X* [) y, z
begin
6 @! \# q: c& }% L' d! y-- 获取 DDL
! z6 i- b' \4 ]( m' wvClob := dbms_metadata.get_ddl(’TABLE’, upper(vTable));
2 o, f) W9 \% T: S$ h1 G5 m7 K5 ?) e% l* S
-- 获取 CLOB 长度
9 L% A" h. v  t) r4 q% v  \# wvLength := dbms_lob.GetLength(vClob);
- u! w( m$ Z7 J( n/ R" m& ^" f0 fdbms_output.put_line(’DDL length: ’ || to_char(vLength));& ~) `. z* X: `+ Y6 y2 \
$ K9 R( }. E8 D( O6 u2 k& _: g' ?, c
vOffSet := 1;# C# e* \$ V' P2 G, \3 }0 |% C$ g, V
dbms_lob.read(vClob, vLength, vOffSet, vLongString);; P- p) O9 |0 U) t0 T* k+ H5 g! ^
-- 关闭 CLOB
( C% [3 X; x- I1 {+ Z% V' cif (dbms_lob.isOpen(vClob) > 0)! ^  M: {+ c# C- d' D9 ~/ s" V% c; E$ c
then
% j( I( t' g. ]$ @" t4 [% P! cdbms_lob.close(vClob);+ o( [$ Y8 O" w8 d- y( x
end if;
) y6 @. T/ ^1 s, x- sShow(vLongString, 80);& V1 G9 k5 c) q5 }  \% @
end;
8 L! ^# w: p2 C5 o3 R0 U  列表1生成下面的输出信息:' G. Y& c  z! @8 r- u0 P0 N
0 M5 B  v, b$ A
DDL length: 461" w, l1 Z( V0 t
CREATE TABLE "BORIS"."EMPTEST"
+ Z7 ^- g  U; e; T& Z, [3 V2 j# w( "EMPNO" NUMBER(*,0) NOT NULL ENABLE, ) |3 T# n9 Q: L* l5 o
"LASTNAME" VARCHAR2(30) NOT NULL ENABLE, 9 ]( |; z+ ?0 |: a4 E* S7 T4 c/ O* Z
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE, 1 g* k8 {4 X2 @7 d" |7 k6 e: F. X
"JOB" VARCHAR2(9), 2 x: {( z) P5 @1 w) A+ j: t
"HIREDATE" DATE, 4 @* E+ X3 H! h' Z
"ISACTIVE" NUMBER(1,0),
6 j( X' c3 k& I/ L/ N"SALARY" NUMBER(9,2),
; Y4 ?$ w2 B$ c( E& ]: u# W& r"COMMISION" NUMBER(9,2),
. W( E4 A- a: p' j5 c6 x"DEPTNO" NUMBER(2,0), 9 V; q) K1 F9 i8 R  d! O* R7 b/ V
CONSTRAINT "EMPTEST_CK1" CHECK (isActive in (0,1)) ENABLE,
1 r% Z) d6 V, |* ^' LCONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")
; L- K3 U, e' ?* e6 `USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
- N: @2 A- F  {/ m, ~3 \8 C% Z, {STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
* i: o; v; W& _3 d5 C; @% a( ZPCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
, Q6 F) B! F( M% h( m1 ]TABLESPACE "TOOLS" ENABLE, CONSTRAINT "EMPTEST_AK1" UNIQUE ("LASTNAME", "FIRSTNAME")# {; |+ s  V/ l) v) H% P- x3 d; V
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 6 W$ z! s* |! s4 U  Y% N7 N# L
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/ z/ x; Q% n# d% v" W
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) l7 `- ^5 H# [  A2 {* v8 Y
TABLESPACE "TOOLS" ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 / W: e% N2 u% R1 O$ O! C
MAXTRANS 255 NOCOMPRESS LOGGING
. l5 H& L( q# i  }/ t# V8 aSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 y3 p' \* H% I* d/ t7 `+ J$ OPCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
; c2 k: x  @* _0 ^7 X9 g" X$ r. w6 }; uTABLESPACE "TOOLS"
  q% J' p; t, q: {) f3 j/ s1 c  它运行的情况太好了,返回的数据表的DDL字符串带有主键EmpTest_PK、备用键EmpTest_AK1和检查约束EmpTest_CK1。它建立了两个唯一的索引来支持主键和备用键约束。这不是你需要的结果:你需要一个表,但是为了加快数据载入速度,它不要包含约束和索引。只有在数据载入工作完成以后,你才建立索引和约束。7 _, t* ~' S8 S" A0 }
6 [$ ]% F- V' f' u4 V
  保证对象的定义独立的另外一个原因在于灵活性:你可能需要改变对象建立的次序。 % c( v  K! t' @" N, a1 Y9 o
/ v& B& @5 o9 n; i! o( |
  现在可以设计一个数据结构来存储对象的元数据了。
* O/ |, X4 W6 j- [) i" s: L- ~4 g% {; N+ v
  元数据存储器:MetaDataPkg程序包规范: R9 X  Y! c, @, {+ n, w5 Q
6 p- d. _0 @2 Q, H1 t$ P
  首先,你必须建立记录类型来存储独立的对象(例如数据表、索引等)的所有必要信息:
1 {. [8 g: H0 ]. _& h5 Q) b; l
  O, S' |5 E% [8 M) ~. G' csubtype tString is varchar2(30);6 l& l& q- s8 _% P7 t8 M
subtype tDBString is varchar2(255);: ?: m& Q8 b& m; F3 w& N
subtype tDBLongString is varchar2(4000);  S) X; ^3 l! V! W, {6 o$ A$ P) I! {
subtype tLongString is varchar2(32767);
2 g% Z0 [6 i# L) F2 A( Atype tArrayLongString is table of tLongString
/ A7 W8 i$ D2 A4 e: S# X0 rindex by pls_integer;
( m5 j, I# F" Dtype tMetaObject is record
  y" C% G# i- {* d. y(/ i1 j% t" f( X% f! J3 ?! I' l; J
aName tString,8 i3 }/ q) y! E( A
aType tString,
6 M3 Y8 `% R. ^8 d  `; G9 E2 }aLogging tString,* M- t4 w! [/ W7 |4 M- Q2 E8 g. }0 j/ }
aParallel tString,
% s, W' j9 B2 ^' H# F. AaStatus tString,- ?; h6 U- [2 a9 F1 V! t
aValidated tString,5 @2 N. B; v4 O* b" `' i# N3 l" T
aRely tString,
' n0 F* j0 R; B; C. ^aDDLString tLongString
& l# q1 u) Q4 |" |$ ~( A);. E$ M7 X9 K6 U( O- [
  tMetaObject属性保存了下面一些信息:
( ]& q7 E, T' j/ u/ K/ b  p3 O- p* T) h
  · aName:对象的名称,例如EMPTEST_PK1。
7 c4 |) F6 j: Q2 J: V& J# h7 W3 x
  · aType:对象的类型,例如’YES’ (分区的)/’NO’ (分区的) (用于表)、 ’UNIQUE’/’NONUNIQUE’ (用于索引)、 约束类型 ’P’/’U’/’C’/’R’ (用于约束)。
4 W! w5 s% e0 X& d1 B5 r# A
! D  [) J: i) Q* ]  · aLogging:对象的日志选项,例如’LOGGING’/ ’NOLOGGING’ (用于表和索引)。
2 d# g& Y9 C0 m9 l7 b& T
. Z8 q) D- a/ G/ X- z: B  · aParallel: 对象的平行程度(用于表和索引)。
5 ?" |: ~. z( e/ S) i8 X1 @9 s" t4 U0 g; ~2 \( P
  · AStatus:对象的状态,例如’VALID’/’UNUSABLE’ 用于索引、 ’Y’ (备份了)/’N’ (未备份)用于表。
$ P3 s' w, D" l
* Q5 W, @9 s5 ^# k/ I) S9 V/ [, U  · AValidated:对象的验证选项,例如’VALIDATED’/’NOT VALIDATED’(用于约束)。9 N2 ]6 b' G, w* c5 D  ?8 D
6 s9 X  b6 q" F  L( [% {- G
  · ARely:对象的依赖选项,例如’RELY’/’NORELY’ (用于约束)。, t( P5 _4 ~4 {2 z7 X
9 y' d) |. c! \) a' n  R, {
  · ADDLString:对象的定义SQL字符串。$ C" }/ X5 [, E; B+ J

# R* W% B/ M' Q! g( Z5 n! i' ?  现在你必须定义一个相关的数组类型,它能够列举出某种类型的对象,从保存tMetaObject类型的多个对象,例如,所有的EmpTest索引:- Q# a5 p+ l& k, ~* T3 O1 a
0 L( j$ t1 \; @1 `5 r( G! o5 {- Z* v
type tArrayMetaObject is table of tMetaObject
  g/ B9 H8 O' N2 @1 ]: v9 Q& b) \* {index by pls_integer;! w$ Y8 d+ e% g# ^% x9 r4 n% Q, Y! q
  下一步需要建立一个记录类型,它包含了数据表表自身(aTable)的tMetaObject属性和三个tArrayMetaObject属性:一个用于索引(aIndexes),一个用于约束(aConstraints),一个用于触发器(aTriggers):0 b9 X- |4 ~- t3 _
6 Z+ |  B! K. t8 U
type tFullMetaObject is record( X) q+ l% Y; c: \$ ^7 ?6 W
(. n* ?! _+ i1 V* o2 \( ^
aTable tMetaObject,8 b4 Z6 b5 t' e' d3 }6 {
aIndexes tArrayMetaObject,  r# v$ t2 a( L! N+ N  m1 E
aConstraints tArrayMetaObject,, g, S5 F" P1 N+ c8 N9 Q5 P
aTriggers tArrayMetaObject. Q" `5 _% M4 X2 U9 x
);
. R, f: S0 S& A# j) N  tFullMetaObject对象类型保存了单个表的全部对象的元数据。最后,位于顶层的类型是tFullMetaObject数组。TarrayFullMetaObjectByString类型是tFullMetaObject的一个表,索引类型是varchar2(30)。
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:21 | 显示全部楼层

Oracle中提取和存储数据库对象的DDL

列表2:MetaDataPkg程序包规范
) j: G0 B; |" S% T( O& n, h) S7 H# @; J$ d7 K+ i4 u0 w$ ]# X
  用如下的方式建立或更新MetaDataPkg:
) d0 Y- V* e" ^0 ~) ?
6 X; I" @' n, T& C! ^cEnabled constant char(7) := ’ENABLED’;
0 O1 i4 h2 b9 x7 ^& DcDisabled constant char(8) := ’DISABLED’;5 z3 y5 P( j5 F/ g* \
cUsable constant char(6) := ’USABLE’;% a8 o# J" l4 x% K: N
cUnusable constant char(8) := ’UNUSABLE’;
1 k' T! q; I6 h8 a2 vcValid constant char(5) := ’VALID’;
$ v( K" \1 d8 lcInvalid constant char(7) := ’INVALID’;
& \# J# `+ ^. s2 f# `6 |/ R8 R. i4 e
cTable constant char(5) := ’TABLE’;  b1 e8 m) w/ B
cView constant char(4) := ’VIEW’;5 b& z) v# r  x8 e
cIndex constant char(5) := ’INDEX’;
  W8 y' @: O4 ^% PcConstraint constant char(10) := ’CONSTRAINT’;
+ t* X& q! ?& L( \( xcTrigger constant char(7) := ’TRIGGER’;
% ]' c+ `5 t0 J! l2 ~  W& y0 G/ W/ qcLobType constant char(3) := ’LOB’;
$ ?8 e2 o5 T  B2 g) R' I3 u! ecClobType constant char(4) := ’CLOB’;
) x" ?6 q( Z: J' g% vcBlobType constant char(4) := ’BLOB’;
, U+ v1 C0 J* P* U. AcPackage constant char(7) := ’PACKAGE’;' i4 K2 z. D9 W3 w
cPackageBody constant char(12) := ’PACKAGE BODY’;
) k2 a3 h! z4 M' W/ W: W0 [4 s2 hcProcedure constant char(9) := ’PROCEDURE’;
1 w+ c' P- f3 s% U4 D1 k; ucFunction constant char(8) := ’FUNCTION’;
: g; j0 X3 L7 @cSequence constant char(8) := ’SEQUENCE’;; Y6 j: j* C9 S4 l' m+ U1 C4 T
cSynonym constant char(7) := ’SYNONYM’;
7 P+ P: f& t/ o* c8 ]9 H; _: i0 [cType constant char(4) := ’TYPE’;
" J: X1 o8 c! j: d* [& pcColumn constant char(6) := ’COLUMN’;" N* i0 Q8 q, J, ]1 H
cJavaSource constant char(11) := ’Java SOURCE’;
& F, K) e# S  v- tcJavaClass constant char(10) := ’Java CLASS’;
' `6 a! h1 j  o2 O* g
% n' i3 a2 Y3 E4 N5 |; I' |) ZcYes constant char(3) := ’YES’;
# m4 v$ T# @- p# N1 @6 H2 QcNo constant char(2) := ’NO’;
: ]3 g1 }/ A0 C6 Z1 L- W
+ f1 f- @3 O; t2 V. zcPKConsType constant char(1) := ’P’;  P2 g2 P, W) J9 u2 d! C
cUNConsType constant char(1) := ’U’;
, ?. p% @' M% e! o: y3 T* f* ocFKConsType constant char(1) := ’R’;" Q: j+ r: M( y8 C  D
cCKConsType constant char(1) := ’C’;
$ i$ U) A( J. g& v- j* }* z# d* b) Z! j# H# c% O" \6 g; P
cDropStorage constant char(12) := ’DROP STORAGE’;! z8 r+ i, e( K1 g5 U9 C% R( o
cReuseStorage constant char(13) := ’REUSE STORAGE’;% p* i; n" I8 p: v# _# Y6 N
cCascade constant char(19) := ’CASCADE CONSTRAINTS’;$ g" Z9 r- U( U& A* C) R
cNoCascade constant char(10) := ’NO CASCADE’;
+ C0 L( o8 j4 UcEnable constant char(6) := ’ENABLE’;, j& C  e, H3 ~7 G/ S' f
cNovalidate constant char(10) := ’NOVALIDATE’;. ~! W8 R# u0 D' K
cRely constant char(4) := ’RELY’;: x. s/ @" C! e4 \
cNoRely constant char(6) := ’NORELY’;
: @3 v( ~. o- M0 G5 C3 ?cValidated constant char(9) := ’VALIDATED’;
; a" m0 K% d1 g) S$ K2 @) ^) ncNotValidated constant char(13) := ’NOT VALIDATED’;
3 X9 f1 {0 ?! o" icLogging constant char(7) := ’LOGGING’;
( j% n4 Z( b4 }+ ~cNoLogging constant char(9) := ’NOLOGGING’;6 Q$ Q+ r# E/ c/ m
cParallel constant char(8) := ’PARALLEL’;
! j, V! y0 ]# scNoParallel constant char(10) := ’NOPARALLEL’;
/ o# m: h1 I( W4 N: dcNull constant char(4) := ’NULL’;* H: i  g4 N' H6 q; E; k
cNotNull constant char(8) := ’NOT NULL’;
% T$ B' c( b/ b% o" D" F1 r2 a7 pcDefault constant char(7) := ’DEFAULT’;
7 V; I  b# f! u+ L- z* }
- ]$ l) c  W1 @$ u4 Y( hcSYSPrefix constant char(4) := ’SYS_’;
4 W- f: M% p0 y$ ?: K& n! V& C/ EcDoubleQuote constant char(1) := ’"’; ) U( H% s% t+ h
& B3 ~, Z3 c/ L0 Q) h" U1 ?
subtype tString is varchar2(30);
* y- V) k) p- p$ M' J7 t. ~subtype tDBString is varchar2(255);
8 E, x0 X; w( g9 T0 l5 M- I; Psubtype tDBLongString is varchar2(4000);
$ U/ t& ]4 N3 I) Z/ w: N# g  Lsubtype tLongString is varchar2(32767);
( H4 U$ m8 Y+ Y: Z9 @  y  X) \7 @3 C# e* b7 [4 O0 h
type tArrayLongString is table of tLongString
, g0 v2 k7 b: iindex by pls_integer;$ R; \; t+ ^( m+ V+ i

( p% N7 z' J. X" B0 Ztype tMetaObject is record+ @% `8 e, V8 z3 ~* G
(3 _5 h3 T6 z3 [
 aName tString,
/ r" w1 t' {0 g. i aType tString,
; D. l1 k2 X6 P- L7 A$ y  ? aLogging tString,
- A% Y+ c3 ]2 {$ X/ C  t+ s4 U- z8 q aParallel tString,7 a4 ]' T1 c& _; P
 aStatus tString,) H" |  u, B3 z- ]" Z: F- H
 aValidated tString,3 ?' V* S! W) X0 C1 a5 V/ y7 F- L  Q
 aRely tString,
- [* ?6 S5 W' i; L9 C" S: J aDDLString tLongString4 a+ Q$ L+ i) f% G9 o
);% Q- C6 f/ _! |" Q+ p2 ]. E
% Y0 e' W! F3 ?8 P/ ?& y
type tArrayMetaObject is table of tMetaObject
8 `! x+ t1 e* N! i7 Q( ^! e1 `index by pls_integer;) G8 C" o2 y' L

% z3 i" w6 v, m! O. b! _0 T2 |type tFullMetaObject is record
, N  e0 i. L' y% h, i(
' k1 H3 j; U5 s& q) J; F: s8 p* S aTable tMetaObject,' g. e, F6 c! g% p2 y8 ?% }2 B% I2 n
 aIndexes tArrayMetaObject,7 \9 r5 D7 ], c$ I- C
 aConstraints tArrayMetaObject,
) j+ ~2 O  N# n% W; \5 W) K2 b$ c aTriggers tArrayMetaObject
& D! a4 g% {& \9 E);
) m, n. b# }# _8 c! ~/ y8 N
  p' w5 n6 Q3 v, m7 }3 @type tArrayFullMetaObjectByString is table of tFullMetaObject  Q  x! @0 y; y% m! D4 O; X
index by varchar2(30); 5 e7 a0 A* {' p3 q( ^' ^

- s/ Z5 u& r, N* K* n; c$ jprocedure Load# z+ v3 ?: ?0 |( C
(
; z5 m4 `( B* b% c3 w1 N pTable in tString,
) L8 x6 G& c& k' R9 j pForce in boolean := false/ E+ N6 x+ p( I, ]1 h
);4 O' f9 O. o- H4 q- {! G

( D/ n& n' B+ X( Pprocedure Reset
3 K9 L/ d) C4 H7 z1 f% s8 q(9 l7 J, P$ Y& c2 y
 pTable in tString
- K2 y0 u6 a9 C+ X, B* n- d! u);
' ^9 p" V: |" T4 K; w' S6 C& z* D+ D! V1 `- J) F
procedure Reset;
6 S, w* M' |% J5 B8 ~
/ k5 d( Y6 t" vfunction GetMeta
' h4 H$ J2 ?, E% A(
6 _; m$ q9 S4 X: `6 U3 W- ~. Q1 a+ X8 E pTable in tString,
( n0 W5 l* r* K" }6 B; Q. Y6 U: j( y pForce in boolean := false
. f; j/ D: f2 Y: t" ]8 n- L: t1 ])( {4 F- V! h  ], |9 l6 l. J
return tFullMetaObject;+ I2 U* y/ z, S8 J5 \+ }; U

. `- S9 Q9 i  a/ R! Y% s$ Tfunction GetMeta. {/ s0 o1 G' s( I$ K
return tArrayFullMetaObjectByString;( ]8 p7 [# \' R2 I8 w& k

6 ^! q) a+ S  E, @procedure SetMeta) Q/ O, b! N8 H4 t; n* x2 b
(- {. m1 X$ y! ], p% e; v* S) Y' g
 pTable in tString," Z5 q  i" d0 }8 x( ^' u1 R3 z
 pFullMetaObject in tFullMetaObject, P$ W- @7 y! }( c3 n4 M
);  i5 X1 B6 u6 F- I6 J3 [3 E6 B

4 c9 K6 p% G  ]( I2 @+ oprocedure SetMeta4 J7 q* P6 j8 J4 N8 G
(
) d+ |2 |( ^/ G5 e pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
& ?. K2 Z2 D8 a# O! \5 L5 z);% P# @: \) g& y) [  D/ [5 L
3 h7 k. `. [+ q1 ^* J- h2 P! ?# U6 y
procedure Show
8 w8 B& N" U/ h9 g; m(. U1 W$ M: |4 i/ f
 pTable in tString
: w2 \+ P8 p' ~1 C);0 U2 d* b# E4 T

# [) [' X* i7 q' _procedure Show;
& @" ], M+ g9 C! send MetaDataPkg;
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:22 | 显示全部楼层

Oracle中提取和存储数据库对象的DDL

上面的类型对象是作为元数据存储器的,用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中(列表2所示)。我还介绍了下面一些API:. K, R4 _) |8 a8 G; C( K& G1 A
' c) p! i: K& Q! a, q1 [. B' v
  · MetaDataPkg.Load()过程:把特定表的元数据信息载入存储器中。
+ m. ~8 U7 l) m8 h1 z, ?/ j: V
- r% q, V5 W) c' {  P( \2 u  · MetaDataPkg.GetMeta()函数:它从存储器中检索tFullMetaObject类型的对象。
1 ?$ s3 j8 n7 s& `2 z% |6 \; i0 k  S* p2 I2 P  @
  · MetaDataPkg.SetMeta()过程(重载的):把对象的元数据存储到存储器中。 ! W; J% t. ^( g$ Q; J4 C

) P2 z. e$ ?3 w) v. b  · MetaDataPkg.Reset过程(重载的):对存储器复位。 ; u' X% [' x( t5 b9 v' w' [* |/ @
' ?2 T% a* y" R/ C3 V, j- N3 E
  · MetaDataPkg.Show过程(重载的):显示存储器的内容。/ f% S6 o/ o2 L. e8 i. {8 T  ~
, ?* C& K. J! E  i4 _1 q  ]
  实现所有这些事务的代码4 _  H5 _9 V  O+ N$ v1 [+ U
/ D9 D* f, w/ P7 b% V- p
  列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程,因此在每个对话中它都只执行一次,符合你的需求(你希望在开头设置一次)。程序包提供了用于设置环境参数的API:DBMS_METADATA.SET_TRANSFORM_PARAM()过程。2 {1 M& H9 Y& r

" i, L0 p# d' ^; Q6 a4 q  列表3:MetaDataPkg程序包主体
6 i8 _7 J: j6 o( U7 m
' n4 z- K$ a5 f$ n2 K" mvMetaData tArrayFullMetaObjectByString;5 d( G# u  T. H, N7 h2 Y7 v
) E6 H* H0 J7 W* g) d
procedure SetEnvironment
5 F9 c: Z+ D" x  _& i. Nis
# C( A2 x3 g1 m9 }begin" `3 T$ y9 Y  Y# ~5 p
 dbms_metadata.set_transform_param(
5 i) k  Q& ~# ]+ K+ \& ~  dbms_metadata.session_transform, ’PRETTY’, false);/ B3 A8 y4 x5 L1 }( c9 u, K3 D
 dbms_metadata.SET_TRANSFORM_PARAM(
' P" ]- |! D1 A. V2 q: t( d) s  dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);% E9 X( [! @+ s, A6 A8 ^6 G
 dbms_metadata.set_transform_param(
1 U+ |$ K! [4 Q  dbms_metadata.session_transform, ’STORAGE’, true);% b& }' u3 H5 r+ u
 dbms_metadata.set_transform_param(( ]" s& r  ^3 B4 k
  dbms_metadata.session_transform, ’TABLESPACE’, true); ) J. Q& Q% X8 W- N! p* v7 F* P2 M
 dbms_metadata.set_transform_param(; w# n/ G9 |7 ~+ C
  dbms_metadata.session_transform, ’CONSTRAINTS’, false);
" Z8 B$ K& ?% e9 V) s dbms_metadata.set_transform_param(* \9 I' e5 M  x- i! r& u8 t; ]0 N% g
  dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
/ o1 \7 G  `' G9 @& l3 H dbms_metadata.set_transform_param(2 J3 U! J$ c0 D1 `2 o
  dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);. Z% W6 [" U8 V% |

; L( R! b; K3 |9 P6 i  h1 Mend SetEnvironment;% @8 n: W) G6 w! y, g+ N

, q2 i; v5 l% Vprocedure Print) W5 S; ^# O- C: j- ^& e
(, q: e& @; _8 S3 D2 ]7 X
 pString varchar2,; l& @. a1 r  v/ L: R, }$ @7 Q
 pLineSize positive := 80
0 u* b- Z1 @" w' _)
2 q$ e( `- S6 |. e0 Mis
. g9 v) c1 p& ]* U
; w5 A# y6 r8 e8 UvLineSize pls_integer := least(nvl(pLineSize, 80), 255);
; a( s# a& J5 x: ]& s4 r% X2 q5 n: z, b: T! r9 u# _
begin
% d; m0 I& k9 Y$ X6 t dbms_output.enable(1000000);% W1 j: F* |: }. S, D( `$ m9 s

' @' p' ]  V+ ?, o  A2 x: \ if (length(pString) > vLineSize)5 F: R* k, v- c/ T- s
 then
& ^* U  F# b( C# o; K' S+ z$ {, ?  dbms_output.put_line(substr(pString, 1, vLineSize));* E  N/ @/ B" I3 q* T5 _4 w3 p" h
  Print(substr(pString, pLineSize + 1), vLineSize);/ P# M2 h; I0 n9 j# M
 else! S2 Q. m- T6 V1 h
  dbms_output.put_line(pString);" k! z8 O! L; A6 N' w, U; a
 end if;
) B8 ]* Q! k- \5 Q: D9 Z8 |+ G( ]: B3 V5 E  d6 S, P8 H
end Print;3 Z5 q5 H  s0 K) u
- ?9 R3 S' W+ A" f) W+ i
procedure Show
% O. S; q, c0 J' w0 c/ G- W(
+ ]5 p8 s7 T2 o pMetaObject in tMetaObject
6 G2 j6 M- e7 N2 i9 _)3 j% _6 b$ }# Y5 m. ?* {# b1 C
is
9 K+ n# k0 p# ]2 q$ |begin
; ^8 T1 t6 I- {2 Z& l7 P; W5 `dbms_output.put_line(’***’);9 y: S  v# y- v
dbms_output.put_line(’Name: ’ || pMetaObject.aName);
9 n) z: X7 t" i+ }+ _/ R# Kdbms_output.put_line(’Type: ’ || pMetaObject.aType);
% y7 }+ U7 y. n# q  ^8 odbms_output.put_line(’Logging: ’ || pMetaObject.aLogging);
6 J9 I% _8 w2 V! k3 Z+ Mdbms_output.put_line(’Parallel: ’ ||6 \  m2 i# H8 d7 C
to_char(pMetaObject.aParallel));
- G+ B1 c/ d, Q; E3 ]dbms_output.put_line(’Status: ’ || pMetaObject.aStatus);
2 d  x: D6 y; [dbms_output.put_line(’Validated: ’ ||
+ z! c+ f  L. R* qpMetaObject.aValidated);
0 f: R$ t& ~( C* _3 n4 U3 ]$ bdbms_output.put_line(’Rely: ’ || pMetaObject.aRely);
3 ]" w  K; {( r* p6 ]' g! w( ~print(’DDL String: ’ || pMetaObject.aDDLString, 255);; ]6 ~7 X( `5 Q! f
dbms_output.put_line(’***’);
) c# O0 ]0 K, Pend Show;8 K: b! l4 n4 H) Q

% g, r2 T8 [) C- b! cfunction GetDDL' P& l+ |" W7 p& _0 o9 p
(! p/ m* m/ r# \2 f5 A. H3 ^0 s
 pName in tString,
  k& N) M/ P7 D9 p/ e6 H0 { pType in tString
: v) t6 ~$ n% a)
4 O) `7 d0 P' R1 b' [" ?' Ereturn tLongString2 ?4 {% ^( f1 m+ Y
is
# v0 l' K, O4 t- zvClob clob;+ W; O9 B+ V7 O# I* r
- u, [5 Z6 q6 _3 z% `* W
vLongStrings tArrayLongString;. G8 [( b* S5 A0 D

7 S) L) E# a7 D  I4 QvFullLength pls_integer := 0;$ M% B5 i( d' }% ^: a0 ?
vOffSet pls_integer := 0;
- @3 S# N0 M+ j! I! S; ~7 F) o% q  v: _vLength pls_integer := 0;
9 _8 `# p, W: y# e6 x6 ^0 \# c  g% [: G% b! N7 w
begin
0 B2 n/ Z/ G6 N! \/ i' A% k" E) \! M. N) K* {$ g
vClob := dbms_metadata.get_ddl(pType, upper(pName));
3 U# n% U$ R( X/ R5 g
' N3 w$ q& k% X6 Q- ^, xvFullLength := dbms_lob.GetLength(vClob);$ f: I% s; v9 l5 P# N; I
0 R: h. v+ g1 y2 B
for nIndex in 1..ceil(vFullLength / 32767)
) u# @" C1 x) N- |loop
$ j' A; H& Y; O2 J# A vOffSet := vLength + 1;% G. J: C- A: @9 s, c
 vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);* _9 @* ]) J2 D- ]% f/ k8 k
. T/ O+ \; H+ _2 `
 dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex));8 o% L1 t5 ]& p' a8 w* e
9 e2 `$ ^& x; I+ T4 T7 \, k
 vLongStrings(nIndex) := replace(vLongStrings(nIndex),. f4 U" C8 X4 O, D0 J( K
    cDoubleQuote || user || cDoubleQuote || ’.’,6 }1 y4 m. C# s- H9 Q" j# f  l
    ’’);0 X: a+ x: S" M$ R  H- ]. x! b
5 |6 T3 V! I4 \" d7 C$ s8 N
 vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ’’)));
+ p4 Y4 _7 K. Fend loop;
/ O; x) X6 }1 t2 Z% L% D" G& ^0 L/ v3 S- E  ]. u3 @# f# h" C- o
if (dbms_lob.isOpen(vClob) > 0)
$ l- B6 i& W" H% K# L7 N! e+ cthen
2 K: `: G; c4 A. P6 Z dbms_lob.close(vClob);
% v- C% h8 h5 I) w  v6 Jend if;( d' i" X! ?0 o# O5 y
/ V; |$ [8 w+ _; C
return vLongStrings(1);5 p4 K1 e4 Q  [5 g7 ^

/ Y, |/ L: a) l$ o! r8 s7 g, `end GetDDL;* }/ q" Z) x0 l" e# J5 O

9 J; Y5 l1 v  w/ hfunction ObjectExists$ |2 v# ]$ |$ ~  P$ n) T9 R$ T6 q$ O
(
1 n) ]& S; y  P* { pObjectName in tString,
3 [5 `; j4 A4 ?2 t, \+ [ pObjectType in tString,
% Z% o: G# y4 p$ g8 T& ? pTableName in tString := null" }3 }, |0 i8 z$ a" r. u2 n
)
( D* r# P* `4 b; g( Q( Y* a3 Greturn boolean4 @6 T3 d  y8 T3 g8 S3 |
is9 c) D( V0 @! E
) U7 E" A- n4 J; T& s0 ]
vCount pls_integer := 0;
' ?, N7 a* S( _6 W% GvObjectName tString := ltrim(rtrim(pObjectName));* @, b- x8 H5 l
vObjectType tString := upper(ltrim(rtrim(pObjectType)));
0 M  Y, T8 o  I5 z- _vTableName tString := upper(ltrim(rtrim(pTableName)));
# n# x+ r4 S& r0 G& w" T1 a) Q. l  a8 D6 ~8 \
begin4 p4 W& F& X5 e7 f0 I2 [  R
: B$ s3 T& u' a% ~* ~
case
, r! }. D9 f  t" `% k2 D# P when vObjectType = cColumn3 ~$ G9 }8 j! O) |; Q
 then
% w) |6 T. O( r. l" ` select count(*)
$ @+ l+ N' n1 H8 R9 o% p: {( H  into vCount
$ U9 n  A; R/ M, x% I. K  from Dual
+ ^4 X1 R+ M( Z* k1 P  where exists (select ’1’
5 c) i+ i! Y/ b1 Z- ?  V  from user_tab_columns% y# V. J* R; j1 P- v4 B
  where column_name = upper(vObjectName)7 n! ~9 J2 i' o$ \1 J
  and table_name = vTableName);
' L- N: x- |' f, V8 \
% g( U" ~* G* [% _5 T& C. z when vObjectType = cConstraint
3 t! Y! {+ V( J# n4 v% W% G then: V- B3 }( q3 b; y; Z% `
  select count(*), g. _  q3 o9 U& w& z
  into vCount
0 r# p0 L- a9 a  from Dual
9 O# l  h  M, [  where exists (select ’1’2 \) v- ]8 k6 R
  from user_constraints
, s) Q8 S0 x9 b% _0 r8 b' X  where constraint_name = upper(vObjectName)
, H# P' j) B9 }! _% ]; _, c  and table_name = vTableName);
8 w4 ]# p6 ^9 g
$ T& [1 j4 m$ b7 A6 t. swhen vObjectType in (cJavaSource, cJavaClass)2 B; B2 b7 y! }
then
5 P/ `+ j& U9 U* \& ?9 ^2 h. w" {" }( p select count(*)
; ]/ P- q" _" M- ` into vCount
; ?; {  _! Y2 w6 o$ a from Dual
' h3 j) M1 j* g8 q+ B; Z5 s: w where exists (select ’1’( ?+ c2 T4 M, b' |# W
 from user_objects+ k6 X* l/ ~1 y+ y9 P2 \
 where object_name = vObjectName and object_type = vObjectType);% f3 I8 T# ]" K

9 }( Z1 ~7 q; t( j) ^ else; J' E8 N4 r0 Z! C/ ^8 V* J
  select count(*)7 N" G# Q7 s& g" g
  into vCount; p- `# m9 v" k
  from Dual& r* _' m* \# s; q; q) S$ B# M, U
  where exists (select ’1’
& `7 O; D% Y/ I  from user_objects) n; O5 ], i" a+ Q3 w
  where object_name = upper(pObjectName)
  b* {" D- C4 W, L4 |" c  and object_type = vObjectType);) T6 \5 a4 x! X4 \$ ?% Y
 end case;2 W, t5 A- ?8 |# j0 n: P
' E' I4 o3 {3 x- X$ N/ y' @
return (vCount > 0);
. Z3 O( R8 Y  W) I
! X% y" E- ?) V9 V% j2 O9 {* vend ObjectExists;
4 w8 T  y+ W6 P( G* A4 @) D: ?  p' v
procedure Load
9 J9 f# E8 r- x: X0 K! N3 k( A$ `* Z(
. I/ H8 N3 y/ w* D+ X+ U pTable in tString,
6 Z5 F8 h* L1 D' P, L pForce in boolean := false* D7 q  |) U9 x9 ^5 J( `, e
)* r2 H. G3 R6 L" C1 G9 L
is
2 k" o* D- m/ f; ?/ v( {. n" }& `4 c
vFullMetaObject tFullMetaObject;6 g  N6 ]8 _$ n6 u0 a% S/ W+ R

8 `% ~) r; ^) X5 o4 q& c0 c- EvTable tString := upper(ltrim(rtrim(pTable)));$ J3 M5 O. m7 G" m6 C7 U" d" q
vCount pls_integer := 0;
. @" u. N' N0 ^* d, e" W; C5 ]% n* y% ^' C+ b# f$ c$ P" k
begin$ n# T5 a7 j; c( m% g$ J
) u; u$ i  r5 \# R6 K  q
if (not vMetaData.exists(vTable) or nvl(pForce, false))
! M1 R; k/ U: E then/ y* ~* ~" H0 M6 N
 if not ObjectExists(pTable, cTable)1 S* Q! L/ F- N. t4 `
 then
( ^% K/ j2 q1 |. A$ Z2 K8 u! e  raise_application_error(-20500,# ~. ]  X& u4 L" w* K# H  p+ S! O0 K
      ’Unable to load metadata for ’ || nvl(pTable, ’NULL’) ||
& O4 }4 r  I- ]' `# W" a9 N* N, y      ’. ’ || ’Table does not exist.’' A4 W: x/ ]+ _4 L7 m
  );
! w1 n6 n- w' _. wend if;
; x4 ~/ G; M( d" X4 H3 B
$ w" l- c) j/ r( X/ Zfor rec in (select table_name,: q0 l. s0 D# I+ X& Z: L/ i0 a
logging,0 u! s* g) Q+ y
ltrim(rtrim(degree)) as degree,$ ~& n* \/ _# x  z+ v1 V* T" z
partitioned,
# n  s% U: `0 K! E; t4 [; v$ [" ebacked_up
0 g4 G( h) F2 K5 t% d# ]& d7 T) Ofrom user_tables
1 Q! Q2 z/ Z1 c" W/ C0 Vwhere table_name = vTable)) U6 F2 s+ q  O# X% {* n
loop
# I& u, t+ q. h
8 {2 \( D# F8 F0 j2 C3 N0 O vFullMetaObject.aTable.aName := rec.table_name;
! m7 L' @9 q3 m6 e% z& Y4 B vFullMetaObject.aTable.aType := rec.partitioned;$ K  n& E2 q# {  j+ i# k
 vFullMetaObject.aTable.aLogging := rec.logging;
! I0 v  V. g' |& R$ R9 N vFullMetaObject.aTable.aParallel := ltrim(rtrim(rec.degree));- G6 h) z4 n0 `. K  z
 vFullMetaObject.aTable.aStatus := rec.backed_up; 6 K  L, M# D& G$ e! F1 O0 u
vFullMetaObject.aTable.aDDLString := GetDDL(rec.table_name, cTable);; U- B  T- ?% ?  Y( Z7 f; [9 a
end loop;
' g5 o6 r8 U9 V; L0 Z& J8 B4 |: Q( F2 h. j
for rec in (select index_name,, K' W4 V! z: j2 V
uniqueness,
4 l9 S" x+ @' [$ u; Xlogging,1 N& j# Z1 e% G* l. {; b
ltrim(rtrim(degree)) as degree,
/ W2 t+ p+ Y% U' jstatus5 P9 }. g! N2 l2 N' A/ }
from user_indexes4 a: s2 R* P7 D5 e6 Z
where table_name = vTable
$ Y! G3 e2 t) H* ]and index_type != cLobType)
7 e+ z. a9 g0 K- ]" r+ p  G- h2 D7 cloop4 ~% i8 H; ]; `3 V0 F, u7 c' u

* k5 }) ]0 l; q& PvCount := vCount + 1;/ I- U% J& h/ m/ V! }; E
vFullMetaObject.aIndexes(vCount).aName := rec.index_name;1 ?0 [2 h% N; Z8 S! s
vFullMetaObject.aIndexes(vCount).aType := rec.uniqueness;( ?# T, [3 U6 z6 M
vFullMetaObject.aIndexes(vCount).aLogging := rec.logging;: `5 v$ t5 G  d3 Q
vFullMetaObject.aIndexes(vCount).aParallel :=
& z, E  {' G0 ^* ~ltrim(rtrim(rec.degree));- i% L5 u0 W3 M7 z: j0 h* a4 L
vFullMetaObject.aIndexes(vCount).aStatus := rec.status;+ z: @: r) g, \5 c6 ~( b* r
vFullMetaObject.aIndexes(vCount).aDDLString :=
! |* W, q' [$ |7 Z& c3 BGetDDL(rec.index_name, cIndex);
& X. v% A& y6 V9 ~, |0 t7 L) u" a6 W2 V
end loop;
  r; T$ ^/ y% ^* W6 a/ X0 v
0 R0 _8 S& Y  N6 d) L/ uvCount := 0;& X- a4 l6 O9 g. y
for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from       user_constraints where table_name = vTable" G4 |: h3 D% j+ g; ^$ y2 O
order by decode(constraint_type,* C) ?0 K% Y' @) u- G
   cPKConsType, 10,
6 Z" `$ W6 i$ E. F! z7 _   cUNConsType, 20,
' T: a- v9 K, J' {, o! I) c1 N. _   cFKConsType, 30,) E3 n  E5 t" Z  @: g7 p
   cCKConsType, 40,
" u+ P: ^" U2 x2 N+ r   100),- g) k* P& L8 s# F7 D
constraint_name)3 C' G  q6 M0 }9 I( m0 z& I
loop * p& k! Y4 n/ R: y' ^, ?
 vCount := vCount + 1;4 z, S! L) Z' r, f
 vFullMetaObject.aConstraints(vCount).aName := rec.constraint_name;
, `& t: @* I0 t6 B8 j vFullMetaObject.aConstraints(vCount).aType := rec.constraint_type;
( ^1 w" Q- O* C' ]0 d  Z! L vFullMetaObject.aConstraints(vCount).aLogging := null;9 j9 [5 g+ o1 b0 x% j- d
 vFullMetaObject.aConstraints(vCount).aParallel := null;2 `' _7 x9 F1 A; ^* n; _3 o. U
 vFullMetaObject.aConstraints(vCount).aStatus := rec.status;5 M6 E- v0 o# _
 vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;
+ J- G+ G5 }  `3 e) X7 V' [ vFullMetaObject.aConstraints(vCount).aRely := rec.rely;
6 v5 t) v! J( S( @" D/ K5 [. n. m# C( y! Z( q; o, u7 Q- L$ `
 if substr(rec.constraint_name, 1, length(cSYSPrefix)) = cSYSPrefix and; [3 L! @0 ]+ T5 V; I
      upper(rec.search_condition) like ’%IS ’ || cNotNull || ’%’
) x+ a+ a" H3 K# ] then. ~/ B) t% \0 k8 s# `, D
  vFullMetaObject.aConstraints(vCount).aDDLString :=
) v$ j: N6 J4 ?- }! E% ?     ’ALTER TABLE ’ || cDoubleQuote || vFullMetaObject.aTable.aName || cDoubleQuote || ’ ’ ||
/ K, z8 S1 `1 G: \  q* d3 o     ’MODIFY ’ || replace(rec.search_condition, ’IS ’ || cNotNull, cNotNull) ||
* o. x8 A" [. D* m; o& E     (case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidated' n  G9 [# g( a. G1 J0 l
      then ’ ’ || cNovalidate# A% ?2 K" y  I
     else ’’end);% s5 D! N2 e9 g' r+ ~* K
 else
" K: {8 ^1 D' ?& B' Y6 q  vFullMetaObject.aConstraints(vCount).aDDLString := GetDDL(rec.constraint_name, cConstraint);- p, S' [' d# n- Z0 @
 end if;
" O2 T6 y3 e* s' i% l1 rend loop;, M: a" p) @0 \% X9 i: q

1 m1 C) D  `% P: N& Z/ J) |SetMeta(pTable, vFullMetaObject);
& m7 n9 R: \  Y( y! aend if;
4 f) `% @1 Q$ t+ T3 _+ |8 k8 Oend Load;
! G5 t3 z. W) p  f) M- N
5 L$ Z! u' `. u! \procedure Reset- A; D  Z/ o& S* {( }4 A6 M
(
* b5 Z: K7 T' r pTable in tString% j8 g  w7 h, z7 Q1 F& i! s1 r: E( _
)( f' [3 a) M& k5 G
is
: G. y0 e: i2 |1 Y5 |2 ybegin
3 c8 v3 d8 p5 A+ Z! O+ A' u- Q4 I5 W' `  G7 Z* T
vMetaData.delete(pTable);+ b5 I9 l* g0 y; R( g! K$ h

8 u  d1 H5 b5 ]end Reset;  c: ]" p: ]0 s# M6 f3 f& P
9 p% O# y5 R; j! H0 T
procedure Reset
) u: _* a5 A* bis
/ [" v+ g; w8 Y0 N  O0 zbegin. B' W* v( g8 l* T0 o( z% H
 vMetaData.delete;
- a1 N: W) [& Vend Reset;
5 {2 x3 A4 ~8 v# ]' Z
" s/ B% k. h8 }; Cfunction GetMeta
& C6 D# W, T9 Q& A9 h! V+ S' y(
. h- c* o$ [# y pTable in tString,
$ u& D  C( _6 b  l; J. r pForce in boolean := false: K3 X  r" L3 D: |3 a4 `8 u
)
/ W0 }% Z7 `5 i( {4 _$ c* @return tFullMetaObject
4 Q6 m! v9 C3 L0 g7 q5 |is2 \+ B+ |$ R% m/ V9 n/ T
begin
/ N/ h4 y2 [: n# `
2 [. h$ ^1 K) m4 s5 gif (not vMetaData.exists(pTable) or nvl(pForce, false))
3 V/ \& T8 o. s* S: v5 `& v% Uthen) D. n! I2 Z8 \
 Load(pTable, pForce);
; {. I  c: R' G3 ]; V  |9 V$ R' _, C& Q% e
 if not vMetaData.exists(pTable)
% Y! O# u/ e% @/ F' m) [ then! j# ?: p, h) n
  raise_application_error(-20501, ’Unable to find metadata for ’ || pTable || ’ in repository.’);: }, a  ^. M4 H
 end if;
8 |) O7 {" H4 [8 Dend if;
$ X  I5 F( s# [6 k9 ~7 g7 _1 i& _' C$ A4 {* m
return vMetaData(pTable);% d" t" j9 x/ P

4 j# |2 e& b* g9 a# ^! U/ Vend GetMeta;
  L. f- ]1 }+ ^( I) S" J
. \! C- c6 A+ ofunction GetMeta6 p% u0 J6 H6 r, _& X! {' E6 \
 return tArrayFullMetaObjectByString
# G- t( I4 V6 @5 r" Uis
% D# i2 ~8 o- Y/ g6 m, E0 \begin
2 \, {* R7 r4 i4 H* E& H2 B7 j$ A4 c. E; W) ?4 R  w  C$ I: a: j1 |
return vMetaData; 6 s! [9 U& v" k, m( s% L

* N! P2 v5 f+ Mend GetMeta;1 V* G5 m% U/ D; z" P. c- @

8 X* P( w/ [) `procedure SetMeta
  a3 `  J8 y5 p: v9 U9 F! r(1 B4 V1 `0 @0 X
 pTable in tString,
0 P0 k4 R4 \+ G0 D) g) c pFullMetaObject in tFullMetaObject# ^3 ^# x( q8 E( P2 D1 e6 o
)
6 U7 m' G( O3 |is# j, w% J; {9 _# o5 |: F( D( n
begin% O6 a7 n! u* E8 v; q4 Y" _' f1 U
7 O- j) h8 k1 o# l' y4 k
vMetaData(pTable) := pFullMetaObject;
1 y& Q% y% O# I2 H% ]( ~5 p  h. ^3 L0 [9 F5 n$ Z- j6 Q
end SetMeta;' g! l3 C. p+ C
8 P1 K. y: l3 e2 k5 K
procedure SetMeta
7 z& p, k. u! E7 i& f() r8 d$ Y; L9 ^  ^! W& i
 pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
8 x/ N- i% n1 X' n7 f! ^4 `* Q)
2 F4 ^/ o' i+ b; O4 k$ H6 M0 `is
' v- @5 Y! q2 N& E: w  y' c. p& A# |begin* ?% T3 _; f7 N- @) Z5 V+ e
3 x; ^  s" L. R$ c
vMetaData := pArrayFullMetaObjectByString;. Q) q5 g+ e* q$ J% ]
% R" W' m9 D- N, Q& o, D2 g
end SetMeta;' ]# B8 e2 p( f1 y

! E$ f6 F8 X( _5 w/ {# Z( G+ ]! qprocedure Show
3 m4 H8 k! j9 \% G(  m7 s$ k* `+ h  y: L
 pTable in tString
7 R! A1 L( I' [# B- @7 e)% ^4 \4 J1 X2 O& }
is5 w) o- O$ k" T% {6 N

. I. i/ ]6 M; UvFullMetaObject tFullMetaObject;' m/ q3 o+ ]( i# b$ k
0 V; \2 |" ~& L
begin0 `' }/ I( E) N3 q0 N* j

2 L' k( q6 u% [0 D0 H9 _& v; gif (vMetaData.exists(pTable))6 `$ O' j/ H; i/ K& {, ]$ Q6 L
then8 C" K# A% k/ D0 Z+ M
dbms_output.enable(1000000);6 v5 Z/ @& q9 j0 x& y, [
: B* L* a9 k" S: t
vFullMetaObject := vMetaData(pTable);7 L# t" }9 a+ [! s
dbms_output.put_line(’Start Full Object: ’ || pTable);  W5 Q! t& B6 |. A' A
dbms_output.put_line(’Start Table: ’ || pTable);
: o* i: N* \6 q6 u% Q5 S/ O! d' A) HShow(vFullMetaObject.aTable);
$ r7 z  \5 h9 ]  `, h$ H. @dbms_output.put_line(’Finish Table: ’ || pTable);. n& F/ v2 w6 ?% e. ?! Y
6 ^, I3 [2 S& ]4 Z( i: N( O
dbms_output.put_line(’Start Indexes: ’ || pTable);
* d/ k, R" m( r9 C: P- l& qif (vFullMetaObject.aIndexes.count > 0)
# `2 X) B! E7 ^; f  f6 Wthen* Y( t0 @; ~+ M. M
for nIndex in vFullMetaObject.aIndexes.first..vFullMetaObject.aIndexes.last0 r1 L! o! R( K5 f5 j
loop
2 {* z/ z3 H* ^0 \) [Show(vFullMetaObject.aIndexes(nIndex));7 [# F/ q# v! b* h
end loop;% }; ]! H) d' p$ o0 S% m
end if;
' Y( P, o1 R$ M6 W7 m+ v4 Pdbms_output.put_line(’Finish Indexes: ’ || pTable);; f. H* t/ e, l
dbms_output.put_line(’Start Constraints: ’ || pTable);
5 b# r5 ~& L4 @; P+ hif (vFullMetaObject.aConstraints.count > 0), _  y8 Y* w: _- P
then
5 k3 u! a  p% H: i( C9 V6 r4 C- o; hfor nIndex in vFullMetaObject.aConstraints.first..vFullMetaObject.aConstraints.last
3 b% l# s- ~. `* w5 |7 Y* \. n7 }loop$ `% s4 n. c, |
Show(vFullMetaObject.aConstraints(nIndex));9 o' D* H7 s. F6 n$ m; M* p% F
end loop;
3 W5 c' X+ w; l+ S: l: Z$ Tend if;) f# j( ~' T" A7 }) A. |
dbms_output.put_line(’Finish Constraints: ’ || pTable);6 x. s/ d  A) N! H' c3 k/ |4 D8 e
dbms_output.put_line(’Start Triggers: ’ || pTable);3 Q" b& A8 q2 T2 ~" l' s
if (vFullMetaObject.aTriggers.count > 0)
! J# A1 T6 E! G) I  z6 F% fthen. X, Z1 T* Z- N% z
for nIndex in vFullMetaObject.aTriggers.first..vFullMetaObject.aTriggers.last
! l+ F3 p4 \* d2 q* Lloop
& e: r0 U0 T' PShow(vFullMetaObject.aTriggers(nIndex));
: V% j5 I; C' c6 r7 zend loop;
6 y! e! q; e7 M$ i. D2 D* kend if;
$ o5 X' q: V+ \3 H3 q- s  rdbms_output.put_line(’Finish Triggers: ’ || pTable); 9 o  y( j- |- u+ Q
dbms_output.put_line(’Finish Full Object: ’ || pTable); 8 e$ {: w& R5 R; m- O# e' P& }6 ?
end if;
! E0 J- j, Z  cend Show;
: v# x. I, K, a: x  q, b% y. m9 f( Q+ |
procedure Show
5 q3 r6 B5 N4 o# Jis) H; o7 H/ \8 B2 j
vTable tString;$ |* D  V$ ]# U# U& ?+ {
begin
! G8 t+ u0 c; _3 v7 V if vMetaData.count > 0
! {6 g. |2 Z; {3 h then
+ G* P8 p3 o& z1 d( p  dbms_output.put_line(’Total Meta Objects: ’ || to_char(vMetaData.count));
% A$ q/ E7 V$ Z1 c) s( I* {  vTable := vMetaData.first;/ B7 i3 N  z3 v" D. q+ @6 K0 l
  while (vTable is not null)
/ J; }) [4 [) W' f% ^7 ^# c  loop) e+ [: l: c  h% m5 r1 @
   Show(vTable);' s4 e5 ]$ L/ m  ~9 m
   vTable := vMetaData.next(vTable);( k, S9 L, m. I4 h
  end loop;
' F3 j' f# r7 e( @6 ?; x! X end if;
# ]9 [. U9 I8 O% _7 u. r8 gend Show;" ]% r6 @- G, \- \

- E' \$ Q$ W' ^, P5 R2 i) Jbegin
1 c" J* n' n/ s) wSetEnvironment;
: U# n/ P' F7 i) N; v2 uend MetaDataPkg;
回复 支持 反对

使用道具 举报

 楼主| 发表于 2012-8-4 14:06:23 | 显示全部楼层

Oracle中提取和存储数据库对象的DDL

 下面的代码防止输出信息采用缩排或换行格式化:
4 q4 d" P* e# D1 c; ]2 z# ^
- K' K$ G' y; z  Q- Fdbms_metadata.set_transform_param(dbms_metadata.session_transform, ’PRETTY’, false);+ Z' U3 g, P; _
  下面的三行输出片段属性(物理属性、存储属性、表空间、日志等)、数据表的存储、表空间子句和索引对象定义:- W3 K+ w1 o5 v- W
6 M( f" n4 F: r3 w* J$ M
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);0 i, H1 r8 J8 {9 f4 X
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’STORAGE’, true);
- ]+ K5 l0 F- s4 O/ W; I% T& @. ddbms_metadata.set_transform_param(dbms_metadata.session_transform, ’TABLESPACE’, true);: _+ d) I9 U2 L& L
  明确地指定所有的物理、存储和日志属性是非常重要的--否则,它们会被设为默认值,而这个值可能与原始设置的值不同。
7 q* u8 h0 |3 V& y# U, ?  x3 r  f- ?- k
  SetEnvironment()过程最后的三行防止所有的非参考和参考约束被包含到表的DDL中。它还禁止独立的ALTER TABLE语句(如果必要,还可以禁止CREATE INDEX语句)来生成数据表约束:7 A( M# P* l- b) X% a

! s3 M, u! e' ]/ }% Adbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS’, false);
, U6 C2 {' z1 `. Rdbms_metadata.set_transform_param(dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);6 Y% M( V, C9 ]5 @% v5 \! @9 b
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);
2 m/ p- w" B" Q, d  为了达到最大的灵活性,最好分别提取数据表、索引和约束的对象定义并保证它们彼此都相互独立。通过这种办法,你可以控制这些对象的建立次序。
$ o; p8 L; [3 f3 Q+ w% Z4 h- f0 c  j0 ]% P* z) D  u) Y
  MetaDataPkg程序包的主要工作部分是MetaDataPkg.GetDDL()函数。MetaDataPkg.GetDDL()包含了列表1代码的扩展版本。添加到里面的是提取超过32767个字符的DDL字符串的能力。它可以帮助处理分区的数据表定义--随着分区数量的增长,它可能变得很长。这也是GetDDL()代码把DDL字符串分析并载入每个长达32767字符的字符串数组的原因。目前的代码版本只返回第一个数组元素,因此你需要修改这段代码,把该数组转换为tMetaObject记录类型的属性。这样就允许它处理长于32767字符的字符串,当然这种情况非常少见。3 ?1 v' b/ {# D* Q" `; W* U7 l

2 h! s1 h1 J% z% K& f1 _  使用MetaDataPkg.GetMeta() API可以得到每个特定数据表的完整的元数据对象。这个API接受两个参数:pTable,它是表的名称;pForce,布尔型标记。当pForce被设置为TRUE的时候,它强迫元数据从Oracle数据字典中检索,接着把元数据载入存储器中--不管是否准备好了。但是默认的值是FALSE,因此第一个调用把元数据载入存储器中并返回tFullMetaObject类型的对象,后面的GetMeta()调用简单地从存储器中检索元数据。
: O. A& h+ x$ \3 x* M5 p7 n* Y' Q+ L; ?( O4 ~
  使用MetaDataPkg程序包
4 s- M' u  H$ _7 d
. I' R/ k5 D+ e. ?" z" E7 I  为了演示如何使用MetaDataPkg程序包,我建立了一小段匿名代码块。它把EmpTest表中的元数据载入元数据存储器中,并输出它的内容。. @( ~" r2 o1 n; n, ^
0 [2 I* }: ]( A" ^' x
  下面就是匿名的PL/SQL代码块:  ?+ E- d/ t) r7 q" f

) q8 _3 ?" G' }3 L: Jdeclare/ X0 ]  L5 u; o& b7 c; [7 M
vTable MetaDataPkg.tString := ’EmpTest’;' u5 q6 y- F8 ?! u
vRunStartTime number;* q6 K7 `9 |' C/ Y
begin
0 y( A0 K; w4 w9 ?- s( _! S) KvRunStartTime := dbms_utility.get_time;1 v; n2 A8 w- L6 V) c
MetaDataPkg.Load(vTable, true);
6 |# g- b; C5 @2 zMetaDataPkg.Show();# t4 f* J8 d6 _( [. H( \! M
dbms_output.put_line(’Time Elapsed: ’ ||
% a+ f! v# c7 dto_char((dbms_utility.get_time - vRunStartTime) / 100) || ’ sec.’);0 G7 i9 j1 E$ L7 D& A/ N
end;9 Q& Q' Q% {+ r8 I/ ~4 J% c$ F6 o# U
  列表4显示了前面的代码的输出信息。. h' S3 O7 [/ ^# x, q$ |& _* }

0 f9 }0 r- q9 k* k# L9 a  你可以看到,这段代码把EmpTest数据表和其索引、约束的全部元数据信息载入到存储器中,并在一秒钟之内把它检索出来了。你现在拥有了一个用于开发自动的解决方案的API了,它可以进行任何数据维护操作,包括更名、转换和删除数据库对象。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|Woexam.Com ( 湘ICP备18023104号 )

GMT+8, 2024-3-29 18:21 , Processed in 0.275268 second(s), 29 queries .

Powered by Discuz! X3.4 Licensed

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表