科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航



ZDNet>软件频道>数据库-zhiding>Oracle中提取和存储数据库对象的DDL

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

从对象(例如数据库表、索引、约束、触发器等)中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据……

来源:天极网 2007年10月22日

关键字:ORACLE DDL 对象 提取 ORACLE

从对象(例如数据库表、索引、约束、触发器等)中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据(metadata),并把这些数据存储在内存中。尽管目前有很多脚本可以实现这样的功能,但是它们通常都是不完整的或者过时的。幸运的是,Oracle 9.2提供了一个实现这样的功能的API:DBMS_METADATA程序包。

  在很多情况下,数据库中数据的维护操作要求我们提取多种对象(例如数据库表、索引、约束、触发器等)的DDL(Data Definition Language,数据定义语言)。

  最近我承担了一个任务,我需要编写一组数据库程序包来执行高性能的大量的数据删除(DELETE)操作。这样的操作要求我拥有提取和存储数据库对象DDL的相关技术。

  提取和存储数据库对象的DDL的方法如下:

  · 建立与源表结构相同的数据表,但是它不带主键、备用键和外部键约束。

  · 例如,使用MyTable_X,其中MyTable是要被删除的目标数据表。

  · 把需要保存的数据插入新建立的数据表(MyTable_X)中。

  · 使用NOLOGGING PARALLEL选项在新数据表上建立索引。

  · 在新数据表上建立约束。

  · MyTable和MyTable_X数据表进行交换。把主表改名为MyTable_T,把MyTable_X改名为MyTable。

  · 验证结果并删除MyTable_T表。

  很明显,为了编写实现上面目标的代码,你必须提取数据库对象的元数据(定义和被选中的属性),并把它存储在内存中,这样在执行上面的操作的时候才能够使用它。

  在网上存在大量的脚本,它们可以从多种Oracle数据字典(user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等)中提取数据库对象的元数据,接着为特定的对象构造DDL命令。这些脚本的一个问题是,它们通常是SQL*Plus脚本,它会生成客户端文本文件,而这个文件不能被服务器端代码访问。它们的主要问题有:

  · 不完整:不能提取所有的选项,并组合进DDL语句中。

  · 过时了:这些脚本通常不支持Oracle最新的数据库特性--分区(partitioning)、基于函数的索引、自动段空间管理(ASSM)等。这些脚本可能崩溃或生成错误的DDL语句。

  问题总结:尽管有大量的从Oracle数据字典中提取数据库对象元数据的脚本,但是它们中的大多数要么不完整,要么过期了。

  解决方案:使用DBMS_METADATA程序包,学习如何用最佳的、没有错误的和易于维护的方式执行上面的事务。

  使用Oracle的本地API:DBMS_METADATA程序包

  Oracle数据库采用补充PL/SQL程序包的形式提供了丰富的预先包装好的API。Oracle 9.2版本中引入的DBMS_METADATA程序包可能正好适合你的需求。它包含了用于检索数据库对象定义的API。

  我们将使用的API主要是DBMS_METADATA.GET_DDL函数。这个函数返回的对象定义SQL字符串是CLOB。它拥有下面一些输入参数:

  · object_type VARCHAR2

  · name VARCHAR2

  · schema VARCHAR2 DEFAULT NULL

  · version VARCHAR2 DEFAULT ’COMPATIBLE

  · model VARCHAR2 DEFAULT ’ORACLE’,

  · transform VARCHAR2 DEFAULT ’DDL’

  下面建立了一个用于测试的EmpTest数据表,它带有索引和约束:

create table EmpTest
(
empNo integer not null,
lastName varchar2(30) not null,
firstName varchar2(20) not null,
job varchar2(9) ’
hireDate date
isActive number(1)
constraint EmpTest_CK1
check (isActive in (0,1)) ,
salary number(9,2) ,
commision number(9,2) ,
deptNo number(2) ,
constraint EmpTest_PK
primary key (empNo),
constraint EmpTest_AK1
unique (lastName, firstName)
);

create index EmpTest_HireDate_Salary
on EmpTest
(
salary,
hireDate
);

  运行上面的脚本之后,就建立了一个带有三个索引(两个唯一的和一个不唯一的索引)的EmpTest表:

select index_name, index_type, uniqueness
from user_indexes
where table_name = ’EMPTEST’;

索引名称 索引类型 唯一性
EMPTEST_AK1 NORMAL UNIQUE
EMPTEST_HIREDATE_SALARY NORMAL NONUNIQUE
EMPTEST_PK NORMAL UNIQUE

  EmpTest表还包括六个约束:

  · 一个主键-EmpTest_PK

  · 一个备用键-EmpTest_AK

  · 一个检查约束-EmpTest_CK1

  · 系统生成的(SYS_*)三个非空的约束,名称如下:

约束名称 约束类型 索引名称
SYS_C002144065 C  
SYS_C002144066 C  
SYS_C002144067 C  
EMPTEST_CK1 C  
EMPTEST_PK P EMPTEST_PK
EMPTEST_AK1 U EMPTEST_AK1

  现在我们执行匿名的PL/SQL代码块来调用DBMS_METADATA.GET_DDL函数,检索数据表的定义。

  DBMS_OUTPUT程序包只能输出最长为255字符的字符串,由于在处理数据表的DDL字符串的时候太容易超过这个限制,所以这是一个问题。为了解决这个问题,我们使用了本地过程Show()(列表1所示)。

  列表1:调用DBMS_METADATA.GET_DDL()函数的PL/SQL代码块

declare
vClob clob;
vLongString varchar2(32767);
vOffSet pls_integer := 0;
vLength pls_integer := 0;
vTable varchar2(30) := ’EmpTest’;

procedure Show (pVariable varchar2, pLineSize pls_integer := 80)
is
begin
dbms_output.enable(1000000);
if (length(pVariable) > pLineSize)
then
dbms_output.put_line(substr(pVariable, 1, pLineSize));
Show(substr(pVariable, pLineSize + 1), pLineSize);
else
dbms_output.put_line(pVariable);
end if;
end Show;
begin
-- 获取 DDL
vClob := dbms_metadata.get_ddl(’TABLE’, upper(vTable));

-- 获取 CLOB 长度
vLength := dbms_lob.GetLength(vClob);
dbms_output.put_line(’DDL length: ’ || to_char(vLength));

vOffSet := 1;
dbms_lob.read(vClob, vLength, vOffSet, vLongString);
-- 关闭 CLOB
if (dbms_lob.isOpen(vClob) > 0)
then
dbms_lob.close(vClob);
end if;
Show(vLongString, 80);
end;

  列表1生成下面的输出信息:

DDL length: 461
CREATE TABLE "BORIS"."EMPTEST"
( "EMPNO" NUMBER(*,0) NOT NULL ENABLE,
"LASTNAME" VARCHAR2(30) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"JOB" VARCHAR2(9),
"HIREDATE" DATE,
"ISACTIVE" NUMBER(1,0),
"SALARY" NUMBER(9,2),
"COMMISION" NUMBER(9,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMPTEST_CK1" CHECK (isActive in (0,1)) ENABLE,
CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS" ENABLE, CONSTRAINT "EMPTEST_AK1" UNIQUE ("LASTNAME", "FIRSTNAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS" ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS"

  它运行的情况太好了,返回的数据表的DDL字符串带有主键EmpTest_PK、备用键EmpTest_AK1和检查约束EmpTest_CK1。它建立了两个唯一的索引来支持主键和备用键约束。这不是你需要的结果:你需要一个表,但是为了加快数据载入速度,它不要包含约束和索引。只有在数据载入工作完成以后,你才建立索引和约束。

  保证对象的定义独立的另外一个原因在于灵活性:你可能需要改变对象建立的次序。

  现在可以设计一个数据结构来存储对象的元数据了。 元数据存储器:MetaDataPkg程序包规范

  首先,你必须建立记录类型来存储独立的对象(例如数据表、索引等)的所有必要信息:

subtype tString is varchar2(30);
subtype tDBString is varchar2(255);
subtype tDBLongString is varchar2(4000);
subtype tLongString is varchar2(32767);
type tArrayLongString is table of tLongString
index by pls_integer;
type tMetaObject is record
(
aName tString,
aType tString,
aLogging tString,
aParallel tString,
aStatus tString,
aValidated tString,
aRely tString,
aDDLString tLongString
);

  tMetaObject属性保存了下面一些信息:

  · aName:对象的名称,例如EMPTEST_PK1。

  · aType:对象的类型,例如’YES’ (分区的)/’NO’ (分区的) (用于表)、 ’UNIQUE’/’NONUNIQUE’ (用于索引)、 约束类型 ’P’/’U’/’C’/’R’ (用于约束)。

  · aLogging:对象的日志选项,例如’LOGGING’/ ’NOLOGGING’ (用于表和索引)。

  · aParallel: 对象的平行程度(用于表和索引)。

  · AStatus:对象的状态,例如’VALID’/’UNUSABLE’ 用于索引、 ’Y’ (备份了)/’N’ (未备份)用于表。

  · AValidated:对象的验证选项,例如’VALIDATED’/’NOT VALIDATED’(用于约束)。

  · ARely:对象的依赖选项,例如’RELY’/’NORELY’ (用于约束)。

  · ADDLString:对象的定义SQL字符串。

  现在你必须定义一个相关的数组类型,它能够列举出某种类型的对象,从保存tMetaObject类型的多个对象,例如,所有的EmpTest索引:

type tArrayMetaObject is table of tMetaObject
index by pls_integer;

  下一步需要建立一个记录类型,它包含了数据表表自身(aTable)的tMetaObject属性和三个tArrayMetaObject属性:一个用于索引(aIndexes),一个用于约束(aConstraints),一个用于触发器(aTriggers):

type tFullMetaObject is record
(
aTable tMetaObject,
aIndexes tArrayMetaObject,
aConstraints tArrayMetaObject,
aTriggers tArrayMetaObject
);

  tFullMetaObject对象类型保存了单个表的全部对象的元数据。最后,位于顶层的类型是tFullMetaObject数组。TarrayFullMetaObjectByString类型是tFullMetaObject的一个表,索引类型是varchar2(30)。

  列表2:MetaDataPkg程序包规范

  用如下的方式建立或更新MetaDataPkg:

cEnabled constant char(7) := ’ENABLED’;
cDisabled constant char(8) := ’DISABLED’;
cUsable constant char(6) := ’USABLE’;
cUnusable constant char(8) := ’UNUSABLE’;
cValid constant char(5) := ’VALID’;
cInvalid constant char(7) := ’INVALID’;

cTable constant char(5) := ’TABLE’;
cView constant char(4) := ’VIEW’;
cIndex constant char(5) := ’INDEX’;
cConstraint constant char(10) := ’CONSTRAINT’;
cTrigger constant char(7) := ’TRIGGER’;
cLobType constant char(3) := ’LOB’;
cClobType constant char(4) := ’CLOB’;
cBlobType constant char(4) := ’BLOB’;
cPackage constant char(7) := ’PACKAGE’;
cPackageBody constant char(12) := ’PACKAGE BODY’;
cProcedure constant char(9) := ’PROCEDURE’;
cFunction constant char(8) := ’FUNCTION’;
cSequence constant char(8) := ’SEQUENCE’;
cSynonym constant char(7) := ’SYNONYM’;
cType constant char(4) := ’TYPE’;
cColumn constant char(6) := ’COLUMN’;
cJavaSource constant char(11) := ’JAVA SOURCE’;
cJavaClass constant char(10) := ’JAVA CLASS’;

cYes constant char(3) := ’YES’;
cNo constant char(2) := ’NO’;

cPKConsType constant char(1) := ’P’;
cUNConsType constant char(1) := ’U’;
cFKConsType constant char(1) := ’R’;
cCKConsType constant char(1) := ’C’;

cDropStorage constant char(12) := ’DROP STORAGE’;
cReuseStorage constant char(13) := ’REUSE STORAGE’;
cCascade constant char(19) := ’CASCADE CONSTRAINTS’;
cNoCascade constant char(10) := ’NO CASCADE’;
cEnable constant char(6) := ’ENABLE’;
cNovalidate constant char(10) := ’NOVALIDATE’;
cRely constant char(4) := ’RELY’;
cNoRely constant char(6) := ’NORELY’;
cValidated constant char(9) := ’VALIDATED’;
cNotValidated constant char(13) := ’NOT VALIDATED’;
cLogging constant char(7) := ’LOGGING’;
cNoLogging constant char(9) := ’NOLOGGING’;
cParallel constant char(8) := ’PARALLEL’;
cNoParallel constant char(10) := ’NOPARALLEL’;
cNull constant char(4) := ’NULL’;
cNotNull constant char(8) := ’NOT NULL’;
cDefault constant char(7) := ’DEFAULT’;

cSYSPrefix constant char(4) := ’SYS_’;
cDoubleQuote constant char(1) := ’"’;

subtype tString is varchar2(30);
subtype tDBString is varchar2(255);
subtype tDBLongString is varchar2(4000);
subtype tLongString is varchar2(32767);

type tArrayLongString is table of tLongString
index by pls_integer;

type tMetaObject is record
(
 aName tString,
 aType tString,
 aLogging tString,
 aParallel tString,
 aStatus tString,
 aValidated tString,
 aRely tString,
 aDDLString tLongString
);

type tArrayMetaObject is table of tMetaObject
index by pls_integer;

type tFullMetaObject is record
(
 aTable tMetaObject,
 aIndexes tArrayMetaObject,
 aConstraints tArrayMetaObject,
 aTriggers tArrayMetaObject
);

type tArrayFullMetaObjectByString is table of tFullMetaObject
index by varchar2(30);

procedure Load
(
 pTable in tString,
 pForce in boolean := false
);

procedure Reset
(
 pTable in tString
);

procedure Reset;

function GetMeta
(
 pTable in tString,
 pForce in boolean := false
)
return tFullMetaObject;

function GetMeta
return tArrayFullMetaObjectByString;

procedure SetMeta
(
 pTable in tString,
 pFullMetaObject in tFullMetaObject
);

procedure SetMeta
(
 pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
);

procedure Show
(
 pTable in tString
);

procedure Show;
end MetaDataPkg;

  上面的类型对象是作为元数据存储器的,用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中(列表2所示)。我还介绍了下面一些API:

  · MetaDataPkg.Load()过程:把特定表的元数据信息载入存储器中。

  · MetaDataPkg.GetMeta()函数:它从存储器中检索tFullMetaObject类型的对象。

  · MetaDataPkg.SetMeta()过程(重载的):把对象的元数据存储到存储器中。

  · MetaDataPkg.Reset过程(重载的):对存储器复位。

  · MetaDataPkg.Show过程(重载的):显示存储器的内容。 实现所有这些事务的代码

  列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程,因此在每个对话中它都只执行一次,符合你的需求(你希望在开头设置一次)。程序包提供了用于设置环境参数的API:DBMS_METADATA.SET_TRANSFORM_PARAM()过程。

  列表3:MetaDataPkg程序包主体

vMetaData tArrayFullMetaObjectByString;

procedure SetEnvironment
is
begin
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’PRETTY’, false);
 dbms_metadata.SET_TRANSFORM_PARAM(
  dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’STORAGE’, true);
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’TABLESPACE’, true);
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’CONSTRAINTS’, false);
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
 dbms_metadata.set_transform_param(
  dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);

end SetEnvironment;

procedure Print
(
 pString varchar2,
 pLineSize positive := 80
)
is

vLineSize pls_integer := least(nvl(pLineSize, 80), 255);

begin
 dbms_output.enable(1000000);

 if (length(pString) > vLineSize)
 then
  dbms_output.put_line(substr(pString, 1, vLineSize));
  Print(substr(pString, pLineSize + 1), vLineSize);
 else
  dbms_output.put_line(pString);
 end if;

end Print;

procedure Show
(
 pMetaObject in tMetaObject
)
is
begin
dbms_output.put_line(’***’);
dbms_output.put_line(’Name: ’ || pMetaObject.aName);
dbms_output.put_line(’Type: ’ || pMetaObject.aType);
dbms_output.put_line(’Logging: ’ || pMetaObject.aLogging);
dbms_output.put_line(’Parallel: ’ ||
to_char(pMetaObject.aParallel));
dbms_output.put_line(’Status: ’ || pMetaObject.aStatus);
dbms_output.put_line(’Validated: ’ ||
pMetaObject.aValidated);
dbms_output.put_line(’Rely: ’ || pMetaObject.aRely);
print(’DDL String: ’ || pMetaObject.aDDLString, 255);
dbms_output.put_line(’***’);
end Show;

function GetDDL
(
 pName in tString,
 pType in tString
)
return tLongString
is
vClob clob;

vLongStrings tArrayLongString;

vFullLength pls_integer := 0;
vOffSet pls_integer := 0;
vLength pls_integer := 0;

begin

vClob := dbms_metadata.get_ddl(pType, upper(pName));

vFullLength := dbms_lob.GetLength(vClob);

for nIndex in 1..ceil(vFullLength / 32767)
loop
 vOffSet := vLength + 1;
 vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);

 dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex));

 vLongStrings(nIndex) := replace(vLongStrings(nIndex),
    cDoubleQuote || user || cDoubleQuote || ’.’,
    ’’);

 vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ’’)));
end loop;

if (dbms_lob.isOpen(vClob) > 0)
then
 dbms_lob.close(vClob);
end if;

return vLongStrings(1);

end GetDDL;

function ObjectExists
(
 pObjectName in tString,
 pObjectType in tString,
 pTableName in tString := null
)
return boolean
is

vCount pls_integer := 0;
vObjectName tString := ltrim(rtrim(pObjectName));
vObjectType tString := upper(ltrim(rtrim(pObjectType)));
vTableName tString := upper(ltrim(rtrim(pTableName)));

begin

case
 when vObjectType = cColumn
 then
 select count(*)
  into vCount
  from Dual
  where exists (select ’1’
  from user_tab_columns
  where column_name = upper(vObjectName)
  and table_name = vTableName);

 when vObjectType = cConstraint
 then
  select count(*)
  into vCount
  from Dual
  where exists (select ’1’
  from user_constraints
  where constraint_name = upper(vObjectName)
  and table_name = vTableName);

when vObjectType in (cJavaSource, cJavaClass)
then
 select count(*)
 into vCount
 from Dual
 where exists (select ’1’
 from user_objects
 where object_name = vObjectName and object_type = vObjectType);

 else
  select count(*)
  into vCount
  from Dual
  where exists (select ’1’
  from user_objects
  where object_name = upper(pObjectName)
  and object_type = vObjectType);
 end case;

return (vCount > 0);

end ObjectExists;

procedure Load
(
 pTable in tString,
 pForce in boolean := false
)
is

vFullMetaObject tFullMetaObject;

vTable tString := upper(ltrim(rtrim(pTable)));
vCount pls_integer := 0;

begin

if (not vMetaData.exists(vTable) or nvl(pForce, false))
 then
 if not ObjectExists(pTable, cTable)
 then
  raise_application_error(-20500,
      ’Unable to load metadata for ’ || nvl(pTable, ’NULL’) ||
      ’. ’ || ’Table does not exist.’
  );
end if;

for rec in (select table_name,
logging,
ltrim(rtrim(degree)) as degree,
partitioned,
backed_up
from user_tables
where table_name = vTable)
loop

 vFullMetaObject.aTable.aName := rec.table_name;
 vFullMetaObject.aTable.aType := rec.partitioned;
 vFullMetaObject.aTable.aLogging := rec.logging;
 vFullMetaObject.aTable.aParallel := ltrim(rtrim(rec.degree));
 vFullMetaObject.aTable.aStatus := rec.backed_up;
vFullMetaObject.aTable.aDDLString := GetDDL(rec.table_name, cTable);
end loop;

for rec in (select index_name,
uniqueness,
logging,
ltrim(rtrim(degree)) as degree,
status
from user_indexes
where table_name = vTable
and index_type != cLobType)
loop

vCount := vCount + 1;
vFullMetaObject.aIndexes(vCount).aName := rec.index_name;
vFullMetaObject.aIndexes(vCount).aType := rec.uniqueness;
vFullMetaObject.aIndexes(vCount).aLogging := rec.logging;
vFullMetaObject.aIndexes(vCount).aParallel :=
ltrim(rtrim(rec.degree));
vFullMetaObject.aIndexes(vCount).aStatus := rec.status;
vFullMetaObject.aIndexes(vCount).aDDLString :=
GetDDL(rec.index_name, cIndex);

end loop;

vCount := 0;
for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from       user_constraints where table_name = vTable
order by decode(constraint_type,
   cPKConsType, 10,
   cUNConsType, 20,
   cFKConsType, 30,
   cCKConsType, 40,
   100),
constraint_name)
loop
 vCount := vCount + 1;
 vFullMetaObject.aConstraints(vCount).aName := rec.constraint_name;
 vFullMetaObject.aConstraints(vCount).aType := rec.constraint_type;
 vFullMetaObject.aConstraints(vCount).aLogging := null;
 vFullMetaObject.aConstraints(vCount).aParallel := null;
 vFullMetaObject.aConstraints(vCount).aStatus := rec.status;
 vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;
 vFullMetaObject.aConstraints(vCount).aRely := rec.rely;

 if substr(rec.constraint_name, 1, length(cSYSPrefix)) = cSYSPrefix and
      upper(rec.search_condition) like ’%IS ’ || cNotNull || ’%’
 then
  vFullMetaObject.aConstraints(vCount).aDDLString :=
     ’ALTER TABLE ’ || cDoubleQuote || vFullMetaObject.aTable.aName || cDoubleQuote || ’ ’ ||
     ’MODIFY ’ || replace(rec.search_condition, ’IS ’ || cNotNull, cNotNull) ||
     (case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidated
      then ’ ’ || cNovalidate
     else ’’end);
 else
  vFullMetaObject.aConstraints(vCount).aDDLString := GetDDL(rec.constraint_name, cConstraint);
 end if;
end loop;

SetMeta(pTable, vFullMetaObject);
end if;
end Load;

procedure Reset
(
 pTable in tString
)
is
begin

vMetaData.delete(pTable);

end Reset;

procedure Reset
is
begin
 vMetaData.delete;
end Reset;

function GetMeta
(
 pTable in tString,
 pForce in boolean := false
)
return tFullMetaObject
is
begin

if (not vMetaData.exists(pTable) or nvl(pForce, false))
then
 Load(pTable, pForce);

 if not vMetaData.exists(pTable)
 then
  raise_application_error(-20501, ’Unable to find metadata for ’ || pTable || ’ in repository.’);
 end if;
end if;

return vMetaData(pTable);

end GetMeta;

function GetMeta
 return tArrayFullMetaObjectByString
is
begin

return vMetaData;

end GetMeta;

procedure SetMeta
(
 pTable in tString,
 pFullMetaObject in tFullMetaObject
)
is
begin

vMetaData(pTable) := pFullMetaObject;

end SetMeta;

procedure SetMeta
(
 pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
)
is
begin

vMetaData := pArrayFullMetaObjectByString;

end SetMeta;

procedure Show
(
 pTable in tString
)
is

vFullMetaObject tFullMetaObject;

begin

if (vMetaData.exists(pTable))
then
dbms_output.enable(1000000);

vFullMetaObject := vMetaData(pTable);
dbms_output.put_line(’Start Full Object: ’ || pTable);
dbms_output.put_line(’Start Table: ’ || pTable);
Show(vFullMetaObject.aTable);
dbms_output.put_line(’Finish Table: ’ || pTable);

dbms_output.put_line(’Start Indexes: ’ || pTable);
if (vFullMetaObject.aIndexes.count > 0)
then
for nIndex in vFullMetaObject.aIndexes.first..vFullMetaObject.aIndexes.last
loop
Show(vFullMetaObject.aIndexes(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Indexes: ’ || pTable);
dbms_output.put_line(’Start Constraints: ’ || pTable);
if (vFullMetaObject.aConstraints.count > 0)
then
for nIndex in vFullMetaObject.aConstraints.first..vFullMetaObject.aConstraints.last
loop
Show(vFullMetaObject.aConstraints(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Constraints: ’ || pTable);
dbms_output.put_line(’Start Triggers: ’ || pTable);
if (vFullMetaObject.aTriggers.count > 0)
then
for nIndex in vFullMetaObject.aTriggers.first..vFullMetaObject.aTriggers.last
loop
Show(vFullMetaObject.aTriggers(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Triggers: ’ || pTable);
dbms_output.put_line(’Finish Full Object: ’ || pTable);
end if;
end Show;

procedure Show
is
vTable tString;
begin
 if vMetaData.count > 0
 then
  dbms_output.put_line(’Total Meta Objects: ’ || to_char(vMetaData.count));
  vTable := vMetaData.first;
  while (vTable is not null)
  loop
   Show(vTable);
   vTable := vMetaData.next(vTable);
  end loop;
 end if;
end Show;

begin
SetEnvironment;
end MetaDataPkg;

  下面的代码防止输出信息采用缩排或换行格式化:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’PRETTY’, false);

  下面的三行输出片段属性(物理属性、存储属性、表空间、日志等)、数据表的存储、表空间子句和索引对象定义:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’STORAGE’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’TABLESPACE’, true);

  明确地指定所有的物理、存储和日志属性是非常重要的--否则,它们会被设为默认值,而这个值可能与原始设置的值不同。

  SetEnvironment()过程最后的三行防止所有的非参考和参考约束被包含到表的DDL中。它还禁止独立的ALTER TABLE语句(如果必要,还可以禁止CREATE INDEX语句)来生成数据表约束:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);

  为了达到最大的灵活性,最好分别提取数据表、索引和约束的对象定义并保证它们彼此都相互独立。通过这种办法,你可以控制这些对象的建立次序。

  MetaDataPkg程序包的主要工作部分是MetaDataPkg.GetDDL()函数。MetaDataPkg.GetDDL()包含了列表1代码的扩展版本。添加到里面的是提取超过32767个字符的DDL字符串的能力。它可以帮助处理分区的数据表定义--随着分区数量的增长,它可能变得很长。这也是GetDDL()代码把DDL字符串分析并载入每个长达32767字符的字符串数组的原因。目前的代码版本只返回第一个数组元素,因此你需要修改这段代码,把该数组转换为tMetaObject记录类型的属性。这样就允许它处理长于32767字符的字符串,当然这种情况非常少见。

  使用MetaDataPkg.GetMeta() API可以得到每个特定数据表的完整的元数据对象。这个API接受两个参数:pTable,它是表的名称;pForce,布尔型标记。当pForce被设置为TRUE的时候,它强迫元数据从Oracle数据字典中检索,接着把元数据载入存储器中--不管是否准备好了。但是默认的值是FALSE,因此第一个调用把元数据载入存储器中并返回tFullMetaObject类型的对象,后面的GetMeta()调用简单地从存储器中检索元数据。

  使用MetaDataPkg程序包

  为了演示如何使用MetaDataPkg程序包,我建立了一小段匿名代码块。它把EmpTest表中的元数据载入元数据存储器中,并输出它的内容。

  下面就是匿名的PL/SQL代码块:

declare
vTable MetaDataPkg.tString := ’EmpTest’;
vRunStartTime number;
begin
vRunStartTime := dbms_utility.get_time;
MetaDataPkg.Load(vTable, true);
MetaDataPkg.Show();
dbms_output.put_line(’Time Elapsed: ’ ||
to_char((dbms_utility.get_time - vRunStartTime) / 100) || ’ sec.’);
end;

  列表4显示了前面的代码的输出信息。

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

查看本文来源

推广二维码
邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题