""Ann W. Harrison"" ...

>
> At 03:59 AM 11/24/2004, Lester Caine wrote:
>
> >Not knowing how the intermediate results are currently created, I am
> >probably totally wrong, but isn't the internal table created before it
> >is sorted the thing that needs to be 'held over' for the next stage of a
> >complex calculation?
>
> yes, and that's something that people want. But it's not what
> the standard calls temporary tables.

Can we interpret standard's declared local temporary tables (DLTT)
as local variables of stored procedures \ triggers ?

For example :

CREATE PROCEDURE SP (...) RETURNS (...)
AS
DECLARE LOCAL TEMPORARY TABLE T1 (
ID INT NOT NULL PRIMARY KEY,
VAL VARCHAR(255));
BEGIN
...
END

In such interpretation DLTT definition and live scope is the same scope
as every usual variable. Each incarnation of procedure will have it's own
instances of DLTTs of course.

See what standart say (i refer to book "Foundation (SQL/Foundation)" from
August, 2002 version of standart, paragraph "4.14.1 Types of tables")

>A declared local temporary table is a module local temporary table. A module
>local temporary table is a named table defined by a <temporary table

declaration

> in an SQL-client module.

Let interpret "SQL-client module" as any kind of PL-SQL block (SP,
trigger, EXECUTE BLOCK)

>A module local temporary table is effectively materialized the first time it
>is referenced in an SQL-session, and it persists for that SQL-session.

DLTT is effectively materialized the first time it is referenced in it's
own PL-SQL block and persist only at execution time of that PL-SQL block

>A declared local temporary table may be declared in an SQL-client module.

Ok - local variables are declared only in PL-SQL blocks

>A declared local temporary table that is declared in an SQL-client module is
>a named table defined by a <temporary table declaration> that is effectively
>materialized the first time any <externallyinvoked procedure> in the
><SQL-client module definition> that contains the <temporary table
>declaration> is executed.

Ok - any temporary tables effectively materialized the first time it needs

>A declared local temporary table is accessible only by <externally-invoked
>procedure>s in the <SQL-client module definition> that contains the
><temporary table declaration>.

Ok - local variables is accessible only by PL-SQL block that contains the
declaration

>The effective <schema name> of the <schema qualified name> of the declared
>local temporary table may be thought of as the implementation-dependent
>SQL-session identifier associated with the SQL-session and a unique
>implementation-dependent name associated with the <SQL-client module
>definition> that contains the <temporary table declaration>.
>All references to a declared local temporary table are prefixed by ''MODULE'

Ok - since scope of DLTT is scope of PL-SQL block we don't need any kind
of prefix inside this PL-SQL block

Regards,
Vlad