SQLite loop statements?

问题:

Is there any loop statements in SQLite like FOR .. in .. LOOP or something like that? I have two columns StartRange, EndRange and I need to insert whole sequence in other table. So if StartRange is 1 and EndRange is 3 it's necessary to make three inserts with value, contains 1, 2, 3.

回答1:

You can do this sort of thing in straight SQL if you have an extra table that holds all the integers that you need.

Suppose your StartRange and EndRange range between one and ten and you have a table like this:

sqlite> select i from ints;
i
1
.
.
.
10

This table simply contains all the possible integers that you need (i.e. one through ten).

Then if you also have this:

sqlite> create table t (startrange int not null, endrange int not null);
sqlite> insert into t values(1, 3);
sqlite> create table target (i int not null);

You can do your INSERTs into target with a join:

insert into target (i)
select ints.i
from ints join t on (ints.i >= t.startrange and ints.i <= t.endrange)

The result is this:

sqlite> select * from target;
i
1
2
3

Of course your real t would have more rows so you'd want a WHERE clause to limit which row of t you look at.

Similar things are often done with dates (look up "calendar tables").

So if your ranges are small (for some definition of small) then generate your ints table once, add an index to it, and use the above technique to do all the INSERTs right inside the database. Other databases have their own ways (such as PostgreSQL's generate_series) to do this sort of thing without need an explicit ints table but SQLite is (intentionally) limited.

SQL is generally set-based so loops aren't natural. What is natural is building the appropriate sets by describing what you need. OTOH, sometimes unnatural acts are necessary and sensible.

I don't know if this makes sense for your application, I just thought I'd demonstrate how it can be done. If this approach doesn't make sense in your case then you can generate a bunch of INSERT statements outside the database.

回答2:

You can make loops in SQL with recursive triggers. Using mu is too short's schema

sqlite> create table t (startrange int not null, endrange int not null);
sqlite> insert into t values(1, 3);
sqlite> create table target (i int not null);

we need to enable recursive triggers in SQLite:

sqlite> PRAGMA recursive_triggers = on;

Make a temporary trigger to loop up to the end of the range:

sqlite> create temp trigger ttrig
   ...> before insert on target
   ...> when new.i < (select t.endrange from t) begin
   ...> insert into target values (new.i + 1);
   ...> end;

Kick it off:

sqlite> insert into target values ((select t.startrange from t));
sqlite> select * from target;
3
2
1
sqlite> 

回答3:

Apparently the looping construct in SQLite is the WITH RECURSIVE clause. That documentation link has sample count-to-ten code, a Mandelbrot set plotter, and a Sudoku puzzle solver, all in pure SQL. Here's an SQLite query that computes the Fibonacci sequence to give you a feel for it:

sqlite> WITH RECURSIVE
   ...>   fibo (curr, next)
   ...> AS
   ...>   ( SELECT 1,1
   ...>     UNION ALL
   ...>     SELECT next, curr+next FROM fibo
   ...>     LIMIT 100 )
   ...> SELECT group_concat(curr) FROM fibo;
1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765,10946,...

And here's a Sieve of Eratosthenes:

begin transaction;

drop table if exists naturals;
create table naturals
( n integer unique primary key asc,
  isprime bool,
  factor integer);

with recursive
  nn (n)
as (
  select 2
  union all
  select n+1 as newn from nn
  where newn < 1e4
)
insert into naturals
select n, 1, null from nn;

insert or replace into naturals
  with recursive
    product (prime,composite)
  as (
    select n, n*n as sqr
      from naturals
      where sqr <= (select max(n) from naturals)
    union all
    select prime, composite+prime as prod
    from
      product
    where
      prod <= (select max(n) from naturals)
  )
select n, 0, prime
from product join naturals
  on (product.composite = naturals.n)
;
commit;