Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Prepared Statements
Oracle Tips by Burleson
 

When EnterpriseDB runs a DML command, it first must parse the command and then find the best way to run the command (optimize it).  The parse step breaks it up into its component pieces and checks permissions.  The optimize step finds the best access path.  Finally, EnterpriseDB executes the command and returns the results.

In some cases, it might be better if EnterpriseDB could do the first two steps up front and store those results.  Then you could execute the query later without having to wait for those steps.  Prepared statements gives you that ability.

When would you want to do this?  Primarily when you have a query that you will run many times per day, per hour or per second and that either does not change or only changes the where clause between calls.   

Prepared statements work with SELECT, INSERT, UPDATE and DELETE.

Prepare

To use a prepared statement, you must first prepare it.  When you prepare a statement, you give it a name (so you can refer to it later), identify the data types of any bind variables (bind variables are those items that will change between calls) and define the query. 

In the query itself, the bind variables are identified by the dollar sign ($) followed by a number.  The first bind variable would be referred to as $1, the second as $2, etc.

Let's say we have an application that frequently requests to see all employees who are in a particular job (that changes between calls) and who have a salary greater than a certain amount (which changes between calls).

PREPARE emp_sal_query (text, int) AS
  SELECT ename, job, sal
    FROM emp
    WHERE job = $1
      AND sal > $2;

edb=# PREPARE emp_sal_query (text, int) AS
edb-#   SELECT ename, job, sal
edb-#     FROM emp
edb-#     WHERE job = $1
edb-#       AND sal > $2;

PREPARE
edb=#

We now have a prepared statement.  We identified two variables that will be replaced at run time, a text variable and an int variable.  In our query, we associated the job = with $1, the text variable and associated the sal > with the $2 variable which is the int variable.

Once we have prepared the statement, EnterpriseDB has parsed it and figured out its execution path.  When we execute it, EnterpriseDB will not need to re-parse or re-optimize the statement.

Execute

To execute a prepared statement, we use the EXECUTE command.

EXECUTE emp_sal_query( 'SALESMAN', 1500 );
edb=# EXECUTE emp_sal_query( 'SALESMAN', 1500 );

 ename  |   job    |   sal
--------+----------+---------
 ALLEN  | SALESMAN | 1648.00
 TURNER | SALESMAN | 1545.00

(2 rows)

edb=#

We can execute it many times with different values:

EXECUTE emp_sal_query( 'MANAGER', 3000 );
EXECUTE emp_sal_query( 'MANAGER', 2700 ); 

edb=# EXECUTE emp_sal_query( 'MANAGER', 3000 );

 ename |   job   |   sal
-------+---------+---------
 JONES | MANAGER | 3064.25

(1 row)

edb=# EXECUTE emp_sal_query( 'MANAGER', 2700 );

 ename |   job   |   sal
-------+---------+---------
 JONES | MANAGER | 3064.25
 BLAKE | MANAGER | 2935.50

(2 rows)

edb=#

That's all there is to it.  If you have an application that runs many queries over and over throughout the day and just changes variables, you can create a stored procedure to prepare all of your statements and just have you application execute those statements.

Using prepared statements does consume a small amount of memory.  If you want to clean up that memory, you can get rid of the prepared statement by using the DEALLOCATE command:

DEALLOCATE emp_sal_query; 

edb=# DEALLOCATE emp_sal_query;
DEALLOCATE
edb=#

Our chapter is almost over now.  The last topic to be covered is some of the more common SQL functions.
 

             
This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter