C# 操作 XML 數據庫類型、Oracle XMLType(6)

4.3 數據庫更改

declare
   -- 舊Schema
  oldSchemaDoc nvarchar2(2000) := '<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Phones" nillable="true" type="ArrayOfPhone" />
  <xs:complexType name="ArrayOfPhone">
    <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="unbounded" name="Phone" nillable="true" type="Phone" />
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="Phone">
    <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="Code" type="xs:string" />
      <xs:element minOccurs="1" maxOccurs="1" name="Type" type="PhoneType" />
    </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="PhoneType">
    <xs:restriction base="xs:string">
      <xs:enumeration value="未知" />
      <xs:enumeration value="移動" />
      <xs:enumeration value="固定" />
    </xs:restriction>
  </xs:simpleType>
</xs:schema>';
 -- 新Schema
  newSchemaDoc nvarchar2(2000) := '<xs:schema elementFormDefault="qualifIEd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Phones" nillable="true" type="ArrayOfPhone" />
  <xs:complexType name="ArrayOfPhone">
    <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="unbounded" name="Phone" nillable="true" type="Phone" />
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="Phone">
    <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="Code" type="xs:string" />
      <xs:element minOccurs="1" maxOccurs="1" name="Type" type="PhoneType" />
      <xs:element minOccurs="0" maxOccurs="1" name="Make" type="xs:string" />
    </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="PhoneType">
    <xs:restriction base="xs:string">
      <xs:enumeration value="未知" />
      <xs:enumeration value="移動" />
      <xs:enumeration value="固定" />
      <xs:enumeration value="小靈通" />
    </xs:restriction>
  </xs:simpleType>
</xs:schema>';
 -- 變量存儲 xmldiff xml差異結果
 diffXMLDoc clob;
 -- url
 v_schema_url nvarchar2(255) :=  'http://www.OracleDemo.com/Phones.xsd';
begin
   --生成差異結果
  select xmldiff(xmltype(oldSchemaDoc),xmltype(newSchemaDoc)).getClobVal() into diffXMLDoc from dual;
  -- 11g 新增加的原地更改函數，性能比原來那個 DBMS_XMLSCHEMA.copyEvolve
  -- 概念 執行原地 XML 模式演變 http://www.Oracle.com/technology/global/cn/obe/11gr1_db/datamgmt/xmldb2_a/xmldb2_a.htm
  DBMS_XMLSCHEMA.inPlaceEvolve( v_schema_url ,  xmltype(diffXMLDoc));
end;

流程就是拿 舊的Schema 和 新的Schema 比較生成一個差異結果 select xmldiff...這裡，然後調用 DBMS_XMLSCHEMA.inPlaceEvolve

更新 Schema很簡單函數就用到兩個，代碼沒多少就是2個Schema 占地方；