OCI8 関数
PHP Manual

oci_bind_by_name

(PHP 5, PECL OCI8 >= 1.1.0)

oci_bind_by_nameOracle プレースホルダーに PHP 変数をバインドする

説明

bool oci_bind_by_name ( resource $statement , string $bv_name , mixed &$variable [, int $maxlength = -1 [, int $type = SQLT_CHR ]] )

PHP の変数 variable を Oracle プレースホルダー 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

有効な OCI8 ステートメント識別子。

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

bv_name に関連づける PHP の変数。

maxlength

バインド時の最大長。-1 に設定した場合、 variable の現在の長さを最大長として設定します。 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.

抽象データ型 (LOB/ROWID/BFILE) をバインドする必要がある場合、まず oci_new_descriptor() 関数を使用してこれを確保する必要があります。 length は抽象データ型用には 使用されず、-1 を設定する必要があります。

type に設定できる値は以下のとおりです。

  • SQLT_BFILEE or OCI_B_BFILE - BFILE 用

  • SQLT_CFILEE or OCI_B_CFILEE - CFILE 用

  • SQLT_CLOB or OCI_B_CLOB - CLOB 用

  • SQLT_BLOB or OCI_B_BLOB - BLOB 用

  • SQLT_RDD or OCI_B_ROWID - ROWID 用

  • SQLT_NTY or OCI_B_NTY - 名前付けされたデータ型用

  • SQLT_INT or OCI_B_INT - integer 用

  • SQLT_CHR - VARCHAR 用

  • SQLT_BIN or OCI_B_BIN - RAW カラム用

  • SQLT_LNG - LONG カラム用

  • SQLT_LBI - LONG RAW カラム用

  • SQLT_RSET - カーソル用。 oci_new_cursor() により、前もって生成されたもの。

返り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

例1 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     '

?>

例2 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'$v20);
foreach (
$a as $v) {
    
$r oci_execute($stidOCI_DEFAULT);  // don't auto commit
}
oci_commit($conn); // commit everything at once

// Table contains five rows: 1, 3, 5, 7, 11

oci_free_statement($stid);
oci_close($conn);

?>

例3 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($stidOCI_ASSOC+OCI_RETURN_NULLS);
foreach (
$row as $item) {
    print 
$item."<br>\n";
}

oci_free_statement($stid);
oci_close($conn);

?>

例4 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 employee_id = :eidbv';
$stid oci_parse($conn$sql);
$myeid 101;
oci_bind_by_name($stid':eidbv'$myeid);
oci_execute($stid);
$row oci_fetch_array($stidOCI_ASSOC);
echo 
$row['LAST_NAME'] ."<br>\n";

// Output is
//    Kochhar

oci_free_statement($stid);
oci_close($conn);

?>

例5 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 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

oci_free_statement($stid);
oci_close($conn);

?>

例6 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
//   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.

例7 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 value
oci_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
//   Hunold

oci_free_statement($stid);
oci_close($conn);

?>

例8 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($stidOCI_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, -1OCI_B_ROWID);
oci_execute($stid);

// The table now contains 1, 100, CHRIS

oci_free_statement($stid);
oci_close($conn);

?>

例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)

$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($connOCI_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, -1OCI_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, -1OCI_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 row
foreach ($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($stidOCI_ASSOC+OCI_RETURN_NULLS)) {
    
var_dump($row);
}

oci_free_statement($stid);
oci_close($conn);

?>

例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;

$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'$r40);

oci_execute($stid);

print 
"$r\n";   // prints 24

oci_free_statement($stid);
oci_close($conn);

?>

例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;

$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'$p240);

oci_execute($stid);

print 
"$p2\n";   // prints 16

oci_free_statement($stid);
oci_close($conn);

?>

例12 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($connOCI_D_LOB);
oci_bind_by_name($stid":mykey"$mykey5);
oci_bind_by_name($stid":myclob"$clob, -1OCI_B_CLOB);
oci_execute($stidOCI_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"$mykey5);
oci_execute($stid);

print 
'<table border="1">';
while (
$row oci_fetch_array($stidOCI_ASSOC)) {
  
$result $row['MYCLOB']->load();
  print 
'<tr><td>'.$result.'</td></tr>';
}
print 
'</table>';

?>

返り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

注意

警告

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.

注意: 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.

注意: The PHP variable argument is a reference. Some forms of loops do not work as expected:

foreach ($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:
foreach ($myarray as $key => $value) {
    oci_bind_by_name($stid, $key, $myarray[$key]);
}

注意: In PHP versions before 5.0.0 you must use ocibindbyname() instead. 現在のバージョンでは、古い関数名もまだ使えます。 しかし、これは廃止予定であり非推奨です。

参考


OCI8 関数
PHP Manual