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