overloadCVII. Oracle 函数
简介
本类函数使用 Oracle Call Interface（OCI）使用户可以访问 Oracle 10，Oracle9，Oracle8 和 Oracle7 数据库。支持将 PHP 变量与 Oracle 占位符（placeholder）绑定，具有完整的 LOB，FILE 和 ROWID 支持，以及允许使用用户提供的定义变量。
需求
使用本扩展需要 Oracle 客户端库。Windows 用户需要至少版本号为 10 的库才能使用 php_oci8.dll。
安装所有所需文件最方便的方法是使用 Oracle Instant Client，可以从此处得到：» http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html。要使 OCI8 模块能工作，"basic" 版的 Oracle Instant Client 已经足够。Instant Client 不需要 ORACLE_SID 或 ORACLE_HOME 环境变量被设定。不过可能还是要设定 LD_LIBRARY_PATH 和 NLS_LANG。
在使用本扩展之前，请确认已经为 Oracle 用户和 web daemon 用户正确设置了 Oracle 环境变量。这些变量应该在启动 web server 之前设定。下面列出了需要设置的环境变量：
- ORACLE_HOME
- ORACLE_SID
- LD_PRELOAD
- LD_LIBRARY_PATH
- NLS_LANG
在为 web 服务器用户设置环境变量之后，还需要将 web 服务器用户（nobody，www）加到 oracle 组中。
I如果 web 服务器不能够启动或者在启动的时候崩溃: 检查 Apache 是否连接了 pthread 库：
# ldd /www/apache/bin/httpd libpthread.so.0 => /lib/libpthread.so.0 (0x4001c000) libm.so.6 => /lib/libm.so.6 (0x4002f000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x4004c000) libdl.so.2 => /lib/libdl.so.2 (0x4007a000) libc.so.6 => /lib/libc.so.6 (0x4007e000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)如果 libpthread 没有列出，必需重新安装 Apache：
# cd /usr/src/apache_1.3.xx # make clean # LIBS=-lpthread ./config.status # make # make install请注意在像 UnixWare 之类的某些操作系统中，使用 libthread 代替了 libpthread。则 PHP 和 Apache 必须使用 EXTRA_LIBS=-lthread 配置。
运行时配置
这些函数的行为受 php.ini 的影响。
表208.OCI8 Configuration Options
| Name | Default | Changeable | Changelog |
|---|---|---|---|
| oci8.privileged_connect | "0" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.max_persistent | "-1" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.persistent_timeout | "-1" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.ping_interval | "60" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.statement_cache_size | "20" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.default_prefetch | "10" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
| oci8.old_oci_close_semantics | "0" | PHP_INI_SYSTEM | Available since PHP 5.1.2. |
以下是配置选项的简要解释。
- oci8.privileged_connect boolean
This option enables privileged connections using external credentials (OCI_SYSOPER, OCI_SYSDBA).
- oci8.max_persistent int
The maximum number of persistent OCI8 connections per process. Setting this option to -1 means that there is no limit.
- oci8.persistent_timeout int
The maximum length of time (in seconds) that a given process is allowed to maintain an idle persistent connection. Setting this option to -1 means that idle persistent connections will be maintained forever.
- oci8.ping_interval int
The length of time (in seconds) that must pass before issuing a ping during oci_pconnect(). When set to 0, persistent connections will be pinged every time they are reused. To disable pings completely, set this option to -1.
注意: Disabling pings will cause oci_pconnect() calls to operate at the highest efficiency, but may cause PHP to not detect faulty connections, such as those caused by network partitions, or if the Oracle server has gone down since PHP connected, until later in the script. Consult the oci_pconnect() documentation for more information.
- oci8.statement_cache_size int
This option enables statement caching, and specifies how many statements to cache. To disable statement caching just set this option to 0.
注意: A larger cache can result in improved performance, at the cost of increased memory usage.
- oci8.default_prefetch int
This option enables statement prefetching and sets the default number of rows that will be fetched automatically after statement execution.
注意: A larger prefetch can result in improved performance, at the cost of increased memory usage.
- oci8.old_oci_close_semantics boolean
This option controls oci_close() behaviour. Enabling it means that oci_close() will do nothing; the connection will not be closed until the end of the script. This is for backward compatibility only. If you find that you need to enable this setting, you are strongly encouraged to remove the oci_close() calls from your application instead of enabling this option.
预定义常量
以下常量由本扩展模块定义，因此只有在本扩展模块被编译到 PHP 中，或者在运行时被动态加载后才有效。
- OCI_DEFAULT (integer)
- Statement execution mode. Statement is not committed automatically when using this mode.
- OCI_DESCRIBE_ONLY (integer)
- Statement execution mode. Use this mode if you don't want to execute the query, but get the select-list's description.
- OCI_COMMIT_ON_SUCCESS (integer)
- Statement execution mode. Statement is automatically committed after oci_execute() call.
- OCI_EXACT_FETCH (integer)
- Statement fetch mode. Used when the application knows in advance exactly how many rows it will be fetching. This mode turns prefetching off for Oracle release 8 or later mode. Cursor is cancelled after the desired rows are fetched and may result in reduced server-side resource usage.
- OCI_SYSDATE (integer)
- OCI_B_BFILE (integer)
- Used with oci_bind_by_name() when binding BFILEs.
- OCI_B_CFILEE (integer)
- Used with oci_bind_by_name() when binding CFILEs.
- OCI_B_CLOB (integer)
- Used with oci_bind_by_name() when binding CLOBs.
- OCI_B_BLOB (integer)
- Used with oci_bind_by_name() when binding BLOBs.
- OCI_B_ROWID (integer)
- Used with oci_bind_by_name() when binding ROWIDs.
- OCI_B_CURSOR (integer)
- Used with oci_bind_by_name() when binding cursors, previously allocated with oci_new_descriptor().
- OCI_B_NTY (integer)
- Used with oci_bind_by_name() when binding named data types. Note: in PHP < 5.0 it was called OCI_B_SQLT_NTY.
- OCI_B_BIN (integer)
- SQLT_BFILEE (integer)
- The same as OCI_B_BFILE.
- SQLT_CFILEE (integer)
- The same as OCI_B_CFILEE.
- SQLT_CLOB (integer)
- The same as OCI_B_CLOB.
- SQLT_BLOB (integer)
- The same as OCI_B_BLOB.
- SQLT_RDD (integer)
- The same as OCI_B_ROWID.
- SQLT_NTY (integer)
- The same as OCI_B_NTY.
- SQLT_LNG (integer)
- Used with oci_bind_by_name() to bind LONG values.
- SQLT_LBI (integer)
- Used with oci_bind_by_name() to bind LONG RAW values.
- SQLT_BIN (integer)
- Used with oci_bind_by_name() to bind RAW values.
- SQLT_NUM (integer)
- Used with oci_bind_array_by_name() to bind arrays of NUMBER.
- SQLT_INT (integer)
- Used with oci_bind_array_by_name() to bind arrays of INTEGER.
- SQLT_AFC (integer)
- Used with oci_bind_array_by_name() to bind arrays of CHAR.
- SQLT_CHR (integer)
- Used with oci_bind_array_by_name() to bind arrays of VARCHAR2. Also used with oci_bind_by_name().
- SQLT_VCS (integer)
- Used with oci_bind_array_by_name() to bind arrays of VARCHAR.
- SQLT_AVC (integer)
- Used with oci_bind_array_by_name() to bind arrays of CHARZ.
- SQLT_STR (integer)
- Used with oci_bind_array_by_name() to bind arrays of STRING.
- SQLT_LVC (integer)
- Used with oci_bind_array_by_name() to bind arrays of LONG VARCHAR.
- SQLT_FLT (integer)
- Used with oci_bind_array_by_name() to bind arrays of FLOAT.
- SQLT_ODT (integer)
- Used with oci_bind_array_by_name() to bind arrays of LONG.
- SQLT_BDOUBLE (integer)
- SQLT_BFLOAT (integer)
- OCI_FETCHSTATEMENT_BY_COLUMN (integer)
- Default mode of oci_fetch_all().
- OCI_FETCHSTATEMENT_BY_ROW (integer)
- Alternative mode of oci_fetch_all().
- OCI_ASSOC (integer)
- Used with oci_fetch_all() and oci_fetch_array() to get an associative array as a result.
- OCI_NUM (integer)
- Used with oci_fetch_all() and oci_fetch_array() to get an enumerated array as a result.
- OCI_BOTH (integer)
- Used with oci_fetch_all() and oci_fetch_array() to get an array with both associative and number indices.
- OCI_RETURN_NULLS (integer)
- Used with oci_fetch_array() to get empty array elements if field's value is NULL.
- OCI_RETURN_LOBS (integer)
- Used with oci_fetch_array() to get value of LOB instead of the descriptor.
- OCI_DTYPE_FILE (integer)
- This flag tells oci_new_descriptor() to initialize new FILE descriptor.
- OCI_DTYPE_LOB (integer)
- This flag tells oci_new_descriptor() to initialize new LOB descriptor.
- OCI_DTYPE_ROWID (integer)
- This flag tells oci_new_descriptor() to initialize new ROWID descriptor.
- OCI_D_FILE (integer)
- The same as OCI_DTYPE_FILE.
- OCI_D_LOB (integer)
- The same as OCI_DTYPE_LOB.
- OCI_D_ROWID (integer)
- The same as OCI_DTYPE_ROWID.
- OCI_SYSOPER (integer)
- Used with oci_connect() to connect as SYSOPER using external credentials (oci8.privileged_connect should be enabled for this).
- OCI_SYSDBA (integer)
- Used with oci_connect() to connect as SYSDBA using external credentials (oci8.privileged_connect should be enabled for this).
- OCI_LOB_BUFFER_FREE (integer)
- Used with OCI-Lob->flush to free buffers used.
- OCI_TEMP_CLOB (integer)
- Used with OCI-Lob->writeTemporary to indicate explicilty that temporary CLOB should be created.
- OCI_TEMP_BLOB (integer)
- Used with OCI-Lob->writeTemporary to indicate explicilty that temporary BLOB should be created.
范例
例1517.基本查询
<?php
$conn = oci_connect('hr', 'hr', 'orcl');
if (!$conn) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}
$query = 'SELECT * FROM DEPARTMENTS';
$stid = oci_parse($conn, $query);
if (!$stid) {
$e = oci_error($conn);
print htmlentities($e['message']);
exit;
}
$r = oci_execute($stid, OCI_DEFAULT);
if(!$r) {
$e = oci_error($stid);
echo htmlentities($e['message']);
exit;
}
print '<table border="1">';
while($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {
print '<tr>';
foreach($row as $item) {
print '<td>'.($item?htmlentities($item):' ').'</td>';
}
print '</tr>';
}
print '</table>';
oci_close($conn);
?>
例1518.用绑定变量插入
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mid NUMBER, myd VARCHAR2(20));
$conn = oci_connect('scott', 'tiger', 'orcl');
$query = 'INSERT INTO MYTABLE VALUES(:myid, :mydata)';
$stid = oci_parse($conn, $query);
$id = 60;
$data = 'Some data';
oci_bind_by_name($stid, ':myid', $id);
oci_bind_by_name($stid, ':mydata', $data);
$r = oci_execute($stid);
if($r)
print "One row inserted";
oci_close($conn);
?>
例1519.将数据插入到 CLOB 列中
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mykey NUMBER, myclob CLOB);
$conn = oci_connect('scott', 'tiger', 'orcl');
$mykey = 12343; // arbitrary key for this example;
$sql = "INSERT INTO mytable (mykey, myclob)
VALUES (:mykey, EMPTY_CLOB())
RETURNING myclob INTO :myclob";
$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");
oci_commit($conn);
// Fetching CLOB data
$query = 'SELECT myclob FROM mytable WHERE mykey = :mykey';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid, OCI_DEFAULT);
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$result = $row['MYCLOB']->load();
print '<tr><td>'.$result.'</td></tr>';
}
print '</table>';
?>
可以很容易地访问存储过程，就和从命令行访问一样。
例1520.使用存储过程
<?php
// by webmaster at remoterealty dot com
$sth = oci_parse($dbh, "begin sp_newaddress( :address_id, '$firstname',
'$lastname', '$company', '$address1', '$address2', '$city', '$state',
'$postalcode', '$country', :error_code );end;");
// This calls stored procedure sp_newaddress, with :address_id being an
// in/out variable and :error_code being an out variable.
// Then you do the binding:
oci_bind_by_name($sth, ":address_id", $addr_id, 10);
oci_bind_by_name($sth, ":error_code", $errorcode, 10);
oci_execute($sth);
?>
连接处理
OCI8 扩展提供了 3 个不同函数来连接 Oracle。取决于用户来使用对自己的应用程序最合适的函数。本节的信息有助于用户作出合适的选择。
连接到 Oracle 服务器从所需的时间上来讲是个相当花费的操作。oci_pconnect() 函数使用了一个连接的持久缓冲区，可以被不同的脚本请求重复使用。这意味着通常在每个 PHP 进程（或 Apache 子进程）中只需要连接一次。
如果应用程序连接 Oracle 时对每个 web 用户都使用了不同的认证信息，则由 oci_pconnect() 使用的持久缓冲区就用处不大了，因为随着并发用户的增加，到某个程度后会由于要保持太多的空闲连接而对 Oracle 服务器的整体性能起到逆反的影响。如果应用程序是这样的架构，建议要么用 oci8.max_persistent 和 oci8.persistent_timeout 配置选项（此二者可以使用户控制持久连接缓冲区的大小和生命周期）来协调应用程序，要么用 oci_connect() 来连接。
oci_connect() 和 oci_pconnect() 都使用了一个连接缓冲区。如果在某个脚本中用同样的参数多次调用 oci_connect()，则第二个和之后的调用会返回已有的连接句柄。oci_connect() 使用的连接缓冲区会在脚本执行完毕后或者明确地关闭了连接句柄时被清空。oci_pconnect() 有相似的行为，不过其缓冲区独立地维持着并在不同请求之间都存活着。
要记住此缓冲特性，因为它使两个句柄没有在事务级隔离开来（事实上是同一个连接句柄，因此没有任何方式的隔离）。如果应用程序需要两个独立的，事务级隔离的连接，应该使用 oci_new_connect()。
oci_new_connect() 总是创建一个到 Oracle 服务器的新连接，不管其它连接是否已经存在。高流量的 web 应用应该避免使用 oci_new_connect()，尤其是在程序最忙的部分。
驱动程序支持的数据类型
表209.用 oci_bind_by_name() 函数绑定参数时支持以下类型
| 类型 | 映射 |
|---|---|
| SQLT_NTY | 映射到一个来自 PHP collection 对象的本地 collection 类型，例如由 oci_new_collection() 创建的。 |
| SQLT_BFILEE | 映射到一个本地的 descriptor，例如由 oci_new_descriptor() 创建的。 |
| SQLT_CFILEE | 映射到一个本地的 descriptor，例如由 oci_new_descriptor() 创建的。 |
| SQLT_CLOB | 映射到一个本地的 descriptor，例如由 oci_new_descriptor() 创建的。 |
| SQLT_BLOB | 映射到一个本地的 descriptor，例如由 oci_new_descriptor() 创建的。 |
| SQLT_RDD | 映射到一个本地的 descriptor，例如由 oci_new_descriptor() 创建的。 |
| SQLT_NUM | 将 PHP 参数转换为 C 语言的 long 类型，并绑定为其值。 |
| SQLT_RSET | 映射到一个本地的 statement 句柄，例如由 oci_parse() 创建或从其它 OCI 查询取得的。 |
| SQLT_CHR 以及任何其它类型 | 将 PHP 参数转换为字符串类型并绑定为字符串。 |
表210.在从结果集中取得列时支持以下类型
| 类型 | 映射 |
|---|---|
| SQLT_RSET | 创建一个 oci statement 资源来代表指针。 |
| SQLT_RDD | 创建一个 ROWID 对象。 |
| SQLT_BLOB | 创建一个 LOB 对象。 |
| SQLT_CLOB | 创建一个 LOB 对象。 |
| SQLT_BFILE | 创建一个 LOB 对象。 |
| SQLT_LNG | 限制为 SQLT_CHR，返回为字符串。 |
| SQLT_LBI | 限制为 SQLT_BIN，返回为字符串。 |
| 任何其它类型 | 限制为 SQLT_CHR，返回为字符串。 |
目录
- oci_bind_array_by_name Binds PHP array to Oracle PL/SQL array by name
- oci_bind_by_name 绑定一个 PHP 变量到一个 Oracle 位置标志符
- oci_cancel 取消从游标读取数据
- oci_close 关闭 Oracle 连接
- OCI-Collection->append Appends element to the collection
- OCI-Collection->assign Assigns a value to the collection from another existing collection
- OCI-Collection->assignElem Assigns a value to the element of the collection
- OCI-Collection->free Frees the resources associated with the collection object
- OCI-Collection->getElem Returns value of the element
- OCI-Collection->max Returns the maximum number of elements in the collection
- OCI-Collection->size Returns size of the collection
- OCI-Collection->trim Trims elements from the end of the collection
- oci_commit 提交未执行的事务处理
- oci_connect 建立一个到 Oracle 服务器的连接
- oci_define_by_name 在 SELECT 中使用 PHP 变量作为定义的步骤
- oci_error 返回上一个错误
- oci_execute 执行一条语句
- oci_fetch_all 获取结果数据的所有行到一个数组
- oci_fetch_array Returns the next row from the result data as an associative or numeric array, or both
- oci_fetch_assoc Returns the next row from the result data as an associative array
- oci_fetch_object Returns the next row from the result data as an object
- oci_fetch_row Returns the next row from the result data as a numeric array
- oci_fetch Fetches the next row into result-buffer
- oci_field_is_null 检查字段是否为 NULL
- oci_field_name 返回字段名
- oci_field_precision 返回字段精度
- oci_field_scale 返回字段范围
- oci_field_size 返回字段大小
- oci_field_type_raw 返回字段的原始 Oracle 数据类型
- oci_field_type 返回字段的数据类型
- oci_free_statement 释放关联于语句或游标的所有资源
- oci_internal_debug 打开或关闭内部调试输出
- OCI-Lob->append Appends data from the large object to another large object
- OCI-Lob->close Closes LOB descriptor
- oci_lob_copy Copies large object
- OCI-Lob->eof Tests for end-of-file on a large object's descriptor
- OCI-Lob->erase Erases a specified portion of the internal LOB data
- OCI-Lob->export Exports LOB's contents to a file
- OCI-Lob->flush Flushes/writes buffer of the LOB to the server
- OCI-Lob->free Frees resources associated with the LOB descriptor
- OCI-Lob->getBuffering Returns current state of buffering for the large object
- OCI-Lob->import Imports file data to the LOB
- oci_lob_is_equal Compares two LOB/FILE locators for equality
- OCI-Lob->load Returns large object's contents
- OCI-Lob->read Reads part of the large object
- OCI-Lob->rewind Moves the internal pointer to the beginning of the large object
- OCI-Lob->save Saves data to the large object
- OCI-Lob->saveFile 别名 oci_lob_import()
- OCI-Lob->seek Sets the internal pointer of the large object
- OCI-Lob->setBuffering Changes current state of buffering for the large object
- OCI-Lob->size Returns size of large object
- OCI-Lob->tell Returns current position of internal pointer of large object
- OCI-Lob->truncate Truncates large object
- OCI-Lob->write Writes data to the large object
- OCI-Lob->writeTemporary Writes temporary large object
- OCI-Lob->writeToFile 别名 oci_lob_export()
- oci_new_collection 分配新的 collection 对象
- oci_new_connect 建定一个到 Oracle 服务器的新连接
- oci_new_cursor 分配并返回一个新的游标（语句句柄）
- oci_new_descriptor 初始化一个新的空 LOB 或 FILE 描述符
- oci_num_fields 返回结果列的数目
- oci_num_rows 返回语句执行后受影响的行数
- oci_parse 配置 Oracle 语句预备执行
- oci_password_change 修改 Oracle 用户的密码
- oci_pconnect 使用一个持久连接连到 Oracle 数据库
- oci_result 返回所取得行中字段的值
- oci_rollback 回滚未提交的事务
- oci_server_version 返回服务器版本信息
- oci_set_prefetch 设置预提取行数
- oci_statement_type 返回 OCI 语句的类型
- ocibindbyname oci_bind_by_name() 的别名
- ocicancel oci_cancel() 的别名
- ocicloselob OCI-Lob->close 的别名
- ocicollappend OCI-Collection->append 的别名
- ocicollassign OCI-Collection->assign 的别名
- ocicollassignelem OCI-Collection->assignElem 的别名
- ocicollgetelem OCI-Collection->getElem 的别名
- ocicollmax OCI-Collection->max 的别名
- ocicollsize OCI-Collection->size 的别名
- ocicolltrim OCI-Collection->trim 的别名
- ocicolumnisnull oci_field_is_null() 的别名
- ocicolumnname oci_field_name() 的别名
- ocicolumnprecision oci_field_precision() 的别名
- ocicolumnscale oci_field_scale() 的别名
- ocicolumnsize oci_field_size() 的别名
- ocicolumntype oci_field_type() 的别名
- ocicolumntyperaw oci_field_type_raw() 的别名
- ocicommit oci_commit() 的别名
- ocidefinebyname oci_define_by_name() 的别名
- ocierror oci_error() 的别名
- ociexecute oci_execute() 的别名
- ocifetch oci_fetch() 的别名
- ocifetchinto 获取下一行到一个数组
- ocifetchstatement oci_fetch_all() 的别名
- ocifreecollection OCI-Collection->free 的别名
- ocifreecursor oci_free_statement() 的别名
- ocifreedesc OCI-Lob->free 的别名
- ocifreestatement oci_free_statement() 的别名
- ociinternaldebug oci_internal_debug() 的别名
- ociloadlob OCI-Lob->load 的别名
- ocilogoff oci_close() 的别名
- ocilogon oci_connect() 的别名
- ocinewcollection oci_new_collection() 的别名
- ocinewcursor oci_new_cursor() 的别名
- ocinewdescriptor oci_new_descriptor() 的别名
- ocinlogon oci_new_connect() 的别名
- ocinumcols oci_num_fields() 的别名
- ociparse oci_parse() 的别名
- ociplogon oci_pconnect() 的别名
- ociresult oci_result() 的别名
- ocirollback oci_rollback() 别名
- ocirowcount oci_num_rows() 的别名
- ocisavelob OCI-Lob->save 的别名
- ocisavelobfile OCI-Lob->import 的别名
- ociserverversion oci_server_version() 的别名
- ocisetprefetch oci_set_prefetch() 的别名
- ocistatementtype oci_statement_type() 的别名
- ociwritelobtofile OCI-Lob->export 的别名
- ociwritetemporarylob OCI-Lob->writeTemporary 的别名
add a note
User Contributed NotesOracle 函数