SQLScript Complete Syntax Reference

Procedure Syntax
Function Syntax
Anonymous Block Syntax
Variable Declaration
Control Flow Statements
Cursor Operations
Exception Handling
Table Type Definition
Assignment Statements
- Simple Assignment
- SELECT INTO
DDL Statements
Built-in Functions
Special Constructs

Procedure Syntax

CREATE PROCEDURE

CREATE [OR REPLACE] PROCEDURE <schema_name>.<procedure_name>
  (
    [IN <parameter_name> <sql_type> [DEFAULT <default_value>]],
    [OUT <parameter_name> <sql_type>],
    [INOUT <parameter_name> <sql_type>]
  )
  LANGUAGE SQLSCRIPT
  [SQL SECURITY {DEFINER | INVOKER}]
  [DEFAULT SCHEMA <schema_name>]
  [READS SQL DATA]
  [WITH RESULT VIEW <view_name>]
  [DETERMINISTIC]
AS
BEGIN
  [SEQUENTIAL EXECUTION]
  <procedure_body>
END;

Note: SEQUENTIAL EXECUTION forces the procedure to execute sequentially without parallelism. This is rarely needed but can be useful for procedures that rely on side effects or specific ordering guarantees.

Parameter Modes

Mode	Description
`IN`	Input parameter (read-only within procedure)
`OUT`	Output parameter (write-only, initial value undefined)
`INOUT`	Input and output (read/write)

Security Modes

Mode	Description
`SQL SECURITY DEFINER`	Execute with privileges of procedure owner
`SQL SECURITY INVOKER`	Execute with privileges of caller

Function Syntax

Scalar User-Defined Function

CREATE [OR REPLACE] FUNCTION <schema_name>.<function_name>
  (
    <parameter_name> <sql_type> [DEFAULT <value>],
    ...
  )
RETURNS <return_type>
LANGUAGE SQLSCRIPT
[SQL SECURITY {DEFINER | INVOKER}]
[DEFAULT SCHEMA <schema_name>]
[DETERMINISTIC]
AS
BEGIN
  DECLARE result <return_type>;
  -- function logic
  RETURN result;
END;

Table User-Defined Function

CREATE [OR REPLACE] FUNCTION <schema_name>.<function_name>
  (
    <parameter_name> <sql_type> [DEFAULT <value>],
    ...
  )
RETURNS TABLE (
  <column_name> <sql_type>,
  ...
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
[DEFAULT SCHEMA <schema_name>]
AS
BEGIN
  RETURN SELECT <columns> FROM <source>;
END;

Anonymous Block Syntax

DO [(<parameter_clause>)]
BEGIN [SEQUENTIAL EXECUTION]
  <block_body>
END;

With Parameters

DO (
  IN iv_input INTEGER => 100,
  OUT ov_output INTEGER => ?
)
BEGIN
  ov_output = :iv_input * 2;
END;

Variable Declaration Syntax

Scalar Variables

DECLARE <variable_name> <sql_type> [:= <initial_value>];
DECLARE <var1>, <var2> <sql_type>;  -- Multiple variables same type

Constant Variables

DECLARE <constant_name> CONSTANT <sql_type> := <value>;

Table Variables

-- Inline definition
DECLARE <table_var> TABLE (
  <column_name> <sql_type> [NOT NULL],
  ...
);

-- From existing table structure
DECLARE <table_var> TABLE LIKE <table_name>;
DECLARE <table_var> TABLE LIKE :<other_table_var>;

-- From table type
DECLARE <table_var> <table_type_name>;

Array Variables

DECLARE <array_name> <sql_type> ARRAY;
DECLARE <array_name> <sql_type> ARRAY := ARRAY(<value1>, <value2>, ...);

Assignment Syntax

Scalar Assignment

<variable> := <expression>;
<variable> = <expression>;  -- Alternative syntax

SELECT INTO

SELECT <column> INTO <variable> FROM <table> [WHERE ...];
SELECT <col1>, <col2> INTO <var1>, <var2> FROM <table>;

Table Variable Assignment

<table_var> = SELECT <columns> FROM <table>;
<table_var> = :<other_table_var>;

Control Flow Syntax

IF-THEN-ELSE

IF <condition> THEN
  <statements>
[ELSEIF <condition> THEN
  <statements>]
[ELSE
  <statements>]
END IF;

CASE Expression

-- Simple CASE
CASE <expression>
  WHEN <value1> THEN <result1>
  WHEN <value2> THEN <result2>
  ELSE <default_result>
END

-- Searched CASE
CASE
  WHEN <condition1> THEN <result1>
  WHEN <condition2> THEN <result2>
  ELSE <default_result>
END

WHILE Loop

WHILE <condition> DO
  <statements>
END WHILE;

DO n TIMES Loop

Repeat a block a fixed number of times:

DO <n> TIMES
BEGIN
  <statements>
END;

Example:

DO 10 TIMES
BEGIN
  INSERT INTO "LOG_TABLE" (message) VALUES ('Iteration');
END;

FOR Loop

-- Numeric range (inclusive on both ends)
FOR <var> IN [REVERSE] <start>..<end> DO
  <statements>
END FOR;

-- Cursor iteration
FOR <row_var> AS <cursor_name> DO
  <statements using row_var.column_name>
END FOR;

-- Inline cursor
FOR <row_var> AS (SELECT <columns> FROM <table>) DO
  <statements>
END FOR;

Range Semantics: The numeric FOR loop range is inclusive on both bounds. FOR i IN 1..5 iterates with i = 1, 2, 3, 4, 5 (five iterations total).

LOOP with EXIT

LOOP
  <statements>
  IF <condition> THEN
    BREAK;  -- or LEAVE
  END IF;
  [CONTINUE;]  -- Skip to next iteration
END LOOP;

Cursor Syntax

Declaration

DECLARE CURSOR <cursor_name> FOR
  <select_statement>;

-- With parameters
DECLARE CURSOR <cursor_name> (<param> <type>) FOR
  SELECT * FROM <table> WHERE col = :<param>;

Operations

OPEN <cursor_name>;
OPEN <cursor_name> (<param_value>);

FETCH <cursor_name> INTO <var1>, <var2>, ...;

CLOSE <cursor_name>;

Cursor Attributes

Attribute	Type	Description	Typical Usage
`<cursor>::ISCLOSED`	BOOLEAN	TRUE if cursor is closed	Check before OPEN to avoid "already open" error
`<cursor>::NOTFOUND`	BOOLEAN	TRUE if FETCH found no row	Loop termination condition after FETCH
`<cursor>::ROWCOUNT`	INTEGER	Number of rows fetched so far	Progress tracking, batch processing limits

Usage Example:

WHILE NOT cur::NOTFOUND DO  -- Check NOTFOUND to exit loop
  FETCH cur INTO lv_var;
  IF cur::ROWCOUNT > 1000 THEN  -- Limit processing
    BREAK;
  END IF;
END WHILE;

Table Type Syntax

CREATE TYPE

CREATE TYPE <schema_name>.<type_name> AS TABLE (
  <column_name> <sql_type> [NOT NULL],
  ...
);

DROP TYPE

DROP TYPE <schema_name>.<type_name> [CASCADE];

Exception Handling Syntax

EXIT HANDLER

DECLARE EXIT HANDLER FOR <condition>
  <statement>;

DECLARE EXIT HANDLER FOR <condition>
BEGIN
  <statements>
END;

CONTINUE HANDLER

CONTINUE HANDLER catches exceptions and continues execution of the procedure (unlike EXIT HANDLER which suspends execution):

DECLARE CONTINUE HANDLER FOR <condition>
  <statement>;

DECLARE CONTINUE HANDLER FOR <condition>
BEGIN
  <statements>
END;

Note: Use CONTINUE HANDLER when you want to log errors and continue processing. See references/exception-handling.md for detailed comparison of EXIT vs CONTINUE handlers.

Condition Declaration

DECLARE <condition_name> CONDITION FOR SQL_ERROR_CODE <number>;

SIGNAL

SIGNAL <condition_name>;
SIGNAL <condition_name> SET MESSAGE_TEXT = '<message>';
SIGNAL SQL_ERROR_CODE <number> SET MESSAGE_TEXT = '<message>';

RESIGNAL

RESIGNAL;
RESIGNAL <condition_name>;
RESIGNAL SET MESSAGE_TEXT = '<message>';

Table Variable Operators

INSERT

:<table_var>.INSERT((<value1>, <value2>, ...));
:<table_var>.INSERT(:<other_table_var>);
:<table_var>.INSERT(:<other_table_var>, <row_number>);

UPDATE

:<table_var>.UPDATE((<new_value1>, <new_value2>, ...), <row_number>);

DELETE

:<table_var>.DELETE(<row_number>);
:<table_var>.DELETE(<start_row>, <count>);

SEARCH

<position> = :<table_var>.SEARCH((<column_name>, <search_value>), <start_position>);

UNNEST Function

Convert array to table:

UNNEST(<array1> [, <array2>, ...]) [WITH ORDINALITY]
  AS <table_alias> (<col1> [, <col2>, ...] [, <ordinality_col>])