Feature: Stored procedure and SQL Scripting

[andylokandy]

Motivation

This feature aims to empower users to develop and execute complex business logic directly within the database environment. By extending beyond basic SQL functionalities, it introduces capabilities such as loops, variables, and branching. This enhancement eliminates the need for client-side application deployment, allowing users to access and execute advanced business logic through a SQL interface.

Detailed design

Syntax

Stored Procedures

• Define a stored procedure:

CREATE OR REPLACE PROCEDURE sample_procedure(param1 UINT64, param2 UINT64)
RETURNS UINT64
LANGUAGE SQL
AS
$$
BEGIN
  -- Procedure logic here
END;
$$;

• Call a stored procedure:

CALL return_greater(2, 3);

Scripting

• Variable Declaration: Variables are declared using the LET keyword, followed by the variable name, optional type, and initial value. Example: LET var_name := value;

• Query Execution: Scripts can execute SQL queries and store results in variables or result sets. Example: LET result RESULTSET := SELECT * FROM t1;

• Control Flow Constructs:

  • FOR Loop: Iterates over a range or a result set. Example: FOR i IN 1..10 DO ... END FOR;
  • WHILE Loop: Executes a block of code as long as a specified condition is true. Example: WHILE condition DO ... END WHILE;
  • REPEAT Loop: Executes a block of code until a condition is met. Example: REPEAT ... UNTIL condition END REPEAT;
  • LOOP: Executes a block of code indefinitely until a BREAK statement is encountered. Example: LOOP ... END LOOP;
  • CASE Statement: Allows conditional execution of code blocks. Example:

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

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

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

  • RETURN: Return from the script with an optional value. Example: RETURN [expr];
  • RETURN TABLE: Return from the script with a table. Example: RETURN TABLE(result_set_name | select ...);

• Comments

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

Compiler

The compiler's role is to parse the scripting syntax and generate an IR for execution by the sidecar application. The IR resembles assembly language instructions. Instructions include:

• QUERY <query>, <result_set>: Executes a SQL query and stores the result in a named result set.
• ITER <result_set>, <iterator>: Initializes an iterator for a given result set.
• READ <iterator>, <column>, <to_var>: Retrieves column value in the current row to a specified variable.
• NEXT <iterator>: Forward the iterator to the next line.
• JUMP_IF_ENDED <iterator>, <label>: Jumps to a specified label if the iterator has reached the end of the result set.
• JUMP_IF_TRUE <variable>, <label>: Conditional jump based on the boolean value of a variable.
• GOTO <label>: Unconditional jump to a specified label.
• RETURN: Terminates the script and returns control to the caller.
• RETURN_VAR <variable>: Returns a specific variable's value from the script.
• RETURN_SET <result_set>: Returns a result set from the script.

Example of IR Implementation

Consider a simple SQL script that calculates the total price from a set of invoices:

LET c1 RESULTSET := SELECT price FROM invoices;
LET total_price := 0.0;
FOR record IN c1 DO
  total_price := total_price + record.price;
END FOR;
RETURN total_price;

The corresponding IR would be:

1.  QUERY 'SELECT price FROM invoices;', c1;
2.  QUERY 'SELECT 0.0', __query0
3.  ITER __query0, __iter1
4.  READ __iter1, $0, total_price
5.  ITER c1, __iter2
6.  __LOOP0:
7.  JUMP_IF_ENDED __iter2, __EXIT_LOOP0
8.  READ __iter2, 'price', __tmp0
9.  QUERY 'SELECT :total_price + :__tmp0;', __query1
10. ITER __query1, __iter3
11. READ __iter3, $0, total_price
12. NEXT __iter2;
13. GOTO __LOOP0
14. __EXIT_LOOP0:
15. RETURN_VAR total_price

Explanation:

1. Intialize c1 with result from executing the query. 2-4. Initialize total_price with 0.0. 5-12. Loop through each record in c1, accumulating the total price.
2. Exit point for the loop.
3. Return the calculated total price.

[TCeason]

• [x] Procedure CRUD https://github.com/datafuselabs/databend/pull/16348
• [x] Procedure CRUD with parameter https://github.com/datafuselabs/databend/pull/16348
• [ ] Call procedure with parameter