SQL Script Overview
A SQL Script is a procedure that employs procedure declaration, parameters, statements, variables, data types, procedure calls, SQL keywords, dynamic SQL, conditionals, loops, cursors (simple and streaming), exceptions, and transactions. The following lists the TDV SQL Script keywords.
Procedure Declaration and Parameters
By default (and as required), the procedure name is the same as the name assigned to it in the resource tree.
PROCEDURE; IN, INOUT, OUT
Procedure Call
CALL
Compound Statement
BEGIN/END
Variables
DECLARE can only follow BEGIN.
DECLARE, SET, DEFAULT
Data Types
DECLARE TYPE, BOOLEAN, ROW, XML
Path to a Resource
PATH
SQL Keywords
SELECT INTO, INSERT, UPDATE, DELETE
Dynamic SQL
EXECUTE IMMEDIATE
Conditionals
IF/THEN/ELSE, CASE/WHEN
Loops
LOOP, WHILE, REPEAT/UNTIL, FOR, ITERATE, LEAVE
Cursors
ROW, CURSOR, OPEN, CLOSE, FETCH, SELECT, PIPE (for streaming)
Exceptions
RAISE, EXCEPTION, CURRENT_EXCEPTION
Transactions
TRANSACTION, INDEPENDENT, COMMIT, ROLLBACK