Difference Between Named Queries and Stored Procedures

[Deepika Saxena]

Hi,
Could any body please let me know the differences Between Named queries and Stored Procedures?

Thanks.
--Deepika

[John Bengler]

Hi Deepika,

a Named Query is e.g. in EJB a EJBQL querywhich you give a name - and therefore resides inside the application server.

A Stored Procedure is procedure usually written in PL/SQL and resides inside the database.


John

[Deepika Saxena]

Thanks John.
cant a named query use the same execution plan, like a Stored Procedure? or is it like that whenever the named query gets called, every time it will be parsed and executed?

--Deepika

[John Bengler]

Yes, if a stored procedure contains a select which is the same as the one of a named query they will have the same execution plan.

But a stored procedure is more a kind of program, not just a select statement. You can write stored procedures with a lot of select statement, with one select statement or without any select.

Here you can find a short introduction to PL/SQL:

Using PL/SQL
Introduction to PL/SQL

Here is an example of a simple PL/SQL stored procedure that doesn't access any table:

    
  1  CREATE OR REPLACE PROCEDURE say_hello 
  2    (name VARCHAR2)
  3  IS
  2   dbms_output.put_line('Welcome '||name||'!');
  3  END;
  4  /

Of course this doesn't make too much sense in real life.

John