Helen Borrie schrieb am 05.12.2010 um 15:00 (+1300):

> At 02:20 AM 5/12/2010, Michael Ludwig wrote:
> >
> >I haven't encountered too much guidance on writing Firebird-PSQL
> >yet, like best practices and so on.
>
> I think that would be hard! Any "best practices" are driven by
> your own requirements and test results. (My own are driven by
> complete paranoia about data consistency! From years of
> experiences as a support person, I am only too well aware that
> others have different priorities...the *overriding* one being to
> save typing!!!)

Okay, but that's only rogue programmers. :-) I was under the
impression that on this list at least, we all do care about
nothing but data consistency, don't we all.

> I have often noted your potential value to Firebird as a
> documenter! ;-)

That's flattering, thanks! I could possibly make some time for
it, why not? Doing the docs in DITA? (Seen you on the DITA list.)
I don't know DITA yet, but I'm well versed in XSLT. Not sure if
there's any need for that - other than fixing the page numbers,
of course.

> >> b) you want to return values from a sub-procedure back to local
> >> variables in a calling procedure
> >
> >What's the rationale for preferring an executable SP in this
> >scenario?
>
> Less work for the engine to do.

That's a very good reason.

> >> Understand what SUSPEND actually *does*. It sends a result row
> >> out to the client buffer and waits for the client app to fetch
> >> the row. Execution of the SP is *suspended* until the waiting
> >> row has been fetched.
> >
> >This sounds like some IPC, possibly even a network transaction,
> >is needed for each row. But is that so?
>
> I don't know. You could ask on firebird-devel if it is important
> for you.

I assume that if *you* don't know it cannot be very important.

> There are ways that client apps can "batch" fetches. The engine
> delivers output rows from a selectable SP one by one and keeps the
> execution flow of the SP suspended until it hears that the client
> is no longer waiting for the current contents of the output row
> structure.

Okay.

> >> Furthermore, there are some good reasons why you shouldn't write
> >> SPs that both change data and return sets.
> >
> >What are those reasons? I can think of user expectation. Are there
> >other reasons?
>
> The rows output from a SP are not real data. They are a contrived
> set of values that reflect the state of the underlying data within
> the context of the user's (currently unfinished) transaction.
> Thus, if you hand SP output to the client app during the course of
> a data-changing operation and the client makes decisions according
> to those values, you run the risk of writing bad data if the SP
> excepts.

That makes perfect sense.

> >> >usage as (SELECTABLE) STORED PROCEDURE:
> >> >execute procedure MYPROC( PARAM) returning_values :RESULT
> >>
> >> This is not usage as a selectable SP. The SUSPEND in this
> >> procedure, when invoked with EXECUTE PROCEDURE, is a no-op
> >> (actually, it just behaves as EXIT).
> >
> >Except for (at least) one aspect: Omitting SUSPEND will deny you the
> >possibility to SELECT from the procedure.
>
> I don't get that point.

Irrelevant of what the OP wrote, if you remove the SUSPEND call
and then try to SELECT from it, you'll get an error; if you leave
the SUSPEND in the procedure you can SELECT from it.

> If there is no SUSPEND, a procedure won't return a result from a
> SELECT call. If it has output arguments, it will return a result
> of zero or one rows from an EXECUTE PROCEDURE call. Note that the
> XSQLVAR structure for EXECUTE commands is *different* to that for
> SELECTs. There have been some API drivers around that don't
> support that, so it seemed to me there has been a culture of
> making all SPs selectable ones on principle. ;-)

Which means more work for the engine, as explained above.

> A selectable SP is one that returns a result set from a SELECT
> call. It doesn't have to be one that contains a SELECT call
> inside it.
>
> To be a selectable SP it must
> a) have output parameters declared (and loaded, of course!!)
> and
> b) be written with a SUSPEND after every block that should return
> a row.

Good.

> > -SQL error code = -313
> > -count of column list and variable list do not match
>
> That error is the response to a specific *coding* error in PSQL.
> It means that, in a [FOR] SELECT <list-of-columns>...INTO
> <list-of-variables>... construct, there is a mismatch between the
> column counts in the <list-of-columns> and the <list-of-variables>.
>
> At compile time, the engine knows everything about the structure
> of the output set. In fact, if you try to create a SP in which
> anything about the output structure is indeterminate, it won't
> compile.

Good.

> >> SSPs are for returning multi-row sets to a client or (if you must)
> >> returning a multi-row set to a cursor (fairly inefficiently).
> >
> >Ah, right, because there are no table-valued variables as of FB 2.5,
> >so other than cursors and tables (including global temporary tables)
> >there is no facility to receive the result of a SSP. Which is why
> >you're saying that they're really meant for the client, correct?
>
> Well, sort of, in a way, hmmmmm. Your statement doesn't hold
> totally true...think about derived tables and named cursors (which
> have been with us since v.2.0).
>
> I consider it a mistake to be talking about "table variables":
> it's muddles the thinking. In SQL, the only thing that is a
> "table" is the set that is physically stored on the disk according
> to the specs laid out by its CREATE TABLE definition. Cursors, GTT
> instances, views, derived tables, stored procedure outputs all are
> sets. Variables are supported for cursors and derived tables,
> probably for not much better reason than that the SQL standard
> defines syntaxes that require (or optionally allow) them.

Okay. Some RDBMS docs do talk of table-valued variables, though.
Which I wrongly simplified to tables.

Thanks for your comprehensive answers!

Best,

Michael