Use PHP to manipulate Oracle LOB type data_PHP tutorial

文章来源:IT计算机教程

用过Oracle的人都知道，Oracle有一种数据类型叫VARCHAR2，用来表示不定长的字符串。VARCHAR2也是Oracle公司推荐使用的类型。但使用VARCHAR2有个问题：最大只能表示4000个字符，也就相当于2000个汉字。如果你的程序中某个字符的值要大于20002个汉字，用VARCHAR2就不能满足要求了。这时候，你有两个选择，一是用多个VARCHAR2来表示，二是用LOB字段。这里我们来看看第二个办法。

　　先来大体了解一下Oracle的LOB字段。Oracle的LOB类型分为三种：BLOB,CLOB和BFILE。CLOB称为字符LOB，BLOB和BFILE是用来存储二进制数据的。CLOB和BLOB的最大长度是4GB，它们把值存放在Oracle数据库中。BFILE和BLOB类似，但它把数据放在外部的文件中，所以它又称为外部BLOB（External BLOB）。

　　我想，我们对MYSQL应该都不会陌生。MYSQL中也有类似的数据类型，如TEXT和BLOB。在PHP的MYSQL函数中，对TEXT/BLOB的操作是直接的，就象其它类型的数据一样。但在Oracle中，情况就不一样了。Oracle把LOB当作一种特殊的数据类型来处理，在操作上不能用常规的方法。比如，不能在INSERT语句中直接把值插入到LOB字段中，也不能用LIKE进行查找。

　　下面就通过几个例子来说明如何用PHP的OCI函数来插入，取出和查询LOB数据。

　　插入

　　不能直接用INSERT语句向LOB字段中插入值。一般情况下，有如下的几步：

　　1、先分析一个INSERT语句，返回一个LOB的描述符

　　2、用OCI函数生成一个本地的LOB对象

　　3、将LOB对象绑定到LOB描述符上

　　4、执行INSERT语句

　　5、给LOB对象赋值

　　6、释放LOB对象和SQL语句句柄

　　下面的这个例子是把用户上传的图片文件存放到BLOB（或BFILE中，操作稍有不同）中。首先要建一个表，结构如下：

CREATE TABLE PICTURES (
ID NUMBER,
DESCRIPTION VARCHAR2(100),
MIME VARCHAR2(128),
PICTURE BLOB
);

　　如果要实现ID的自动增加，再建一个SEQUENCE:

CREATE SEQUENCE PIC_SEQ;

　　然后是用来处理数据的PHP程序代码。

＜?php

　//建立Oracle数据库连接

　$conn = OCILogon($user, $password, $SID);

　//提交SQL语句给Oracle
　//在这里要注意的两点：一是用EMPTY_BLOB()函数。这是Oracle的内部函数，返回一个LOB的定位符。在插入LOB时，只能用这个办法先生成一个空的LOB定位符，然后对这个定位符进行操作。EMPTY_BLOB()函数是针对BLOB类型的，对应于CLOB的是EMPTY_CLOB()。二是RETURNING后面的部分，把picture返回，让PHP的OCI函数能够处理。

　$stmt = OCIParse($conn,"INSERT INTO PICTURES (id, description, picture)
VALUES (pic_seq.NEXTVAL, $description, $lob_upload_type, EMPTY_BLOB()) RETURNING picture INTO :PICTURE");

　//生成一个本地LOB对象的描述符。注意函数的第二个参数：OCI_D_LOB，表示生成一个LOB对象。其它可能的还有OCI_D_FILE和OCI_D_ROWID，分别对应于BFILE和ROWID对象。

　$lob = OCINewDescriptor($conn, OCI_D_LOB);

　//将生成的LOB对象绑定到前面SQL语句返回的定位符上。

　OCIBindByName($stmt, :PICTURE, &$lob, -1, OCI_B_BLOB);
　OCIExecute($stmt);

　//向LOB对象中存入数据。因为这里的源数据是一个文件，所以直接用LOB对象的savefile()方法。LOB对象的其它方法还有：save()和load()，分别用来保存和取出数据。但BFILE类型只有一个方法就是save()

　if($lob-＞savefile($lob_upload)){
　　OCICommit($conn);
　　echo "上传成功＜br＞";
　}else{
　　echo "上传失败＜br＞";
　}

　//释放LOB对象

　OCIFreeDesc($lob);
　OCIFreeStatement($stmt);
　OCILogoff($conn);
?＞

There is another thing to note: the value of the LOB field must be at least 1 character, so before save() or savefile(), make sure the value cannot be empty. Otherwise, Oracle will make an error.

Retrieve

There are two ways to retrieve data from a LOB. One is to generate a LOB object, then bind it to the locator returned by a SELECT statement, and then use the load() method of the LOB object to retrieve the data; the other is to directly use PHP's OCIFetch*** function. The first method is much more troublesome than the second method, so I will talk about the second method directly.

Still use the table above.

＜?php

＜?php
　$conn = OCILogon($user, $password, $SID);
　$stmt = OCIParse($conn,"SELECT * FROM PICTURES WHERE ID=$pictureid");
　OCIExecute($stmt);

　//秘密就在PCIFetchInfo的第三个参数上：OCI_RETURN_LOBS。第三个参数是FETCH的模式，如果OCI_RETURN_LOBS，就直接把LOB的值放到结果数组中，而不是LOB定位符，也就不用LOB对象的load()方法了。

　if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS))
　{
　　echo "Content-type: " . StripSlashes($result[MIME]);
　　echo StripSlashes($result[PICTURE]);
　}
　OCIFreeStatement($stmt);
　OCILogoff($conn);
?＞

$conn = OCILogon($user, $password, $SID);
$stmt = OCIParse($conn,"SELECT * FROM PICTURES WHERE ID=$pictureid");
OCIExecute($stmt);

＜IMG SRC="getpicture.php?pictureid=99" ALT="放在Oracle LOB中的图片"＞

//The secret lies in the third parameter of PCIFetchInfo: OCI_RETURN_LOBS. The third parameter is the FETCH mode. If OCI_RETURN_LOBS, the LOB value is directly put into the result array instead of the LOB locator, so the load() method of the LOB object is not needed.
if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS)) {
echo "Content-type: " . StripSlashes($result[MIME]);
echo StripSlashes ($result[PICTURE]);
}
OCIFreeStatement($stmt);
OCILogoff($conn);
?＞

CREATE TABLE ARTICLES (
　ID NUMBER,
　TITLE VARCHAR2(100),
　CONTENT CLOB
);

This program is used to display data (pictures) placed in LOB. Calling method (assuming the script name is getpicture.php):



　
Query

＜?php
　$conn = OCILogon($user, $password, $SID);

　//WHERE子句中用了DBMS_LOB.INSTR过程。它有四个参数，前面两个分别表示LOB的定位符（可以直接用字段表示）和要查找的字符串；后面两个分别表示开始的偏移量和出现的次数。要注意的是必须判断它的返回值，也就是要大于0。

　$stmt = OCIParse($conn,"SELECT * FROM ARTICLES WHERE DBMS_LOB.INSTR(CONTENT, PHP中文用户, 1, 1) ＞ 0");
　OCIExecute($stmt);
　if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS))
　{
　　...
　}
　OCIFreeStatement($stmt);
　OCILogoff($conn);
?＞

As mentioned before, LIKE cannot be used to match Oracle's LOB fields. What to do? In fact, it is not complicated. Oracle has an anonymous package called DBMS_LOB, which contains all the processes required to operate LOB.
Suppose there is a table like this:

CREATE TABLE ARTICLES (

ID NUMBER, TITLE VARCHAR2(100), CONTENT CLOB );

The content of the article is placed in the CONTENT field. Now we want to find all the articles that contain "PHP Chinese users" in the content. We can do it like this:

＜?php 　$conn = OCILogon($user, $password, $SID); 　//The DBMS_LOB.INSTR procedure is used in the WHERE clause. It has four parameters. The first two represent the LOB locator (which can be directly represented by a field) and the string to be searched for; the latter two represent the starting offset and the number of occurrences. It should be noted that its return value must be judged, that is, it must be greater than 0. 　$stmt = OCIParse($conn,"SELECT * FROM ARTICLES WHERE DBMS_LOB.INSTR(CONTENT, PHP Chinese users, 1, 1) ＞ 0"); OCIExecute($stmt); if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS))

{

... } OCIFreeStatement($stmt); OCILogoff($conn); ?＞

Oracle also provides many procedures for operating LOB data, such as LENGTH, SUBSTR, etc. As for their detailed usage, you can consider Oracle's development manual. That’s all about the operation of LOB type data in Oracle database. Since I haven't been in contact with Oracle for a long time, there may be errors in this article. Everyone is welcome to criticize and correct me. http://www.bkjia.com/PHPjc/532663.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/532663.htmlTechArticleArticle Source: IT Computer Tutorial Anyone who has used Oracle knows that Oracle has a data type called VARCHAR2, which is used to Represents a string of variable length. VARCHAR2 is also a class recommended by Oracle...