有什么办法可以在Firebird存储的proc内创建本地表变量? [英] Is there any way to create a local table variable inside a Firebird stored proc?

问题描述

在MS SQL Server中，可以声明任何原始类型或表类型的局部变量.该表是普通表，可以像其他表一样在其中运行SELECT，INSERT，UPDATE和DELETE，只是它是局部变量，而不是数据库本身的一部分.

我正在尝试在Firebird中做同样的事情，但是它似乎不喜欢这种语法.

declare variable value int; --works fine
declare variable values table (value int); --Error: "Token unknown (table)"

有没有办法做到这一点? (并且在任何人说使用可选的存储过程"之前，这将行不通.我需要可以动态运行INSERT和SELECT的东西.)

解决方案

Firebird不像SQL Server那样支持表变量.

您可以使用的最接近的方法是全局临时表(需要Firebird 2.1或更高版本)

(v.2.1)全局临时表(GTT)是具有永久元数据但具有临时数据的，存储在系统目录中的表.来自不同连接(或事务，取决于范围)的数据彼此隔离，但是GTT的元数据在所有连接和事务之间共享.

GTT有两种:

具有在引用了指定GTT的连接的整个生命周期中一直存在的数据；和

仅在引用事务的生命周期内保留的数据.

您必须预先创建GTT.

CREATE GLOBAL TEMPORARY TABLE
  ...
  [ON COMMIT <DELETE | PRESERVE> ROWS]

In MS SQL Server, you can declare local variables of any primitive type, or of a table type. This table is a normal table that you can run SELECT, INSERT, UPDATE and DELETE on, just like any other table, except that it's a local variable, not a part of the database itself.

I'm trying to do the same thing in Firebird, but it doesn't seem to like the syntax.

declare variable value int; --works fine
declare variable values table (value int); --Error: "Token unknown (table)"

Is there any way to do this? (And before anyone says "use a selectable stored procedure," that won't work. I need something I can dynamically run INSERT and SELECT on.)

解决方案

Firebird doesn't support table variables the same way SQL Server does.

The close thing you have at your disposal is Global Temporary Tables (Requires Firebird 2.1 or greater)

(v.2.1) Global temporary tables (GTTs) are tables that are stored in the system catalogue with permanent metadata, but with temporary data. Data from different connections (or transactions, depending on the scope) are isolated from each other, but the metadata of the GTT are shared among all connections and transactions.

There are two kinds of GTT:

with data that persists for the lifetime of connection in which the specified GTT was referenced; and

with data that persists only for the lifetime of the referencing transaction.

You have to create the GTT beforehand.

CREATE GLOBAL TEMPORARY TABLE
  ...
  [ON COMMIT <DELETE | PRESERVE> ROWS]

这篇关于有什么办法可以在Firebird存储的proc内创建本地表变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！