SQL Scripting

This page outlines the SQL scripting options available in Databend. You can use SQL scripting with either of the following query methods:

• Stored Procedure
• EXECUTE IMMEDIATE

Variable Declaration

Variables can be declared using the LET keyword, followed by the variable name, an optional type, and the initial value.

Examples:

LET x := 100;

Query Execution

SQL queries can be executed within the script, and results can be stored in variables or result sets.

Examples:

LET result RESULTSET := SELECT * FROM t1;

Control Flow Constructs

• FOR Loop: Iterates over a range or a result set.

  Examples:

  FOR i IN 1..10 DO ... END FOR;

• WHILE Loop: Executes a block of code as long as a specified condition is true.

  Examples:

  WHILE condition DO ... END WHILE;

• REPEAT Loop: Executes a block of code until a condition is met.

  Examples:

  REPEAT ... UNTIL condition END REPEAT;

• LOOP: Executes a block of code indefinitely until a BREAK statement is encountered.

  Examples:

  LOOP ... END LOOP;

• CASE Statement: Allows conditional execution of code blocks based on different conditions.

  Examples:

  CASE [operand]
  WHEN condition1 THEN ...
  WHEN condition2 THEN ...
  ELSE ...
  END;

• IF Statement: Executes a block of code based on a condition.

  Examples:

  IF condition THEN ...
  ELSEIF condition THEN ...
  ELSE ...
  END IF;

RETURN

Returns from the script with an optional value.

Examples:

RETURN [expr];

RETURN TABLE

Returns from the script with a table result as a String column.

Examples:

EXECUTE IMMEDIATE $$
BEGIN
    CREATE OR REPLACE TABLE t1 (a INT, b FLOAT, c STRING);
    INSERT INTO t1 VALUES (1, 2.0, '3');
    RETURN TABLE(select * from t1);
END;
$$;

┌─────────────────────────────────────────────┐
│                    Result                   │
│                    String                   │
├─────────────────────────────────────────────┤
│ ┌─────────────────────────────────────────┐ │
│ │      a     │       b      │      c      │ │
│ │ Int32 NULL │ Float32 NULL │ String NULL │ │
│ ├────────────┼──────────────┼─────────────┤ │
│ │ 1          │ 2            │ '3'         │ │
│ └─────────────────────────────────────────┘ │
└─────────────────────────────────────────────┘

Comments

• Single-line comments: -- comment
• Multi-line comments: /* comment */