Introduction to batches

A simple batch consists of a set of SQL statements, separated by semicolons. For example, the following statements form a batch that creates an Eastern Sales department and transfers all sales representatives from Massachusetts (MA) to that department.

INSERT
INTO department ( dept_id, dept_name )
VALUES ( 220, 'Eastern Sales' ) ;

UPDATE employee
SET dept_id = 220
WHERE dept_id = 200
AND state = 'MA' ;

COMMIT ;

You can include this set of statements in an application and execute them together.

dbisql and batches A list of semicolon-separated statements, such as the above, is parsed by dbisql before it is sent to the server. In this case, dbisql sends each statement individually to the server, not as a batch. Unless you have such parsing code in your application, the statements are sent and treated as a batch. Putting a BEGIN and END around a set of statements causes dbisql to treat them as a batch.

Many statements used in procedures can also be used in batches. You can use control statements (CASE, IF, LOOP, and so on), including compound statements (BEGIN and END), in batches. Compound statements can include declarations of variables, exceptions, temporary tables, or cursors inside the compound statement.

The following batch creates a table only if a table of that name does not already exist:

BEGIN
	IF NOT EXISTS (
		SELECT * FROM SYSTABLE
		WHERE table_name = 't1' ) THEN
		CREATE TABLE t1 (
				firstcol INT PRIMARY KEY,
				secondcol CHAR( 30 )
		) ;
	ELSE
		MESSAGE 'Table t1 already exists' ;
	END IF 
END

If you run this batch twice from dbisql, it creates the table the first time you run it and displays the message in the dbisql messages window the next time you run it.