Binds a PHP variable variable to the Oracle
bind variable placeholder bv_name. Binding
is important for Oracle database performance and also as a way to
avoid SQL Injection security issues.
Binding allows the database to reuse the statement context and
caches from previous executions of the statement, even if another
user or process originally executed it. Binding reduces SQL
Injection concerns because the data associated with a bind
variable is never treated as part of the SQL statement. It does
not need quoting or escaping.
PHP variables that have been bound can be changed and the
statement re-executed without needing to re-parse the statement or
re-bind.
In Oracle, bind variables are commonly divided
into IN binds for values that are passed into
the database, and OUT binds for values that are
returned to PHP. A bind variable may be
both IN and OUT. Whether a
bind variable will be used for input or output is determined at
run-time.
You must specify maxlength when using
an OUT bind so that PHP allocates enough memory
to hold the returned value.
For IN binds it is recommended to set
the maxlength length if the statement is
re-executed multiple times with different values for the PHP
variable. Otherwise Oracle may truncate data to the length of the
initial PHP variable value. If you don't know what the maximum
length will be, then re-call oci_bind_by_name()
with the current data size prior to
each oci_execute() call. Binding an
unnecessarily large length will have an impact on process memory
in the database.
A bind call tells Oracle which memory address to read data from.
For IN binds that address needs to contain
valid data when oci_execute() is called. This
means that the variable bound must remain in scope until
execution. If it doesn't, unexpected results or errors such as
"ORA-01460: unimplemented or unreasonable conversion requested"
may occur. For OUT binds one symptom is no
value being set in the PHP variable.
For a statement that is repeatedly executed, binding values that
never change may reduce the ability of the Oracle optimizer to
choose the best statement execution plan. Long running statements
that are rarely re-executed may not benefit from binding. However
in both cases, binding might be safer than joining strings into a
SQL statement, as this can be a security risk if unfiltered user
text is concatenated.
인수
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 the variable 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
is SQLT_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.
// 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]); }
// 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 SQL 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 // South Brunswick // South San Francisco // Southlake
// 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 // Singapore
oci_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 #7 Binding Multiple Values in an IN Clause
$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, CHRIS
oci_free_statement($stid); oci_close($conn);
?>
Example #9 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)
// 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 #10 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;
// 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 24
oci_free_statement($stid); oci_close($conn);
?>
Example #11 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;
// 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 16
oci_free_statement($stid); oci_close($conn);
?>
Example #12 Binding a CLOB column
<?php
// Before running, create the table: // CREATE TABLE mytab (mykey NUMBER, myclob CLOB);
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>';
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
a WHERE clause, remember that Oracle uses
blank-padded comparison semantics for CHAR
columns. Your PHP variable should be blank padded to the same
width as the column for the WHERE clause to
succeed.
Note:
The PHP variable argument is a reference. Some
forms of loops do not work as expected: