Php/docs/function.oci-bind-by-name
oci_bind_by_name
(PHP 5, PHP 7, PECL OCI8 >= 1.1.0)
oci_bind_by_name — 绑定一个 PHP 变量到一个 Oracle 位置标志符
说明
oci_bind_by_name
( resource $statement
, string $bv_name
, mixed &$variable
[, int $maxlength
= -1
[, int $type
= SQLT_CHR
]] ) : bool
将 PHP 变量 variable
绑定到
Oracle 位置标志符 bv_name
。绑定操作对 Oracle 数据库性能很重要,同时也是避免 SQL 注入安全问题的一种方式。
oci_bind_by_name() 将 PHP 变量
variable
绑定到 Oracle 的位置标志符
ph_name
。该变量是否会被用作输入输出是在运行时决定的,并且函数给该变量分配必要的存储空间。length
参数确定该绑定的最大长度,如果将 length
设为 -1,oci_bind_by_name() 会用
variable
变量的当前长度确定绑定的最大长度。
如果要绑定一个抽象数据类型(LOB/ROWID/BFILE),需要先用
oci_new_descriptor() 函数分配空间。length
没有用于抽象数据类型,应被设为 -1。type
参数告诉 Oracle 要使用什么样的描述符。可能的值为:
SQLT_FILE
- 对应于 BFILE;SQLT_CFILE
- 对应于 CFILE;SQLT_CLOB
- 对应于 CLOB;SQLT_BLOB
- 对应于 BLOB;SQLT_ROWID
- 对应于 ROWID;SQLT_NTY
- 对应于有名字的数据类型;SQLT_INT
- 对应于 integers;SQLT_CHR
- 对应于 VARCHARs;SQLT_BIN
- 对应于 RAW 列;SQLT_LNG
- 对应于 LONG 列;SQLT_LBI
- 对应于 LONG RAW 列;SQLT_RSET
- 对应于游标,是之前由 oci_new_cursor() 创建的。
Example #1 oci_bind_by_name() 例子
<?php/* oci_bind_by_name example thies at thieso dot net (980221) inserts 3 records into emp, and uses the ROWID for updating the records just after the insert.*/$conn = oci_connect("scott", "tiger");$stmt = oci_parse($conn, " INSERT INTO emp (empno, ename) VALUES (:empno,:ename) RETURNING ROWID INTO :rid ");$data = array( 1111 => "Larry", 2222 => "Bill", 3333 => "Jim" );$rowid = oci_new_descriptor($conn, OCI_D_ROWID);oci_bind_by_name($stmt, ":empno", $empno, 32);oci_bind_by_name($stmt, ":ename", $ename, 32);oci_bind_by_name($stmt, ":rid", $rowid, -1, OCI_B_ROWID);$update = oci_parse($conn, " UPDATE emp SET sal = :sal WHERE ROWID = :rid ");oci_bind_by_name($update, ":rid", $rowid, -1, OCI_B_ROWID);oci_bind_by_name($update, ":sal", $sal, 32);$sal = 10000;foreach ($data as $empno => $ename) { oci_execute($stmt); oci_execute($update);}$rowid->free();oci_free_statement($update);oci_free_statement($stmt);$stmt = oci_parse($conn, " SELECT * FROM emp WHERE empno IN (1111,2222,3333) ");oci_execute($stmt);while ($row = oci_fetch_assoc($stmt)) { var_dump($row);}oci_free_statement($stmt);/* delete our "junk" from the emp table.... */$stmt = oci_parse($conn, " DELETE FROM emp WHERE empno IN (1111,2222,3333) ");oci_execute($stmt);oci_free_statement($stmt);oci_close($conn);?>
记住,本函数删除了行尾的空白字符。见以下例子:
Example #2 oci_bind_by_name() 例子
<?php$connection = oci_connect('apelsin','kanistra');$query = "INSERT INTO test_table VALUES(:id, :text)";$statement = oci_parse($query);oci_bind_by_name($statement, ":id", 1);oci_bind_by_name($statement, ":text", "trailing spaces follow ");oci_execute($statement);/* This code will insert into DB string 'trailing spaces follow', without trailing spaces*/?>
Example #3 oci_bind_by_name() 例子
<?php$connection = oci_connect('apelsin','kanistra');$query = "INSERT INTO test_table VALUES(:id, 'trailing spaces follow ')";$statement = oci_parse($query);oci_bind_by_name($statement, ":id", 1);oci_execute($statement);/* And this code will add 'trailing spaces follow ', preserving trailing whitespaces*/?>
Warning 不要将 magic_quotes_gpc 或 addslashes() 与 oci_bind_by_name() 同时使用,因为不需要转义,任何自动加上的引号都会被写入数据库中,因为 oci_bind_by_name() 不能分辨有意加上的引号和魔术引号。
成功时返回 true
, 或者在失败时返回 false
。
Note:
在 PHP 5.0.0 之前的版本必须使用 ocibindbyname() 替代本函数。该函数名仍然可用,为向下兼容作为 oci_bind_by_name() 的别名。不过其已被废弃,不推荐使用。
参数
statement
A valid OCI8 statement identifer.
bv_name
The colon-prefixed bind variable placeholder used in the statement. The colon is optional in
bv_name
. Oracle does not use question marks for placeholders.variable
The PHP variable to be associated with
bv_name
maxlength
Sets the maximum length for the data. If you set it to -1, this function will use the current length of
variable
to set the maximum length. In this case thevariable
must exist and contain data when oci_bind_by_name() is called.type
The datatype that Oracle will treat the data as. The default
type
used isSQLT_CHR
. Oracle will convert the data between this type and the database column (or PL/SQL variable type), when possible.If you need to bind an abstract datatype (LOB/ROWID/BFILE) you need to allocate it first using the oci_new_descriptor() function. The
length
is not used for abstract datatypes and should be set to -1.Possible values for
type
are:SQLT_BFILEE
orOCI_B_BFILE
- for BFILEs;SQLT_CFILEE
orOCI_B_CFILEE
- for CFILEs;SQLT_CLOB
orOCI_B_CLOB
- for CLOBs;SQLT_BLOB
orOCI_B_BLOB
- for BLOBs;SQLT_RDD
orOCI_B_ROWID
- for ROWIDs;SQLT_NTY
orOCI_B_NTY
- for named datatypes;SQLT_INT
orOCI_B_INT
- for integers;SQLT_CHR
- for VARCHARs;SQLT_BIN
orOCI_B_BIN
- for RAW columns;SQLT_LNG
- for LONG columns;SQLT_LBI
- for LONG RAW columns;SQLT_RSET
- for cursors created with oci_new_cursor().SQLT_BOL
orOCI_B_BOL
- for PL/SQL BOOLEANs (Requires OCI8 2.0.7 and Oracle Database 12c)
返回值
成功时返回 true
, 或者在失败时返回 false
。
范例
Example #4 Inserting data with oci_bind_by_name()
<?php// Create the table with:// CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");$id = 1;$text = "Data to insert ";oci_bind_by_name($stid, ":id_bv", $id);oci_bind_by_name($stid, ":text_bv", $text);oci_execute($stid);// Table now contains: 1, 'Data to insert '?>
Example #5 Binding once for multiple executions
<?php// Create the table with:// CREATE TABLE mytab (id NUMBER);$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$a = array(1,3,5,7,11); // data to insert$stid = oci_parse($conn, 'INSERT INTO mytab (id) VALUES (:bv)');oci_bind_by_name($stid, ':bv', $v, 20);foreach ($a as $v) { $r = oci_execute($stid, OCI_DEFAULT); // don't auto commit}oci_commit($conn); // commit everything at once// Table contains five rows: 1, 3, 5, 7, 11oci_free_statement($stid);oci_close($conn);?>
Example #6 Binding with a foreach() loop
<?php$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$sql = 'SELECT * FROM departments WHERE department_name = :dname AND location_id = :loc';$stid = oci_parse($conn, $sql);$ba = array(':dname' => 'IT Support', ':loc' => 1700);foreach ($ba as $key => $val) { // oci_bind_by_name($stid, $key, $val) does not work // because it binds each placeholder to the same location: $val // instead use the actual location of the data: $ba[$key] oci_bind_by_name($stid, $key, $ba[$key]);}oci_execute($stid);$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);foreach ($row as $item) { print $item."<br>\n";}oci_free_statement($stid);oci_close($conn);?>
Example #7 Binding in a WHERE clause
<?php$conn = oci_connect("hr", "hrpwd", "localhost/XE");if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$sql = 'SELECT last_name FROM employees WHERE department_id = :didbv ORDER BY last_name';$stid = oci_parse($conn, $sql);$didbv = 60;oci_bind_by_name($stid, ':didbv', $didbv);oci_execute($stid);while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) { echo $row['LAST_NAME'] ."<br>\n";}// Output is// Austin// Ernst// Hunold// Lorentz// Pataballaoci_free_statement($stid);oci_close($conn);?>
Example #8 Binding with a LIKE clause
<?php$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}// Find all cities that begin with 'South'$stid = oci_parse($conn, "SELECT city FROM locations WHERE city LIKE :bv");$city = 'South%'; // '%' is a wildcard in SQLoci_bind_by_name($stid, ":bv", $city);oci_execute($stid);oci_fetch_all($stid, $res);foreach ($res['CITY'] as $c) { print $c . "<br>\n";}// Output is// South Brunswick// South San Francisco// Southlakeoci_free_statement($stid);oci_close($conn);?>
Example #9 Binding with REGEXP_LIKE
<?php$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}// Find all cities that contain 'ing'$stid = oci_parse($conn, "SELECT city FROM locations WHERE REGEXP_LIKE(city, :bv)");$city = '.*ing.*';oci_bind_by_name($stid, ":bv", $city);oci_execute($stid);oci_fetch_all($stid, $res);foreach ($res['CITY'] as $c) { print $c . "<br>\n";}// Output is// Beijing// Singaporeoci_free_statement($stid);oci_close($conn);?>
For a small, fixed number of IN clause conditions, use individual bind variables. Values unknown at run time can be set to NULL. This allows a single statement to be used by all application users, maximizing Oracle DB cache efficiency.
Example #10 Binding Multiple Values in an IN Clause
<?php$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$sql = 'SELECT last_name FROM employees WHERE employee_id in (:e1, :e2, :e3)';$stid = oci_parse($conn, $sql);$mye1 = 103;$mye2 = 104;$mye3 = NULL; // pretend we were not given this valueoci_bind_by_name($stid, ':e1', $mye1);oci_bind_by_name($stid, ':e2', $mye2);oci_bind_by_name($stid, ':e3', $mye3);oci_execute($stid);oci_fetch_all($stid, $res);foreach ($res['LAST_NAME'] as $name) { print $name ."<br>\n";}// Output is// Ernst// Hunoldoci_free_statement($stid);oci_close($conn);?>
Example #11 Binding a ROWID returned by a query
<?php// Create the table with:// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));// INSERT INTO mytab (id, salary, name) VALUES (1, 100, 'Chris');// COMMIT;$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$stid = oci_parse($conn, 'SELECT ROWID, name FROM mytab WHERE id = :id_bv FOR UPDATE');$id = 1;oci_bind_by_name($stid, ':id_bv', $id);oci_execute($stid);$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);$rid = $row['ROWID'];$name = $row['NAME'];// Change name to upper case & save the changes$name = strtoupper($name);$stid = oci_parse($conn, 'UPDATE mytab SET name = :n_bv WHERE ROWID = :r_bv');oci_bind_by_name($stid, ':n_bv', $name);oci_bind_by_name($stid, ':r_bv', $rid, -1, OCI_B_ROWID);oci_execute($stid);// The table now contains 1, 100, CHRISoci_free_statement($stid);oci_close($conn);?>
Example #12 Binding a ROWID on INSERT
<?php// This example inserts an id & name, and then updates the salary// Create the table with:// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));//// Based on original ROWID example by thies at thieso dot net (980221)$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $m = oci_error(); trigger_error(htmlentities($m['message']), E_USER_ERROR);}$sql = "INSERT INTO mytab (id, name) VALUES(:id_bv, :name_bv) RETURNING ROWID INTO :rid";$ins_stid = oci_parse($conn, $sql);$rowid = oci_new_descriptor($conn, OCI_D_ROWID);oci_bind_by_name($ins_stid, ":id_bv", $id, 10);oci_bind_by_name($ins_stid, ":name_bv", $name, 32);oci_bind_by_name($ins_stid, ":rid", $rowid, -1, OCI_B_ROWID);$sql = "UPDATE mytab SET salary = :salary WHERE ROWID = :rid";$upd_stid = oci_parse($conn, $sql);oci_bind_by_name($upd_stid, ":rid", $rowid, -1, OCI_B_ROWID);oci_bind_by_name($upd_stid, ":salary", $salary, 32);// ids and names to insert$data = array(1111 => "Larry", 2222 => "Bill", 3333 => "Jim");// Salary of each person$salary = 10000;// Insert and immediately update each rowforeach ($data as $id => $name) { oci_execute($ins_stid); oci_execute($upd_stid);}$rowid->free();oci_free_statement($upd_stid);oci_free_statement($ins_stid);// Show the new rows$stid = oci_parse($conn, "SELECT * FROM mytab");oci_execute($stid);while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) { var_dump($row);}oci_free_statement($stid);oci_close($conn);?>
Example #13 Binding for a PL/SQL stored function
<?php// Before running the PHP program, create a stored function in// SQL*Plus or SQL Developer://// CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS// BEGIN// RETURN p * 3;// END;$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message']), E_USER_ERROR);}$p = 8;$stid = oci_parse($conn, 'begin :r := myfunc(:p); end;');oci_bind_by_name($stid, ':p', $p);// The return value is an OUT bind. The default type will be a string// type so binding a length 40 means that at most 40 digits will be// returned.oci_bind_by_name($stid, ':r', $r, 40);oci_execute($stid);print "$r\n"; // prints 24oci_free_statement($stid);oci_close($conn);?>
Example #14 Binding parameters for a PL/SQL stored procedure
<?php// Before running the PHP program, create a stored procedure in// SQL*Plus or SQL Developer://// CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS// BEGIN// p2 := p1 * 2;// END;$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message']), E_USER_ERROR);}$p1 = 8;$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');oci_bind_by_name($stid, ':p1', $p1);// The second procedure parameter is an OUT bind. The default type// will be a string type so binding a length 40 means that at most 40// digits will be returned.oci_bind_by_name($stid, ':p2', $p2, 40);oci_execute($stid);print "$p2\n"; // prints 16oci_free_statement($stid);oci_close($conn);?>
Example #15 Binding a CLOB column
<?php// Before running, create the table:// CREATE TABLE mytab (mykey NUMBER, myclob CLOB);$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message']), E_USER_ERROR);}$mykey = 12343; // arbitrary key for this example;$sql = "INSERT INTO mytab (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 mytab WHERE mykey = :mykey';$stid = oci_parse ($conn, $query);oci_bind_by_name($stid, ":mykey", $mykey, 5);oci_execute($stid);print '<table border="1">';while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) { print '<tr><td>'.$row['MYCLOB'].'</td></tr>'; // In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage unset($row); }print '</table>';?>
Example #16 Binding a PL/SQL BOOLEAN
<?php$conn = oci_connect('hr', 'welcome', 'localhost/XE');if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message']), E_USER_ERROR);}$plsql = "begin :output1 := true; :output2 := false; end;";$s = oci_parse($c, $plsql);oci_bind_by_name($s, ':output1', $output1, -1, OCI_B_BOL);oci_bind_by_name($s, ':output2', $output2, -1, OCI_B_BOL);oci_execute($s);var_dump($output1); // truevar_dump($output2); // false?>
返回值
成功时返回 true
, 或者在失败时返回 false
。
注释
Warning Do not use magic_quotes_gpc or addslashes() and oci_bind_by_name() simultaneously as no quoting is needed. Any magically applied quotes will be written into your database because oci_bind_by_name() inserts data verbatim and does not remove quotes or escape characters.
Note:
If you bind a string to a
CHAR
column in aWHERE
clause, remember that Oracle uses blank-padded comparison semantics forCHAR
columns. Your PHP variable should be blank padded to the same width as the column for theWHERE
clause to succeed.
Note:
The PHP
variable
argument is a reference. Some forms of loops do not work as expected:
<?phpforeach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $value);}?>
This binds each key to the location of $value, so all bound variables end up pointing to the last loop iteration's value. Instead use the following:
<?phpforeach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $myarray[$key]);}?>
参见
- oci_bind_array_by_name() - Binds a PHP array to an Oracle PL/SQL array parameter
- oci_parse() - 配置 Oracle 语句预备执行