We all know that VARCHAR2 is also the type recommended by Oracle. But there is a problem with using VARCHAR2: it can only represent a maximum of 4000 characters, which is equivalent to 2000 Chinese characters. If the value of a certain character in your program is greater than 20,002 Chinese characters, VARCHAR2 cannot meet the requirements. At this time, you have two options, one is to use multiple VARCHAR2 to represent it, and the other is to use LOB fields. Here we take a look at the second method.
First, let’s have a general understanding of Oracle’s LOB fields. Oracle's LOB types are divided into three types: BLOB, CLOB and BFILE. CLOB is called character LOB, BLOB and BFILE are used to store binary data. The maximum length of CLOB and BLOB is 4GB, and they store values in the Oracle database. BFILE is similar to BLOB, but it places data in an external file, so it is also called external BLOB (External BLOB).
I think we are all familiar with MYSQL. There are similar data types in MYSQL, such as TEXT and BLOB. In PHP's MYSQL function, operations on TEXT/BLOB are direct, just like other types of data. But in Oracle, the situation is different. Oracle treats LOBs as a special data type and cannot use conventional methods for operations. For example, you cannot directly insert values into LOB fields in the INSERT statement, nor can you use LIKE to search.
The following uses several examples of PHP management of Oracle LOB data to illustrate how to use PHP's OCI function to insert, retrieve and query LOB data.
Insert
You cannot directly use the INSERT statement to insert values into LOB fields. Generally, there are the following steps:
1. First analyze an INSERT statement and return a LOB descriptor
2. Use the OCI function to generate a local LOB object
3. Bind the LOB object to the LOB descriptor
4. Execute the INSERT statement
5. Assign a value to the LOB object
6. Release the LOB object and SQL statement handle
The following example of PHP managing Oracle LOB data is to store image files uploaded by users into BLOB (or BFILE, the operation is slightly different). First, create a table with the following structure:
<ol class="dp-xml"> <li class="alt"><span><span>CREATE TABLE PICTURES ( </span></span></li> <li><span>ID NUMBER, </span></li> <li class="alt"><span>DESCRIPTION VARCHAR2(100), </span></li> <li><span>MIME VARCHAR2(128), </span></li> <li class="alt"><span>PICTURE BLOB </span></li> <li><span>); </span></li> </ol>
If you want to realize the automatic increase of ID, create another SEQUENCE:
CREATE SEQUENCE PIC_SEQ;
Then there is the PHP program code used to process the data.
<ol class="dp-xml">
<li class="alt"><span><span>＜ ?php </span></span></li>
<li><span>//建立Oracle数据库连接 </span></li>
<li class="alt">
<span>$</span><span class="attribute">conn</span><span> = </span><span class="attribute-value">OCILogon</span><span>($user, $password, $SID); </span>
</li>
<li><span>//提交SQL语句给Oracle </span></li>
<li class="alt"><span>//在这里要注意的两点：一是用EMPTY_BLOB()函数。这是Oracle的内部函数，<br>返回一个LOB的定位符。在插入LOB时，只能用这个办法先 生成一个空的LOB定<br>位符，然后对这个定位符进行操作。EMPTY_BLOB()函数是针对BLOB类型的，<br>对应于CLOB的是 EMPTY_CLOB()。二是RETURNING后面的部分，把picture<br>返回，让PHP的OCI函数能够处理。 </span></li>
<li>
<span>$</span><span class="attribute">stmt</span><span> = </span><span class="attribute-value">OCIParse</span><span>($conn,”INSERT INTO PICTURES (id, description, picture) </span>
</li>
<li class="alt"><span>VALUES (pic_seq.NEXTVAL, ‘$description’, ‘$lob_upload_type’<br>, EMPTY_BLOB()) RETURNING picture INTO ICTURE”); </span></li>
<li><span>//生成一个本地LOB对象的描述符。注意函数的第二个参数：OCI_D_LOB，<br>表示生成一个LOB对象。其它可能的还有OCI_D_FILE和OCI_D_ROWID，<br>分别对应于BFILE和ROWID对象。 </span></li>
<li class="alt">
<span>$</span><span class="attribute">lob</span><span> = </span><span class="attribute-value">OCINewDescriptor</span><span>($conn, OCI_D_LOB); </span>
</li>
<li><span>//将生成的LOB对象绑定到前面SQL语句返回的定位符上。 </span></li>
<li class="alt"><span>OCIBindByName($stmt, ‘:PICTURE’, &$lob, -1, OCI_B_BLOB); </span></li>
<li><span>OCIExecute($stmt); </span></li>
<li class="alt"><span>//向LOB对象中存入数据。因为这里的源数据是一个文件，所以直接用LOB<br>对象的savefile()方法。LOB对象的其它方法还有：save()和load()，<br>分别用来保存和取出数据。但BFILE类型只有一个方法就是save() </span></li>
<li><span>if($lob-＞savefile($lob_upload)){ </span></li>
<li class="alt"><span>OCICommit($conn); </span></li>
<li><span>echo “上传成功＜ br＞”; </span></li>
<li class="alt"><span>}else{ </span></li>
<li><span>echo “上传失败＜ br＞”; </span></li>
<li class="alt"><span>} </span></li>
<li><span>//释放LOB对象 </span></li>
<li class="alt"><span>OCIFreeDesc($lob); </span></li>
<li><span>OCIFreeStatement($stmt); </span></li>
<li class="alt"><span>OCILogoff($conn); </span></li>
<li><span>?＞ </span></li>
</ol>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
PHP manages Oracle LOB data. 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.
<ol class="dp-xml">
<li class="alt"><span><span>＜ ?php </span></span></li>
<li>
<span>$</span><span class="attribute">conn</span><span> = </span><span class="attribute-value">OCILogon</span><span>($user, $password, $SID); </span>
</li>
<li class="alt">
<span>$</span><span class="attribute">stmt</span><span> = </span><span class="attribute-value">OCIParse</span><span>($conn,”SELECT * <br>FROM PICTURES WHERE </span><span class="attribute">ID</span><span>=$pictureid”); </span>
</li>
<li><span>OCIExecute($stmt); </span></li>
<li class="alt"><span>//秘密就在PCIFetchInfo的第三个参数上：<br>OCI_RETURN_LOBS。第三个参数是FETCH的模式，<br>如果OCI_RETURN_LOBS，就直接把LOB的值放到结<br>果数组中，而不是LOB定位符，也就不用LOB对象的load()方法了。 </span></li>
<li><span>if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS)) </span></li>
<li class="alt"><span>{ </span></li>
<li><span>echo “Content-type: ” . StripSlashes($result[MIME]); </span></li>
<li class="alt"><span>echo StripSlashes($result[PICTURE]); </span></li>
<li><span>} </span></li>
<li class="alt"><span>OCIFreeStatement($stmt); </span></li>
<li><span>OCILogoff($conn); </span></li>
<li class="alt"><span>?＞ </span></li>
</ol>This program is used to display data (pictures) placed in LOB. Calling method (assuming the script name is getpicture.php):
<ol class="dp-xml"><li class="alt"><span><span>＜ IMG </span><span class="attribute">SRC</span><span>=”getpicture.php?</span><span class="attribute">pictureid</span><span>=</span><span class="attribute-value">99</span><span>″ <br></span><span class="attribute">ALT</span><span>=”放在Oracle LOB中的图片”＞ </span></span></li></ol>
Query
As mentioned before, PHP manages Oracle LOB Data cannot be matched using LIKE for 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:
<ol class="dp-xml"> <li class="alt"><span><span>CREATE TABLE ARTICLES ( </span></span></li> <li><span>ID NUMBER, </span></li> <li class="alt"><span>TITLE VARCHAR2(100), </span></li> <li><span>CONTENT CLOB </span></li> <li class="alt"><span>); </span></li> </ol>
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. You can do this:
<ol class="dp-xml">
<li class="alt"><span><span>＜ ?php </span></span></li>
<li>
<span>$</span><span class="attribute">conn</span><span> = </span><span class="attribute-value">OCILogon</span><span>($user, $password, $SID); </span>
</li>
<li class="alt"><span>//WHERE子句中用了DBMS_LOB.INSTR过程。它有四个参数，<br>前面两个分别表示LOB的定位符（可以直接用字段表示）<br>和要查找的字符串；后面两个分别表示开始的偏移量和出现的次数。<br>要注意的是必须判断它的返回值，也就是要大于0。 </span></li>
<li>
<span>$</span><span class="attribute">stmt</span><span> = </span><span class="attribute-value">OCIParse</span><span>($conn,”SELECT * FROM ARTICLES <br>WHERE DBMS_LOB.INSTR(CONTENT, ‘PHP中文用户’, 1, 1) ＞ 0″); </span>
</li>
<li class="alt"><span>OCIExecute($stmt); </span></li>
<li><span>if (OCIFetchInto($stmt, $result, OCI_ASSOC+OCI_RETURN_LOBS)) </span></li>
<li class="alt"><span>{ </span></li>
<li><span>… </span></li>
<li class="alt"><span>} </span></li>
<li><span>OCIFreeStatement($stmt); </span></li>
<li class="alt"><span>OCILogoff($conn); </span></li>
<li><span>?＞ </span></li>
</ol><br> Oracle also provides many processes 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 PHP to manage Oracle LOB data. 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.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
