SQLite에서 변수를 선언하고 사용

SQLite에서 변수를 선언하고 사용

---

SQLite에서 변수를 선언하고 insert작동에 사용하고 싶습니다 .

MS SQL에서와 같이 :

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

예를 들어에서 가져 와서 last_insert_row사용해야합니다 insert.

바인딩에 대해 뭔가를 찾았지만 완전히 이해하지 못했습니다.

---

SQLite는 기본 변수 구문을 지원하지 않지만 메모리 내 임시 테이블을 사용하여 거의 동일한 결과를 얻을 수 있습니다.

나는 큰 프로젝트에 아래 접근 방식을 사용했으며 매력처럼 작동합니다.

    /* 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;

---

Herman의 솔루션은 작동하지만 Sqlite는 모든 필드에 모든 값 유형을 저장할 수 있기 때문에 단순화 할 수 있습니다.

다음은 값을 저장하기 위해 Value선언 된 하나의 필드 를 사용하는 더 간단한 버전입니다 TEXT.

CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);

INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');

SELECT Value
  FROM Variables
 WHERE Name = 'VarStr'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarInt'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarBlob';

---

Herman's solution worked for me, but the ... had me mixed up for a bit. I'm including the demo I worked up based on his answer. The additional features in my answer include foreign key support, auto incrementing keys, and use of the last_insert_rowid() function to get the last auto generated key in a transaction.

My need for this information came up when I hit a transaction that required three foreign keys but I could only get the last one with last_insert_rowid().

PRAGMA foreign_keys = ON;   -- sqlite foreign key support is off by default
PRAGMA temp_store = 2;      -- store temp table in memory, not on disk

CREATE TABLE Foo(
    Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE Bar(
    Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FOREIGN KEY(Thing2) REFERENCES Foo(Thing1)
);

BEGIN TRANSACTION;

CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER);

INSERT INTO Foo(Thing1)
VALUES(2);

INSERT INTO _Variables(Key, Value)
VALUES('FooThing', last_insert_rowid());

INSERT INTO Bar(Thing2)
VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing'));

DROP TABLE _Variables;

END TRANSACTION;

---

For read-only variables (using the same value throughout the query) use a Common Table Expression (CTE).

WITH var AS (SELECT 'name' AS name, 10 AS more)
SELECT *, (table.cost + var.more) AS result 
FROM table, var 
WHERE table.name = var.name

SQLite WITH clause

---

Try using Binding Values. You cannot use variables as you do in T-SQL but you can use "parameters". I hope the following link is usefull.Binding Values

참고URL : https://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it