在SQLite中声明变量并使用它 [英] Declare variable in SQLite and use it

问题描述

我想在SQLite中声明一个变量，并在 insert 操作中使用它。

I want to declare a variable in SQLite and use it in insert operation.

就像MS SQL ：

declare @name as varchar(10)
set name = 'name'
select * from table where name = @name

例如，我将需要获取 last_insert_row 并在 insert 中使用它。

For example, I will need to get last_insert_row and use it in insert.

我找到了一些有关绑定的信息，但我没有

I have found something about binding but I didn't really fully understood it.

推荐答案

SQLite不支持本机变量语法，但是您可以使用内存中的临时表。

SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.

我在大型项目中使用了以下方法，并且像魅力一样工作。

I've used the below approach for large projects and works like a charm.

    /* Create in-memory temp table for variables */
    BEGIN;

    PRAGMA temp_store = 2;
    CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);

    /* Declaring a variable */
    INSERT INTO _Variables (Name) VALUES ('VariableName');

    /* Assigning a variable (pick the right storage class) */
    UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';

    /* Getting variable value (use within expression) */
    ... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...

    DROP TABLE _Variables;
    END;

这篇关于在SQLite中声明变量并使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！