On Thu, 16 Sep 2004, Greg Stark wrote:
>
> Neil Conway <neilc@samurai.com> writes:
>
> > whereas adding support for CALL to SQL is part of proper support for stored
> > procedures. Gavin and I are hoping to send a proposal for the latter to
> > -hackers in a few days.
>
> What is the point of stored procedures being distinct from functions anyways?
> Is there any real difference other than the irregular calling syntax? Is there
> anything you can't do with functions that you can do with procedures? Or is it
> purely a question of satisfying a spec or providing a more Oracle compatible
> syntax?
SQL-invoked procedures (ie, stored procedures) differ in two ways from
functions. These are:
1) Procedures do not return a value.
2) Arguments have 'parameter modes'. These modes are: IN - an input
parameter, which has been initialised to some value and is read-only; OUT
- an uninitialised parameter which can be written to; IN OUT - which has
the properties of each of the above.
What this actually means is that you can declare a procedure as follows:
CREATE PROCEDURE foo(IN bar INT, OUT baz INT, OUT bat INT, ...)
That is, a procedure can actually 'return' many values from a call. We can
do this with composite types but, speaking from experience, this can make
migration from PL/SQL just that much harder.
The other thing which SQL-invoked procedures necessitate is support for
the concept of a 'variable'. The reason being that if you use CALL in top
level SQL, you cannot make reference to a field of a relation in any
meaningful way and passing a column reference, for example, as an OUT
parameter does make any sense.
So, SQL2003 defines a few types of variables but the one people may be
most familiar with is the host parameter. This is a named variable which
is referenced as :foo.
I'm putting together a much more detailed email on all this which I hope
to send out in the next few days.
Thanks,
Gavin
