You can do conditionals via WHERE clauses, as others have pointed out.
You can also use WHEN clauses on triggers.
Think of it as <statement> IF <condition>. And remember that the
WHERE clause can refer to all sorts of things, including parameters
from the application (something like WHERE @foo = 1).
You can do iteration via INSERT/UPDATE/DELETE if you have tables with
rows to iterate over.
You can make sub-routines (that return no values but have
side-effects) out of INSERTs on VIEWs or TABLEs with triggers that
will use the new row(s) as arguments, and but leave the VIEW/TABLE
itself unaltered. In the case of a TABLE you'd use a BEFORE INSERT
trigger that does a SELECT RAISE(IGNORE) at the end to make sure the
TABLE is left unaltered.
You can use recursive triggers to implement recursion.
This is really an abuse of SQL, of course, but it can be done :) And,
of course, you have SQLite3's various limits to keep in mind (e.g., on
recursion depth).
I've done this sort of thing as a proof of concept, for the fun of it.
Factorial() might be implemented as follows:
PRAGMA recursive_triggers = 1;
CREATE TABLE factorials (n INTEGER PRIMARY KEY, n_factorial INTEGER);
CREATE VIEW factorial AS SELECT n, n_factorial FROM factorials;
CREATE TRIGGER factorial_ins INSTEAD OF INSERT ON factorial BEGIN
SELECT RAISE(FAIL, "n must be non-negative") WHERE NEW.n < 0;
SELECT RAISE(IGNORE) WHERE EXISTS (SELECT f.n FROM factorials f
WHERE f.n = NEW.n);
INSERT INTO factorials SELECT NEW.n, 1 WHERE NEW.n < 2 AND NOT
EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n);
INSERT INTO factorial SELECT NEW.n - 1, NULL WHERE NEW.n > 0 AND
NOT EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1);
INSERT INTO factorials SELECT NEW.n, NEW.n * (SELECT f.n_factorial
FROM factorials f WHERE f.n = NEW.n - 1) WHERE NEW.n > 0 AND EXISTS
(SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1) AND NOT EXISTS
(SELECT f.n FROM factorials f WHERE f.n = NEW.n);
END;
sqlite> select * from factorial;
sqlite> insert into factorial select 5, null;
sqlite> select * from factorial;
0|1
1|1
2|2
3|6
4|24
5|120
sqlite>
(With 64-bit signed integers you can compute up to 20!, but no further.)
Here we have a trigger acting as a sub-routine that doesn't return any
results, but it does ensure that the results are available for a
subsequent query. Another way to think of it is as a method of
building recursive queries in the absence of recursive query support
in SQLite3.
So there you have it: procedural programming in SQL. My advice:
follow the advice that you've been given, which is to say: do your
procedural programming in C or whatever host language you're using,
and use SQL as it was intended to be used.
Cheers,
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
