 |
|
ss
Oracle Tips by Burleson |
Binding
and Defining Variables
If the same SQL statement has to be executed for
many sets of values and since parsing is so expensive, it needs to be
reused over and over again until the set of values is exhausted. This
is achieved by putting placeholders in the SQL statement and binding
those placeholders with variables in the program. So, if an INSERT
statement like the following is available, it can be reused many times
for many different quadruplets of values (col1, col2, col3, col4):
INSERT INTO
TBL VALUES (:COL1,:COL2,:COL3,:COL4)
This is achieved by binding the placeholders such
as :COL1 and :COL2 to actual program variables and then executing the
statement again, whenever the value of the program variables bound to
the placeholders changes. Not surprisingly, this is known as binding
variables and is performed by the function called oci_bind_by
name().
The following is the description of the
oci_bind_by_name() function, coming directly from the on-line manual:
DESCRIPTION
oci_bind_by_name()
binds the PHP variable variable to the Oracle placeholder ph_name.
Whether it will be used for input or output will be determined at
run-time and the necessary storage space will be allocated. The length
parameter sets the maximum length for the bind. If you set length to
-1 oci_bind_by_name() will use the current length of variable
to set the maximum length.
The concept is simple. Take a statement handle,
placeholder and a variable and bundle it all together with a bind
call. These types of variables need additional attention. PHP is
weakly typed, so it does not have any types worth mentioning. Oracle,
on the other hand, sharply distinguishes between the database types.
For most of the Oracle types, type description is not necessary.
However, if the application uses more complex
Oracle types, type description is necessary. Those types are ROWID,
object types, cursors and large objects, also known as LOB types. Term
LOB is somewhat confusing as the term LOB object is also used for the
locator objects, used to access various forms of large objects (BLOB,CLOB,
BFILE). In further text the term LOB will refer to the data type
itself while locator will be separately mentioned to clarify the
difference wherever there is a possibility of confusion.
The following list contains the flags used to
describe those variable types:
-
OCI_B_FILE
- for BFILEs.
-
OCI_B_CFILE - for
CFILEs.
-
OCI_B_CLOB - for
CLOBs.
-
OCI_B_BLOB - for
BLOBs.
-
OCI_B_ROWID - for
ROWIDs.
-
OCI_B_NTY - for
named datatypes.
-
OCI_B_CURSOR - for
cursors, that were created before with oci_new_cursor().
These Oracle data types are special and the user
cannot bind them to regular variables. They are bound to specially
allocated descriptors, allocated by the oci_new_descriptor() or
oci_new_cursor() functions. Both functions are shown in the
section devoted to examples with cursors and LOB variables at the end
of this chapter.
The first example in this section is shown below.
In Example 15, the user binds the PHP variable $job to the
placeholder named “:job”.
$ cat
./example15.php
#!/usr/local/bin/php
<?php
$db=oci_new_connect("scott","tiger");
$job="C%";
$SQL="select ename,job,hiredate,sal from
emp
where job like :job
order by sal";
$sth=oci_parse($db,$SQL);
if (!oci_bind_by_name($sth,":job",$job,32))
{
$err
=oci_error($sth);
die($err['message']);
}
@oci_execute($sth);
if (!defined($sth))
{
$err
=oci_error($sth);
}
while
($row=oci_fetch_array($sth)) {
echo $row[0],"\t",$row[1],"\t",$row[2],
"\t",$row[3],"\n";
}
echo
"This statement returned ",
oci_num_rows($sth)," rows\n";
?>
Execution of Example 15 gives the following
result:
$
./example15.php
SMITH
CLERK 17-DEC-80 800
JAMES
CLERK 03-DEC-81 950
ADAMS
CLERK 23-MAY-87 1100
MILLER
CLERK 23-JAN-82 1300
This statement returned 4 rows
See
code depot for complete scripts
The above book excerpt is from:
Easy Oracle
PHP
Create Dynamic Web Pages with Oracle Data
ISBN
0-9761573-0-6
Mladen Gogala
http://www.rampant-books.com/book_2005_2_php_oracle.htm
|