在 Oracle 和 PHP 中使用 LOB
作者：Harry Fuecks

是否达到 4,000 字节的极限？ 我们先来了解一下 LOB......

本文相关下载： Oracle 数据库 10g Zend Core for Oracle Apache HTTP Server 1.3 和更高版本

使用 VARCHAR2 这样的 Oracle 类型是完全可以的，但如果您要一次性存储的数据量超过它的 4,000 字节的极限，情况将会如何？ 要完成此任务，您需要 Oracle 的某个 Long 对象 (LOB) 类型，为此您应了解如何使用 PHP API 来处理 LOB。 这对于不熟悉它的人来说是很困难的。

• BLOB，用于存储二进制数据
• CLOB，用于使用数据库字符集编码存储字符数据
• NCLOB，用于使用国家字符集存储 Unicode 字符数据。 注意，您将在本文中使用的 PHP OCI8 扩展当前不支持 NCLOB。
• BFILE，用于引用存在于操作系统的文件系统中的外部文件

SELECT DISTINCT <lob_type>
ORDER BY <lob_type>
GROUP BY <lob_col>

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

ALTER DATABASE CHARACTER SET UTF8

<?php 
foreach (get_class_methods('OCI-Lob') as $method ) {
    print "OCI-Lob::$method()\n";
}
?>

OCI-Lob::load()
OCI-Lob::tell()
OCI-Lob::truncate()
OCI-Lob::erase()
OCI-Lob::flush()
OCI-Lob::setbuffering()
OCI-Lob::getbuffering()
OCI-Lob::rewind()
OCI-Lob::read()
OCI-Lob::eof()
OCI-Lob::seek()
OCI-Lob::write()
OCI-Lob::append()
OCI-Lob::size()
OCI-Lob::writetofile()
OCI-Lob::writetemporary()
OCI-Lob::close()
OCI-Lob::save()
OCI-Lob::savefile()
OCI-Lob::free()

CREATE SEQUENCE mylobs_id_seq
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER
INCREMENT BY 1;

CREATE TABLE mylobs (
    id NUMBER PRIMARY KEY,
    mylob CLOB
)

<?php 
// connect to DB etc...

$sql = "INSERT INTO
        mylobs
          (
id,
            mylob
          )
       VALUES
          (
            mylobs_id_seq.NEXTVAL,
            --Initialize as an empty CLOB
            EMPTY_CLOB()
          )
       RETURNING
          --Return the LOB locator
          mylob INTO :mylob_loc";

$stmt = oci_parse($conn, $sql);

// Creates an "empty" OCI-Lob object to bind to the locator
$myLOB = oci_new_descriptor($conn, OCI_D_LOB);

// Bind the returned Oracle LOB locator to the PHP LOB object
oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_CLOB);

// Execute the statement using , OCI_DEFAULT - as a transaction
oci_execute($stmt, OCI_DEFAULT)
    or die ("Unable to execute query\n");
    
// Now save a value to the LOB
if ( !$myLOB->save('INSERT: '.date('H:i:s',time())) ) {
    
    // On error, rollback the transaction
    oci_rollback($conn);
    
} else {

    // On success, commit the transaction
    oci_commit($conn);
    
}

// Free resources
oci_free_statement($stmt);
$myLOB->free();


// disconnect from DB etc.
?>

oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_BLOB);

<?php 
// etc.

$sql = "INSERT INTO
          mylobs
          (
id,
            mylob
          )
        VALUES
          (
            mylobs_id_seq.NEXTVAL,
:string
          )
";

$stmt = oci_parse($conn, $sql);

$string = 'INSERT: '.date('H:i:s',time());

oci_bind_by_name($stmt, ':string', $string);

oci_execute($stmt)
    or die ("Unable to execute query\n");

// etc.
?>

<?php 
// etc.

$sql = "SELECT
          *
FROM
          mylobs
        ORDER BY
Id
";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt)
    or die ("Unable to execute query\n");

while ( $row = oci_fetch_assoc($stmt) ) {
    print "ID: {$row['ID']}, ";
    
    // Call the load() method to get the contents of the LOB
    print $row['MYLOB']->load()."\n";
}

// etc.
?>

while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {
    print "ID: {$row['ID']}, {$row['MYLOB']}\n";
}

<?php 
// etc.

$sql = "SELECT
           mylob
FROM
           mylobs
WHERE
           id = 3
        FOR UPDATE /* locks the row */
";

$stmt = oci_parse($conn, $sql);

// Execute the statement using OCI_DEFAULT (begin a transaction)
oci_execute($stmt, OCI_DEFAULT) 
    or die ("Unable to execute query\n");

// Fetch the SELECTed row
if ( FALSE === ($row = oci_fetch_assoc($stmt) ) ) {
    oci_rollback($conn);
    die ("Unable to fetch row\n");
}

// Discard the existing LOB contents
if ( !$row['MYLOB']->truncate() ) {
    oci_rollback($conn);
    die ("Failed to truncate LOB\n");
}

// Now save a value to the LOB
if ( !$row['MYLOB']->save('UPDATE: '.date('H:i:s',time()) ) ) {
    
    // On error, rollback the transaction
    oci_rollback($conn);
    
} else {

    // On success, commit the transaction
    oci_commit($conn);
    
}

// Free resources
oci_free_statement($stmt);
$row['MYLOB']->free();


// etc.
?>

$sql = "UPDATE
           mylobs
SET
            mylob = EMPTY_CLOB()
WHERE
           id = 2403
        RETURNING
            mylob INTO :mylob
";

$stmt = OCIParse($conn, $sql);

$mylob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':mylob',$mylob, -1, OCI_B_CLOB);

// Execute the statement using OCI_DEFAULT (begin a transaction)
OCIExecute($stmt, OCI_DEFAULT) 
    or die ("Unable to execute query\n");
    
if ( !$mylob->save( 'UPDATE: '.date('H:i:s',time()) ) ) {
    
    OCIRollback($conn);
    die("Unable to update lob\n");
    
}

OCICommit($conn);
$mylob->free();
OCIFreeStatement($stmt);

ALTER TABLE mylobs ADD( mybfile BFILE )

CREATE DIRECTORY IMAGES_DIR AS '/home/harryf/public_html/images'
GRANT READ ON DIRECTORY IMAGES_DIR TO scott

<?php 
// etc.

// Build an INSERT for the BFILE names
$sql = "INSERT INTO
        mylobs
          (
id,
            mybfile
          )
       VALUES
          (
            mylobs_id_seq.NEXTVAL,
            /*
            Pass the file name using the Oracle directory reference
            I created called IMAGES_DIR
            */
            BFILENAME('IMAGES_DIR',:filename)
          )";

$stmt = oci_parse($conn, $sql);

// Open the directory
$dir = '/home/harryf/public_html/images';
$dh = opendir($dir)
    or die("Unable to open $dir");

// Loop through the contents of the directory
while (false !== ( $entry = readdir($dh) ) ) {
    
    // Match only files with the extension .jpg, .gif or .png
    if ( is_file($dir.'/'.$entry) && preg_match('/\.(jpg|gif|png)$/',$entry) ) {
        
        // Bind the filename of the statement
        oci_bind_by_name($stmt, ":filename", $entry);
        
        // Execute the statement
        if ( oci_execute($stmt) ) {
            print "$entry added\n";
        }        
    }
    
}

<?php 
// etc.

$sql = "SELECT
id
FROM
          mylobs
WHERE
          -- Select only BFILES which are not null
          mybfile IS NOT NULL;

$stmt1 = oci_parse($conn, $sql);

oci_execute($stmt1)
    or die ("Unable to execute query\n");

$sql = "DECLARE
          locator BFILE;
          diralias VARCHAR2(30);
          filename VARCHAR2(30);
           
BEGIN
          
SELECT
            mybfile INTO locator
FROM
            mylobs
WHERE
            id = :id;
          
          -- Get the filename from the BFILE
          DBMS_LOB.FILEGETNAME(locator, diralias, filename);
          
          -- Assign OUT params to bind parameters
          :diralias:=diralias;
          :filename:=filename;
          
END;";

$stmt2 = oci_parse($conn, $sql);

while ( $row = oci_fetch_assoc ($stmt1) ) {
    
    oci_bind_by_name($stmt2, ":id", $row['ID']);
    oci_bind_by_name ($stmt2, ":diralias", $diralias,30);
    oci_bind_by_name ($stmt2, ":filename", $filename,30);
    
    oci_execute($stmt2);
    print "{$row['ID']}: $diralias/$filename\n";
    
}
// etc.
?>

将您的意见发送给我们