|
XCVI. Oracle 函數
本類函數使用 Oracle Call
Interface(OCI)使會員可以訪問 Oracle 10,Oracle9,Oracle8 和 Oracle7
資料庫。支援將 PHP 變量與 Oracle 佔位符(placeholder)綁定,具有完整的
LOB,FILE 和 ROWID 支援,以及容許使用會員提供的定義變量。
使用本增加需要 Oracle 用戶端庫。Windows
會員需要至少 Oracle 8.1 版才能使用
php_oci8.dll。
安裝所有所需檔案最方便的方法是使用
Oracle Instant Client,可以從此處得到:http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html。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
對於較少用到的 Oracle 環境變量例如
TNS_ADMIN,TWO_TASK,ORA_TZFILE 和各種 Oracle
全球性設定例如 ORA_NLS33,ORA_NLS10 和 NLS_*
等變量請參考 Oracle 穩當。
在為 web 伺服器會員設定環境變量之後,還需要將 web
伺服器會員(nobody,www)加到 oracle 組中。
I若果 web 伺服器不能夠啟動或是在啟動的時候崩潰:
檢查 Apache 是否連線了 pthread 庫:
若果 libpthread 沒有列出,必需重新安裝 Apache:
請注意在像 UnixWare 之類的某些動作系統中,使用 libthread
代替了 libpthread。則 PHP 和 Apache 必須使用 EXTRA_LIBS=-lthread 配置。
這些函數的行為受 php.ini 的影響。
表格 1. OCI8 Configuration Options Name | Default | Changeable | Changelog |
---|
oci8.privileged_connect | "0" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.max_persistent | "-1" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.persistent_timeout | "-1" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.ping_interval | "60" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.statement_cache_size | "20" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.default_prefetch | "10" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 | oci8.old_oci_close_semantics | "0" | PHP_INI_SYSTEM | Became available in oci8 module version 1.1 |
以下是配置選項的簡要解釋。
- 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 中,或是在運行時被動態加載後才有效。
例子 1. 基本查詢
<?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); ?>
|
|
例子 2. 用綁定變量插入
<?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);
?>
|
|
例子 3. 將資料插入到 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>';
?>
|
|
可以很容易地訪問存儲過程,就和從指令行訪問一樣。
例子 4. 使用存儲過程
<?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);
?>
|
|
表格 3. 在從結果集中取得列時支援以下類型 類型 | 映射 |
---|
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,返回為字串。 |
| |