db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "DerbySQLroutines" by jeanTanderson
Date Fri, 13 Jan 2006 00:13:13 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by jeanTanderson:
http://wiki.apache.org/db-derby/DerbySQLroutines

New page:
This page describes how to implement SQL routines -- functions and procedures -- in java.
Feel free to edit. Especially please add working examples.

== Functions vs. Procedures ==

There is overlap between SQL Functions and Stored Procedures, 
but each can also do things the other cannot and the syntax for invoking each is different.

'''SQL functions''' execute as part of a SQL statement and can be used pretty much anywhere
a SQL expression is allowed, such as in the {{{SELECT}}} list or the {{{WHERE}}} clause. SQL
functions can also be invoked in triggers. However, they are read-only -- they cannot modify
data in the database.

Below is an example of invoking the built-in sql {{{LOWER}}} function in ij. {{{LOWER}}} simply
takes an input string and converts each character to lowercase, for example:

{{{
ij> values lower('LowerCASE me!');
1
-------------
lowercase me!
}}}

'''Stored procedures''' are invoked with the {{{CALL}}} statement or the {{{CallableStatement}}}
method in a Java client application. Stored procedures support {{{IN}}}, {{{OUT}}}, and {{{INOUT}}}
parameters. If the stored procedure has just {{{IN}}} parameters, you can invoke it anywhere
with the {{{CALL}}} statement, including in ij. If the stored procedure has {{{OUT}}} or {{{INOUT}}}
parameters, it can't be invoked from ij, it must be invoked from a client application using
the {{{CallableStatement}}} method.

Here's an example of invoking built-in stored procedures using {{{ij}}}. The first loads my
'myStuff.jar' jar file into the database and the second sets my database class path to include
that jar:

{{{
ij> CALL SQLJ.install_jar
('myStuff.jar', 'APP.MyStuffJar', 0);

ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
('derby.database.classpath', 'APP.MyStuffJar');
}}}


You can't invoke a sql function using {{{CALL}}} -- and you can't invoke a stored procedure
using {{{VALUES}}}. That's just one of the differences between them. More comparisons are
summarized in the table below:

||'''Feature'''||'''Procedure'''||'''Function'''||
||Execute in a trigger||no||yes||
||Return result set(s)||yes||no||
||Process OUT / INOUT Params||yes||no||
||Execute SQL select||yes||yes||
||Execute SQL update/insert/delete||yes||no||
||Execute DDL (create/drop)||yes||no||
||Execute in a SQL expression||no||yes||

== Creating Functions ==

The [http://db.apache.org/derby/docs/dev/ref/rrefcreatefunctionstatement.html Reference Guide]
provides the syntax for creating functions.

The [http://db.apache.org/derby/papers/fortune_tut.html Apache Derby Fortune Server tutorial]
from ApacheCon 2004 shows how to create
three SQL functions:

 * {{{tutRand}}} generates a random integer using the Jakarta Math Library.
 * {{{tutMatch}}} and {{{tutReplace}}} perform regular expression search and replace using
Jakarta Regexp

The [https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Derby functional tests] are an excellent source for function samples.


== Creating Procedures ==

The [http://db.apache.org/derby/docs/dev/ref/rrefcreateprocedurestatement.html Reference Guide]
provides the syntax for creating stored procedures.

The [https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Derby functional tests] also include procedures.

Mime
View raw message