How to store and retrieve large object data using Oracle database in PHP

How to use Oracle database to store and retrieve large object data in PHP

Introduction
Oracle database is a powerful relational database management system that is widely used in enterprise-level application development. During the development process, it is often necessary to store and retrieve large object data, such as pictures, audio, and video. This article will introduce how to use Oracle database in PHP to process these large object data, with code examples.

1. Storage of large object data
In Oracle database, there are two main types of large object data: binary large object (BLOB) and character large object (CLOB). The following is a sample code for storing large object data:

<?php
// 连接Oracle数据库
$conn = oci_connect('username', 'password', 'hostname/service_name');

// 准备SQL语句
$sql = "INSERT INTO table_name (blob_column) VALUES (EMPTY_BLOB()) RETURNING blob_column INTO :blob_column";
$stmt = oci_parse($conn, $sql);

// 绑定变量
lob_var = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ':blob_column', $lob_var, -1, OCI_B_BLOB);

// 执行SQL语句
oci_execute($stmt, OCI_DEFAULT);

// 写入数据到LOB对象
$blob_data = file_get_contents('path/to/file');
$lob_var->save($blob_data);

// 提交事务
oci_commit($conn);

// 释放资源
oci_free_statement($stmt);
$lob_var->free();
oci_close($conn);

echo "大对象数据已成功存储到Oracle数据库中。";
?>

The above code first connects to the Oracle database, and then prepares a SQL statement, which uses the EMPTY_BLOB() function to create an empty BLOB object. Next, bind the LOB object to :blob_column in the SQL statement through the oci_bind_by_name() function, and use the oci_execute() function to execute the SQL statement.

To store the actual large object data into the database, you can use the file_get_contents() function to read the data from the file and the save() method to Data is written to LOB objects. Finally, use the oci_commit() function to commit the transaction, release the bound variables and close the database connection.

2. Retrieve large object data
Retrieval of large object data is similar to storing large object data, except that the SELECT statement is used in the SQL statement to retrieve data. The following is a sample code for retrieving large object data:

<?php
// 连接Oracle数据库
$conn = oci_connect('username', 'password', 'hostname/service_name');

// 准备SQL语句
$sql = "SELECT blob_column FROM table_name WHERE condition";
$stmt = oci_parse($conn, $sql);

// 执行SQL语句
oci_execute($stmt, OCI_DEFAULT);

// 检索LOB对象数据
if ($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    $lob_var = $row['BLOB_COLUMN'];
    $lob_data = $lob_var->load();

    // 处理大对象数据
    file_put_contents('path/to/file', $lob_data);
}

// 释放资源
oci_free_statement($stmt);
oci_close($conn);

echo "大对象数据已成功检索到本地文件中。";
?>

The above code first connects to the Oracle database, then prepares the SQL statement, and uses the oci_execute() function to execute the SQL statement. Next, use the oci_fetch_array() function to retrieve the data in the query result set and save the LOB object in the $lob_var variable.

Finally, use the load() method to load the data of the LOB object, and use the file_put_contents() function to save the data to a local file.

Conclusion
This article introduces how to use Oracle database in PHP to store and retrieve large object data. Through sample code, we can learn the basic methods of processing BLOB and CLOB type data in PHP. In actual development, the code can be modified and optimized according to needs. Hope this article is helpful to you!

The above is the detailed content of How to store and retrieve large object data using Oracle database in PHP. For more information, please follow other related articles on the PHP Chinese website!