php oci_bind_array_by_name查询,PHP: oci_bind_by_name - Manual

I had a query that was working properly at first sight, no errors on execute, nothing, but there were simply no results returned at runtime.

Be careful when putting the database commands into a function and binding your variables there while using oci_fetch_xxx() outside the function.

function sql($conn, $stmt, $var) {

$stid = oci_parse($conn, $stmt);

...

oci_bind_by_name($stid, ':val', $var);

...

}

sql($conn, $q, $var);

$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);

As you see from the definition of oci_bind_by_name(), $var needs to be passed as reference, so your function has to have this reference ready like this:

function sql($conn, $stmt, &$var) {

$stid = oci_parse($conn, $stmt);

...

oci_bind_by_name($stid, ':val', $var);

...

}

The background is that if you don't pass by reference (in which case $var inside the function is a copy of $var outside the function), then oci_bind_by_name() will work just fine at first glance.

However, since the oci_fetch statements that you use to actually get the data will reference the $var that has ceased to exist when the function finished. In fact, since the varbind seems to be a pointer, that pointer will point to an invalid location at that point and your variables won't be substitued in the SQL.

All this also means that:

1) You have to pass a variable, and not just a value

This doesn't work:

$stid = sql($conn, $q, array('bla'=>'blubb'));

This is better:

$vars = array('bla'=>'blubb');

$stid = sql($conn, $q, $vars);

2) Even when passing as reference to your helper function you cannot use e.g. foreach:

This doesn't work:

function sql($conn, $q, $vars) {

...

foreach ($vars as $k => $v) {

oci_bind_by_name($stid, $k, $v);

}

...

}

Again, because $k and $v are local variables that will have disappeared once you perform an oci_fetch outside the function.

Instead you have to work the array in a more low-level way like this:

function sql($conn, $q, &$vars) {

...

$stid = oci_parse($conn, $q);

...

reset($vars);

do {

if (current($vars)===FALSE) { // end of array

break;

}

$b = oci_bind_by_name($stid, key($vars), current($vars));

if ($b === FALSE) {

DIE('Could not bind var');

}

} while (each($vars) !== FALSE);

}

$binds = array(':bla1' => 'blubb1',

':bla2' => 'blubb2');

$stid = sql($conn, $q, $binds);

$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);

Wherever you oci_bind_by_name(), the pointer to the initial data has to exist from beginning to end.