Books for you

The application
submits the following SQL:
UPDATE emp SET salary = salary * (1 + 0.1)
WHERE empno = 123;
UPDATE emp SET salary = salary * (1 + 0.15)
WHERE empno = 456;
These statements are clearly different; they update different employees identified by
different employee numbers, and the employees receive different salary increases. To
obtain the benefits of shared SQL, youcan write the application to use bind variables
for the percentage salary increase and the employee numbers, such as:
UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;
UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;
These statements are recognized as identical and would therefore be shared. The application
would submit different values for the two variables :v_incr and :v_empno, a
percentage increase of 0.1 for employee 123 and 0.15 for employee 456. Oracle substitutes
these actual values for the variables in the SQL. The substitution occurs during
the phase of processing known as the bind phase, which follows the parse phase and
optimize phase.


Pages:
450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474