 |
|
ss
Oracle Tips by Burleson |
Executing SQL
Many modern databases today use dialects of SQL,
“Structured Query Language”. SQL statements are executed and their
execution follows the same phases, regardless of the underlying
database:
-
Parse Phase - During the parse phase,
Oracle opens the statement handle, checks whether the statement is
OK (both syntactically and whether the involved objects exist and
are accessible) and creates an execution plan for executing this
statement. Parse call does not return an error if the statement is
not syntactically correct. Parsing can be a very expensive
operation that takes a lot of resources to execute. Special problem
are so called “hard parses” which happen when there is no previously
parsed version of the SQL to reuse.
-
Bind Phase - Once the plan is
syntactically created, Oracle gathers the parameters from the client
program needed for the execution. It makes the addresses of the
program variables “known” to Oracle.
-
Execute Phase - During the execute phase,
Oracle executes the statement, reports any possible errors, and if
everything is as it should be, forms the result set. Unless the SQL
statement being executed is a query, this is the last step of the
execution.
-
Define Phase - Define is analogous to
binds, only “output oriented”. The OCI define makes addresses of the
output variables “known” to the Oracle process in order to make it
possible to the fetch call to know where to put the output
variables. The define call is frequently skipped and is largely
considered unnecessary because of the automatic variables allocation
in PHP.
-
Fetch Phase - During the fetch phase,
Oracle brings the rows of the result to the program and makes them
accessible by the PHP interpreter. Once more, the define and fetch
phases are relevant for queries only. The Oracle OCI interface and
the PHP OCI8 module contain calls to facilitate each of those
phases.
The first thing to do with a SQL commandis to parse it. SQL statements are parsed by calling the
oci_parse()function. Here
is the syntax:
$sth=$oci_parse($db,$SQL);
The oci_parse()
function returns a statement handle, sometimes called a cursor, and
parses the SQL statement in $SQL at the database pointed to by the
$db database handle, returned by one of the connect functions.
Contrary to what is expected, the parse function does not return an
error if the function is syntactically incorrect or if underlying
objects do not exist. An error is returned only after called to the
oci_execute() function.
This was an optimization to bundle two calls
together and save a trip over the network. In the large applications
that execute numerous SQL statements, it is a significant performance
improvement.
The OCI8 module uses the oci_execute()
function to execute SQL statements. This syntax is extremely simple:
bool oci_execute (resource stmt [, int
mode])
The oci_execute() function takes the
statement handle generated by the oci_parse()function and executes it. The second argument is mode.
What is mode? In PHP, the oci_execute() function issues an
automatic commit after a successful execution. This is the same thing
as having AUTOCOMMIT set to ON in SQL*Plus.
This behavior is highly undesirable and should be
prevented. To prevent such a behavior, specify OCI_DEFAULT as the
mode argument. The illustration is presented below:
$sth=oci_parse(”delete
from emp”);
/*
This will not commit */
oci_execute($sth,OCI_DEFAULT);
/*
The next command will commit */
oci_execute($sth);
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
|
