On Thu, 16 Sep 2004, Greg Stark wrote: > > Neil Conway <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
