[Zope-DB] RE: How to put more sql statements into a zsql?

[Michael Mauws]

Since it opened up a lot of possibilities when I discovered this, it's worth
noting that you can execute multiple statements, not to mention doing
anything that's possible in SQL*PLUS, by putting your SQL between
DECLARE/BEGIN and END. For example, instead of writing

UPDATE emp SET salary = salary * 10
<dtml-var sql_delimiter>
UPDATE emp SET salary = salary * 10

you can write

BEGIN
  UPDATE emp SET salary = salary * 10;
  UPDATE emp SET salary = salary * 10;
END;

Where the above becomes particularly helpful is when you want to do looping
and the like. Thus, it allows you to do things like

DECLARE
  CURSOR c1 IS
    SELECT * FROM emp;
BEGIN
  FOR i IN c1 LOOP
    IF i.salary > 1000 THEN
      <insert statements here>
    END IF;
  END LOOP;
END;

(Note that the above is much slower than a single SQL statement and is
simply provided as an example)