半岛外围网上直营

LOB基本概念：

    LOB是一种数据类型，主要用于存储大对象。在Oracle中存在三种LOB数据类型分别是BLOB：Binary Large Object、CLOB：Character Large Object、DBCLOB：Double-byte Character Large Object，今天主要来看一下这个BLOB和CLOB类型。

1. BLOB数据类型存储非结构化二进制大对象，BLOB对象可以当做是没有字符集语义的bitstreams。
2. CLOB数据类型存储单字节和多字节数据，这两个的固定的宽度和和不同的宽度都支持设置，两者都可以使用数据库字符集。
3. NCLOB数据类型存储Unicode数据。

BLOB 数据的使用

    二进制数据一般都会表现为字节流或是缓冲区。dotConnect for Oracle允许用最方便的方式操纵BLOB数据。你可以使用OracleLob。值属性或使用OracleLob.Read()和OracleLob.Write()方法来传输数据到或是来自服务器，两种方法下面都有雨示例。值得注意的是当OracleLob.LobType属性是OracleDbType.Blob时，这个OracleLob.Value被视为字节数组，OracleDbType.Clob以及OracleDbType.NClob将会被一字符串视为OracleLob.Value。

     下一个示例程序显示如何从硬盘上传文件到服务器以及下载下来。要执行这些程序，你必须创建一个表，如下：

CREATE TABLE PICTURES (

  ID NUMBER(12),

  PICNAME VARCHAR2(20),

  PICTURE BLOB

)

[C#]

static void UploadBlob(OracleConnection myConnection)
{
  //Open file on disk
  FileStream fs = new FileStream("D:\\Tmp\\test.bmp", FileMode.Open, FileAccess.Read);
  BinaryReader r = new BinaryReader(fs);
  myConnection.Open();
  //Create temporary BLOB
  OracleLob myLob = new OracleLob(myConnection,OracleDbType.Blob);
  int streamLength = (int)fs.Length;
  //Transfer data to server
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
  //Perform INSERT
  OracleCommand myCommand = new OracleCommand(
   "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection);
  OracleParameter myParam = myCommand.Parameters.Add("Pictures", OracleDbType.Blob);
  myParam.OracleValue = myLob;
  try
  {
    Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected.");
  }
  finally
  {
    myConnection.Close();
    r.Close();
    fs.Close();
  }
}

static void DownloadBlob(OracleConnection myConnection)
{
  OracleCommand myCommand = new OracleCommand("SELECT * FROM Pictures", myConnection);
  myConnection.Open();
  OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  try
  {
    while (myReader.Read())
    {
      //Obtain OracleLob directly from OracleDataReader
      OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"));
      if (!myLob.IsNull)
      {
        string FN = myReader.GetString(myReader.GetOrdinal("PicName"));
        //Create file on disk
        FileStream fs = new FileStream("D:\\Tmp\\" + FN + ".bmp", FileMode.Create);
        //Use buffer to transfer data
        byte[] b = new byte[myLob.Length];
        //Read data from database
        myLob.Read(b,0,(int)myLob.Length);
        //Write data to file
        fs.Write(b,0,(int)myLob.Length);
        fs.Close();
        Console.WriteLine(FN + " downloaded.");
      }
    }
  }
  finally
  {
    myReader.Close();
    myConnection.Close();
  }
}

[Visual Basic]

Public Sub UploadBlob(ByVal myConnection As OracleConnection)
  'Open file on disk
  Dim fs As FileStream = New FileStream("D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read)
  Dim r As BinaryReader = New BinaryReader(fs)
  myConnection.Open()
  'Create temporary BLOB
  Dim myLob As OracleLob = New OracleLob(myConnection, OracleDbType.Blob)
  Dim streamLength As Int32 = fs.Length
  'Transfer data to server
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength)
  'Perform INSERT
  Dim myCommand As OracleCommand = New OracleCommand(
    "INSERT INTO Pictures (ID, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection)
  Dim myParam As OracleParameter = myCommand.Parameters.Add("Pictures", OracleDbType.Blob)
  myParam.OracleValue = myLob
  Try
    Console.WriteLine(myCommand.ExecuteNonQuery() & " rows affected.")
  Finally
    myConnection.Close()
    r.Close()
    fs.Close()
  End Try
End Sub

Public Sub DownloadBlob(ByVal myConnection As OracleConnection)
  Dim myCommand As New OracleCommand("SELECT * FROM Pictures", myConnection)
  myConnection.Open()
  Dim myReader As OracleDataReader = & _
      myCommand.ExecuteReader(System.Data.CommandBehavior.Default)
  Try
    While myReader.Read()
      Dim myLob As OracleLob = myReader.GetOracleLob(myReader.GetOrdinal("Picture"))
      If Not myLob.IsNull Then
        Dim FN As String = myReader.GetString(myReader.GetOrdinal("PicName"))
        Dim fs As FileStream = New FileStream("D:\Tmp\" + FN + ".bmp", FileMode.Create)
        Dim w As BinaryWriter = New BinaryWriter(fs)
        w.Write(myLob.Value)
        w.Close()
        fs.Close()
        Console.WriteLine(String.Concat(FN, " downloaded."))
      End If
    End While
  Finally
    myReader.Close()
    myConnection.Close()
  End Try
End Sub

CLOB 数据使用

    CLOB 数据的使用和BLOB数据是一样的，区别就在于Value属性的表现。当从OracleLob.Value读取时，CLOB和NCLO数据类型，会得到字符串。数据是透明的解码，所以你不必担心字符集。如果需要原始数据，可以使用 OracleLob的里流媒体功能，那是OracleLob.Read 和OracleLob.Write的方法。请注意，由于OracleLob.Value是一个只读属性，你可以只用OracleLob.Write方法将数据写入到的LOB中，但是并没有执行编码。

For example consider this table definition.

CREATE TABLE ClobTable (

  Id NUMBER,

  Name VARCHAR2(30),

  Value CLOB

)

    如果您需要执行简单的任务，如获取记录集，在大多数情况下，你不需要知道oraclelob。当在fetch 上调用OracleDataReader.GetValue时，将会得到一组字节用于BLOB列以及用于CLOB one的字符串。此外你可以使用OracleDataReader.GetChars、 OracleDataReader.GetBytes得到LOB值块。示例如下：

[C#]

...
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
while (reader.Read())
  Console.WriteLine(reader.GetValue(2));
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand( _
    "SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
while (reader.Read())
  Console.WriteLine(reader.GetValue(2))
end while
...

    使用OracleLob对象也可以达到一样的效果：

[C#]

...
OracleCommand oraCommand = new OracleCommand("SELECT * FROM ClobTable", oraConnection);
OracleDataReader reader = oraCommand.ExecuteReader();
OracleLob clob;
while (reader.Read()) {
  clob = reader.GetOracleLob(2);
  Console.WriteLine(clob.Value);
}
...

[Visual Basic]

...
Dim oraCommand As OracleCommand = new OracleCommand( _
    "SELECT * FROM ClobTable", oraConnection)
Dim reader As OracleDataReader = oraCommand.ExecuteReader()
Dim clob As OracleLob
while (reader.Read())
  clob = reader.GetOracleLob(2)
  Console.WriteLine(clob.Value)
end while
...