Using the IF-THEN-ELSE Command Construct Within ttIsql

The IF-THEN-ELSE command construct enables you to implement conditional branching logic in a ttIsql session.

The IF command tests a condition and decides whether to run commands within the THEN clause or the optional ELSE clause. The commands can be SQL statements, SQL scripts, PL/SQL blocks, or TimesTen utilities.

Note:

For details on the syntax of the IF-THEN-ELSE construct, see the ttIsql section in the Oracle TimesTen In-Memory Database Reference.

The following example creates and tests a bind variable to see which type of locking is enabled for the TimesTen database. It uses the autovariables command to create the bind variable from the result of the call to ttConfiguration. The value can be tested within the IF-THEN-ELSE conditional by testing the paramvalue variable.

Note:

For more details on the autovariables command, see Automatically Creating Bind Variables for Retrieved Columns.

Command> SET AUTOVARIABLES ON;
Command> CALL TTCONFIGURATION('LockLevel');
PARAMNAME, PARAMVALUE
< LockLevel, 0 >
1 row found.
Command> IF :paramvalue = 1 THEN "e:Database-level locking is enabled"
> ELSE "e:Row-level locking is enabled";
Row-level locking is enabled

The following example checks to see that the employees table exists. If it does not, it runs the SQL script that creates the employees table; otherwise, a message is printed out.

Command> IF 0 = "SELECT COUNT(*) FROM SYS.TABLES 
WHERE TBLNAME LIKE 'employees';"
THEN "e:EMPLOYEES table already exists"
ELSE "@HR_CRE_TT.SQL;";
EMPLOYEES table already exists