. This is not allowed in
CREATE FUNCTION
. Note that a PROCEDURE or a FUNCTION
may have internal tables or return a table which are populated by the
routine's statements. These tables are not considered SQL DATA,
therefore there is no need to specify MODIFIES SQL
DATA
for such routines.
<SQL-data access indication> ::= NO SQL |
CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA
NULL INPUT
null call clause
Null Arguments
The <null-call clause>
is used only for
functions. If a function returns NULL when any of the calling arguments
is null, then by specifying RETURNS NULL ON NULL
INPUT
, calls to the function are known to be redundant and do
not take place when an argument is null. This simplifies the coding of
the SQL/JRT Java methods and improves performance at the same
time.
<null-call clause> ::= RETURNS NULL ON NULL
INPUT | CALLED ON NULL INPUT
SAVEPOINT LEVEL
transaction impact
The <savepoint level indication>
is used
only for procedures and refers to the visibility of existing savepoints
within the body of the procedure. If NEW SAVEPOINT
LEVEL
is specified, savepoints that have been declared prior
to calling the procedure become invisible within the body of the
procedure. HyperSQL’s implementation accepts only NEW SAVEPOINT
LEVEL
, which must be specified.
<savepoint level indication> ::= NEW SAVEPOINT
LEVEL | OLD SAVEPOINT LEVEL
DYNAMIC RESULT SETS
returned result sets characteristic
The <returned result sets characteristic>
is used with SQL/PSM and SQL/JRT procedures (not with functions). The
maximum number of result sets that a procedure may return can be
specified with the clause below. The default is zero. If you want your
procedure to return result sets, you must specify the maximum number of
result sets that your procedure may return. Details are discussed in the
next sections.
<returned result sets characteristic> ::=
DYNAMIC RESULT SETS <maximum returned result
sets>
The PSM (Persistent Stored Module) specification extends the SQL language with structures and control statements such as conditional and loop statements. Both SQL Function and SQL procedure bodies use the same syntax, with minor exceptions.
The routine body is a SQL statement. In its simplest form, the body is a single SQL statement. A simple example of a function is given below:
CREATE FUNCTION an_hour_before (t TIMESTAMP) RETURNS TIMESTAMP RETURN t - 1 HOUR
An example of the use of the function in an SQL statement is given below:
SELECT an_hour_before(event_timestamp) AS notification_timestamp, event_name FROM events;
A simple example of a procedure is given below:
CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50)) MODIFIES SQL DATA INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP)
The procedure inserts a row into an existing table with the definition given below:
CREATE TABLE customers(id INTEGER GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(50), lastname VARCHAR(50), added TIMESTAMP);
An example of the use of the procedure is given below:
CALL new_customer('JOHN', 'SMITH');
The routine body is often a compound statement. A compound statement can contain one or more SQL statements, which can include control statements, as well as nested compound statements.
Please note carefully the use of
<semicolon>
, which is required at the end of some
statements but not accepted at the end of others.
SQL Language Routines (PSM) have certain advantages over Java Language Routines (SQL/JRT) and a couple of disadvantages.
SQL language routines (PSM) do not rely on custom Java classes to be present on the classpath. The databases that use them are therefore more portable.
For a routine that accesses SQL DATA, all the SQL statements in an SQL routine are known and monitored by the engine. The engine will not allow a table, routine or sequence that is referenced in an SQL routine to be dropped, or its structure modified in a way that will break the routine execution. The engine does not keep this information about a Java routine.
Because the statements in an SQL routine are known to the engine, the execution of an SQL routine locks all the database objects it needs to access before the actual execution. With Java routines, locks are obtained during execution and this may cause additional delays in multi threaded access to the database.
For routines that do not access SQL DATA, Java routines (SQL/JRT) may be faster if they perform extensive calculations.
Only Java routines can access external programs and resources directly.
The following SQL Statements can be used only in routines. These statements are covered in this section.
<handler declaration>
<table variable declaration>
<variable declaration>
<declare cursor>
<assignment statement>
<compound statement>
<case statement>
<if statement>
<while statement>
<repeat statement>
<for statement>
<loop statement>
<iterate statement
<leave statement>
<signal statement>
<resignal statement>
<return statement>
<select statement: single
row>
<open statement>
The following SQL Statements can be used in procedures but not in generally in functions (they can be used in functions only to change the data in a local table variable) . These statements are covered in other chapters of this Guide.
<call statement>
<delete statement>
<insert statement>
<update statement>
<merge statement>
As shown in the examples below, the formal parameters and the variables of the routine can be used in statements, similar to the way a column reference is used.
A compound statement is enclosed in a BEGIN / END block with
optional labels. It can contain one or more <SQL variable
declaration>
, <declare cursor>
or
<handler declaration>
before at least one SQL
statement. The BNF is given below:
<compound statement> ::= [ <beginning
label> <colon> ] BEGIN [[NOT] ATOMIC]
[{<SQL variable declaration> <semicolon>}
...]
[{<declare cursor> <semicolon>}
...]
[{<handler declaration> <semicolon>}...]
{<SQL procedure statement> <semicolon>}
...
END [ <ending label> ]
An example of a simple compound statement body is given below. It performs the common task of inserting related data into two table. The IDENTITY value that is automatically inserted in the first table is retrieved using the IDENTITY() function and inserted into the second table. Other examples show more complex compound statements.
CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50), address VARCHAR(100)) MODIFIES SQL DATA BEGIN ATOMIC INSERT INTO customers VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); INSERT INTO addresses VALUES (DEFAULT, IDENTITY(), address); END
A <table variable declaration>
defines
the name and columns of a local table, that can be used in the routine
body. The table cannot have constraints. Table variable declarations are
made before scalar variable declarations.
BEGIN ATOMIC DECLARE TABLE temp_table (col_a INT, col_b VARCHAR(20); DECLARE temp_id INTEGER; -- more statements END
A <variable declaration>
defines the name
and data type of the variable and, optionally, its default value. In the
next example, a variable is used to hold the IDENTITY value. In
addition, the formal parameters of the procedure are identified as input
parameters with the use of the optional IN keyword. This procedure does
exactly the same job as the procedure in the previous example.
CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100)) MODIFIES SQL DATA BEGIN ATOMIC DECLARE temp_id INTEGER; INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); SET temp_id = IDENTITY(); INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address); END
The BNF for variable declaration is given below:
DECLARE variable
SQL variable declaration
<SQL variable declaration> ::= DECLARE
<variable name list> <data type> [DEFAULT <default
value>]
<variable name list> ::= <variable name> [
{ <comma> <variable name> }... ]
Examples of variable declaration are given below. Note that in a DECLARE statement with multiple comma-separated variable names, the type and the default value applies to all the variables in the list:
BEGIN ATOMIC DECLARE temp_zero DATE; DECLARE temp_one, temp_two INTEGER DEFAULT 2; DECLARE temp_three VARCHAR(20) DEFAULT 'no name'; -- more statements ... SET temp_zero = DATE '2010-03-18'; SET temp_two = 5; -- more statements ... END
A <declare cursor>
statement is used to
declare a SELECT statement. The current usage of this statement in early
versions of HyperSQL 2.0 is exclusively to return a result set from a
procedure. The result set is returned to the JDBC CallableStatement
object that calls the procedure. The getResultSet() method of
CallableStatement is then used to retrieve the JDBC ResultSet.
In the <routine definition>
, the
DYNAMIC RESULT SETS
clause must be used to specify a
value above zero. The DECLARE CURSOR
statement is
used after any variable declaration in compound statement block. The
SELECT statement should be followed with FOR READ ONLY to avoid possible
error messages. The <open statement>
is then
executed for the cursor at the point where the result set should be
populated.
After the procedure is executed with a JDBC CallableStatement execute() method, all the result sets that were opened are returned to the JDBC CallableStatement.
Calling getResultSet() will return the first ResultSet. When there are multiple result sets, the getMoreResults() method of the Callable statement is called to move to the next ResultSet, before getResultSet() is called to return the next ResultSet. See the Data Access and Change chapter on the syntax for declaring the cursor.
BEGIN ATOMIC DECLARE temp_zero DATE; DECLARE result CURSOR WITH RETURN FOR SELECT * FROM INFORMATION_SCHEMA.TABLES FOR READ ONLY; -- more statements ... OPEN result; END
A <handler declaration>
defines the
course of action when an exception or warning is raised during the
execution of the compound statement. A compound statement may have one
or more handler declarations. These handlers become active when code
execution enters the compound statement block and remain active in any
sub-block and statement within the block. The handlers become inactive
when code execution leaves the block.
In the previous example of the new_customer
procedure, if an exception is thrown during the execution of either SQL
statement, the execution of the compound statement is terminated and the
exception is propagated and thrown by the CALL statement for the
procedure. All changes made by the procedure are rolled back.
A handler declaration can resolve the thrown exception within the compound statement without propagating it, and allow the execution of the compound statement to continue.
In the example below, the UNDO
handler
declaration catches any exception that is thrown during the execution of
the compound statement inside the BEGIN ... END
block. As it is an UNDO
handler, all the changes to
data performed within the compound statement ( BEGIN ...
END
block) are rolled back. The procedure then returns without
throwing an exception.
CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100)) MODIFIES SQL DATA label_one: BEGIN ATOMIC DECLARE temp_id INTEGER; DECLARE UNDO HANDLER FOR SQLEXCEPTION; INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); SET temp_id = IDENTITY(); INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address); END
Other types of hander are CONTINUE
and
EXIT
handlers. A CONTINUE
handler
ignores any exception and proceeds to the next statement in the block.
An EXIT
handler terminates execution without undoing
the data changes performed by the previous (successful)
statements.
The conditions can be general conditions, or specific conditions.
Among general conditions that can be specified,
SQLEXCEPTION
covers all exceptions,
SQLWARNING
covers all warnings, while NOT
FOUND
covers the not-found condition, which is raised when a
DELETE, UPDATE, INSERT or MERGE statement completes without actually
affecting any row.
Alternatively, one or more specific conditions can be specified
(separated with commas) which apply to specific exceptions or warnings
or classes or exceptions or warnings. A specific condition is specified
with SQLSTATE <value>
, for example
SQLSTATE 'W_01003'
specifies the warning raised after
a SQL statement is executed which contains an aggregate function which
encounters a null value during execution. An example is given below
which activates the handler when either of the two warnings is
raised:
DECLARE UNDO HANDLER FOR SQLSTATE 'W_01003', 'W_01004';
The BNF for <handler declaration>
is
given below:
DECLARE HANDLER
declare handler statement
<handler declaration> ::= DECLARE {UNDO |
CONTINUE | EXIT} HANDLER FOR {SQLEXCEPTION | SQLWARNING | NOT FOUND} | {
SQLSTATE <state value> [, ...]} [<SQL procedure
statement>];
A handler declaration may specify an <SQL procedure
statement>
to be performed when the handler is activated.
In the example below the handler performs the UNDO
as
in the previous example then inserts the (invalid) data into a separate
table.
CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100)) MODIFIES SQL DATA label_one: BEGIN ATOMIC DECLARE temp_id INTEGER; DECLARE UNDO HANDLER FOR SQLEXCEPTION INSERT INTO invalid_customers VALUES(firstanme, lastname, address); -- last statement is part of the handler INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); SET temp_id = IDENTITY(); INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address); END
The <SQL procedure statement>
in the
handler declaration is required by the SQL Standard but is optional in
HyperSQL. If the execution of the <SQL procedure
statement>
specified in the handler declaration throws an
exception itself, then it is handled by the handlers that are currently
active at an enclosing (outer) BEGIN ... END
block.
The <SQL procedure statement>
can itself be a
compound statement with its own handlers.
When a handler handles an exception condition such as the general
SQLEXCEPTION
or some specific
SQLSTATE
, any changes made by the statement that
caused the exception will be rolled back. For example, execution of a
single update statement that modifies several rows will not change any
row if an exception occurs during the update of one of the rows. The
handler action affects the changes made by statements that were executed
successfully before the exception occured.
Actions performed by different types of handler are listed below:
An UNDO
handler rolls back all the data
changes within the BEGIN ... END
block which
contains the handler declaration. The execution of the
BEGIN ... END
block is considered complete. If an
<SQL procedure statement>
is specified, it
is executed after the roll back.
A CONTINUE
handler does not roll back the
data changes. It continues execution as if the last statement was
successful. If an <SQL procedure statement>
is specified, it is executed before continuing execution.
An EXIT
handler does not roll back the data
changes. It aborts the execution of the BEGIN ...
END
block which contains the handler declaration. The
execution of the BEGIN ... END
block is
considered complete, but unlike the UNDO
handler
the actions are not rolled back. If an <SQL procedure
statement>
is specified, it is executed before
aborting.
The SET statement is used for assignment. It can be used flexibly with rows or single values. The BNF is given below:
<assignment statement> ::= <singleton
variable assignment> | <multiple variable
assignment>
<singleton variable assignment> ::= SET
<assignment target> <equals operator> <assignment
source>
<multiple variable assignment> ::= SET
(<variable or parameter>, ...) = <row value
expression>
In the example below, the result of the SELECT is assigned to two OUT or INOUT arguments. The SELECT must return one row. If it returns more than one, an exception is raised. If it returns no row, no change is made to ARG1 and ARG2.
SET (arg1, arg2) = (SELECT col1, col2 FROM atable WHERE id = 10);
In the example below, the result of a function call is assigned to VAR1.
SET var1 = SQRT(var2);
A special form of SELECT can also be used for assigning values from a query to one or more arguments or variables. This works similar to a SET statement that has a SELECT statement as the source.
SELECT : SINGLE ROW
select statement: single row
<select statement: single row> ::= SELECT [
<set quantifier> ] <select list> INTO <select target
list> <table expression>
<select target list> ::= <target
specification> [ { <comma> <target specification> }...
]
Retrieve values from a specified row of a table and assign the fields to the specified targets. The example below has an identical effect to the example of SET statement given above.
SELECT col1, col2 INTO arg1, arg2 FROM atable WHERE id = 10;
Each parameter of a procedure can be defined as IN, OUT or INOUT. An IN parameter is an input to the procedure and is passed by value. The value cannot be modified inside the procedure body. An OUT parameter is a reference for output. An INOUT parameter is a reference for both input and output. An OUT or INOUT parameter argument is passed by reference, therefore only a dynamic parameter argument or a variable within an enclosing procedure can be passed for it. The assignment statement is used to assign a value to an OUT or INOUT parameter.
In the example below, the procedure is declared with an OUT parameter. It assigns the auto-generated IDENTITY value from the INSERT statement to the OUT argument.
CREATE PROCEDURE new_customer(OUT newid INT, IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100)) MODIFIES SQL DATA BEGIN ATOMIC DECLARE temp_id INTEGER; INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); SET temp_id = IDENTITY(); INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address); SET newid = temp_id; END
In the SQL session, or in the body of another stored procedure, a
variable must be assigned to the OUT parameter. After the procedure
call, this variable will hold the new identity value that was generated
inside the procedure. If the procedure is called directly, using the
JDBC CallableStatement interface, then the value of the first, OUT
argument can be retrieved with a call to
getInt(1)
after calling the execute() method.
In the example below, a session variable,
the_new_id
is declared. After the call to
new_customer
, the value for the identity is stored in
the_new_id
variable. This is returned via the next
CALL statement. Alternatively, the_new_id
can be used
as an argument to another CALL statement.
DECLARE the_new_id INT DEFAULT NULL; CALL new_customer(the_new_id, 'John', 'Smith', '10 Parliament Square'); CALL the_new_id;
Various iterated statements can be used in routines. In these
statements, the <SQL statement list>
consists
of one or more SQL statements. The <search
condition>
can be any valid SQL expression of BOOLEAN
type.
LOOP
loop statement
<loop statement> ::= [ <beginning label>
<colon> ] LOOP <SQL statement list> END LOOP [ <ending
label> ]
WHILE
while statement
<while statement> ::= [ <beginning label>
<colon> ] WHILE <search condition> DO <SQL statement
list> END WHILE [ <ending label> ]
REPEAT
repeat statement
<repeat statement> ::= [ <beginning label>
<colon> ]
REPEAT <SQL statement list> UNTIL <search
condition> END REPEAT [ <ending label>
In the example below, a multiple rows are inserted into a table in a WHILE loop:
loop_label: WHILE my_var > 0 DO INSERT INTO CUSTOMERS VALUES (DEFAULT, my_var); SET my_var = my_var - 1; IF my_var = 10 THEN SET my_var = 8; END IF; IF my_var = 22 THEN LEAVE loop_label; END IF; END WHILE loop_label;
The <for statement>
is similar to other
iterated statement, but it is always used with a cursor declaration to
iterate over the rows of the result set of the cursor and perform
operations using the values of each row.
FOR
for statement
<for statement> ::= [ <beginning label>
<colon> ] FOR <query expression> DO <SQL statement
list> END FOR [ <ending label> ]
The <query expression> is a SELECT statement. When the FOR
statement is executed, the query expression is executed first and the
result set is formed. Then for each row of the result set, the
<SQL statement list>
is executed. What is
special about the FOR statement is that all the columns of the current
row can be accessed by name in the statements in the <SQL
statement list>
. The columns are read only and cannot be
updated. For example, if the column names for the select statement are
ID, FIRSTNAME, LASTNAME, then these can be accessed as a variable name.
The column names must be unique and not equivalent to any parameter or
variable name in scope.
The FOR statement is useful for computing values over multiple rows of the result set, or for calling a procedure for some row of the result set.
In the example below, the procedure uses a FOR statement to iterate over the rows for a customer with lastname equal to name_p. No action is performed for the first row, but for all the subsequent rows, the row is deleted from the table.
Note the following: The result set for the SELECT statement is built only once, before processing the statements inside the FOR block begins. For all the rows of the SELECT statement apart from the first row, the row is deleted from the customer table. The WHERE condition uses the automatic variable id, which holds the customer.id value for the current row of the result set, to delete the row. The procedure updates the val_p argument and when it returns, the val_p represents the total count of rows with the given lastname before the duplicates were deleted.
CREATE PROCEDURE test_proc(INOUT val_p INT, IN lastname_p VARCHAR(20)) MODIFIES SQL DATA BEGIN ATOMIC SET val_p = 0; for_label: FOR SELECT * FROM customer WHERE lastname = lastname_p DO IF val_p > 0 THEN DELETE FROM customer WHERE customer.id = id; END IF; SET val_p = val_p + 1; END FOR for_label; END
There are two types of CASE ... WHEN statement and the IF ... THEN statement.
CASE WHEN
case when statement
The simple case statement uses a <case
operand>
as the predicand of one or more predicates. For
the right part of each predicate, it specifies one or more SQL
statements to execute if the predicate evaluates TRUE. If the ELSE
clause is not specified, at least one of the search conditions must be
true, otherwise an exception is raised.
<simple case statement> ::= CASE <case
operand> <simple case statement when clause>... [ <case
statement else clause> ] END CASE
<simple case statement when clause> ::= WHEN
<when operand list> THEN <SQL statement
list>
<case statement else clause> ::= ELSE <SQL
statement list>
A skeletal example is given below. The variable var_one is first tested for equality with 22 or 23 and if the test evaluates to TRUE, then the INSERT statement is performed and the statement ends. If the test does not evaluate to TRUE, the next condition test, which is an IN predicate, is performed with var_one and so on. The statement after the ELSE clause is performed if none the previous tests returns TRUE.
CASE var_one WHEN 22, 23 THEN INSERT INTO t_one ...; WHEN IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...; ELSE UPDATE t_one ...; END CASE
The searched case statement uses one or more search conditions, and for each search condition, it specifies one or more SQL statements to execute if the search condition evaluates TRUE. An exception is raised if there is no ELSE clause and none of the search conditions evaluates TRUE.
<searched case statement> ::= CASE <searched
case statement when clause>... [ <case statement else clause> ]
END CASE
<searched case statement when clause> ::= WHEN
<search condition> THEN <SQL statement
list>
The example below is partly a rewrite of the previous example, but a new condition is added:
CASE WHEN var_one = 22 OR var_one = 23 THEN INSERT INTO t_one ...; WHEN var_one IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...; WHEN var_two IS NULL THEN UPDATE t_one ...; ELSE UPDATE t_one ...; END CASE
IF
if statement
The if statement is very similar to the searched case statement. The difference is that no exception is raised if there is no ELSE clause and no search condition evaluates TRUE.
<if statement> ::= IF <search condition>
<if statement then clause> [ <if statement elseif clause>...
] [ <if statement else clause> ] END IF
<if statement then clause> ::= THEN <SQL
statement list>
<if statement elseif clause> ::= ELSEIF
<search condition> THEN <SQL statement
list>
<if statement else clause> ::= ELSE <SQL
statement list>
The RETURN statement is required and used only in functions. The body of a function is either a RETURN statement, or a compound statement that contains a RETURN statement.
The return value of a FUNCTION can be assigned to a variable, or used inside an SQL statement.
An SQL/PSM function or an SQL/JRT function can return a single result when the function is defined as RETURNS TABLE ( .. )
To return a table from a SELECT statement, you should use a return statement such as RETURN TABLE( SELECT ...) in an SQL/PSM function. For an SQL/JRT function, the Java method should return a JDBCResultSet instance.
To call a function from JDBC, use a java.sql.CallableStatement
instance. The getResultSet()
call can be used to
access the ResultSet returned from a function that returns a result set.
If the function returns a scalar value, the returned result has a single
column and a single row which contains the scalar returned value.
RETURN
return statement
<return statement> ::= RETURN <return
value>
<return value> ::= <value expression> |
NULL
Return a value from an SQL function. If the function is defined as RETURNS TABLE, then the value is a TABLE expression such as RETURN TABLE(SELECT ...) otherwise, the value expression can be any scalar expression. In the examples below, the same function is written with or without a BEGIN END block. In both versions, the RETURN value is a scalar expression.
CREATE FUNCTION an_hour_before_max (e_type INT) RETURNS TIMESTAMP RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR CREATE FUNCTION an_hour_before_max (e_type INT) RETURNS TIMESTAMP BEGIN ATOMIC DECLARE max_event TIMESTAMP; SET max_event = SELECT MAX(event_time) FROM atable WHERE event_type = e_type; RETURN max_event - 1 HOUR; END
In addition to the RETURN statement, the following statements can be used in specific contexts.
ITERATE STATEMENT
The ITERATE statement can be used to cause the next iteration of a labelled iterated statement (a WHILE, REPEAT or LOOP statement). It is similar to the "continue" statement in C and Java.
<iterate statement> ::= ITERATE <statement
label>
LEAVE STATEMENT
The LEAVE statement can be used to leave a labelled block. When used in an iterated statement, it is similar to the "break" statement is C and Java. But it can be used in compound statements as well.
<leave statement> ::= LEAVE <statement
label>
Signal and Resignal Statements allow the routine to throw an exception. If used with the IF or CASE conditions, the exception is thrown conditionally.
SIGNAL
signal statement
The SIGNAL statement is used to throw an exception (or force an exception). When invoked, any exception handler for the given exception is in turn invoked. If there is no handler, the exception is propagated to the enclosing context. In its simplest form, when there is no exception handler for the given exception, routine execution is halted, any change of data is rolled back and the routine throws the exception. By default, the message for the exception is taken from the predefined exception message for the specified SQLSTATE. A custom message can be specified with the optional SET clause.
<signal statement> ::= SIGNAL SQLSTATE <state
value> [ SET MESSAGE_TEXT = <character string literal> ]
RESIGNAL
resignal statement
The RESIGNAL statement is used to throw an exception from an
exception handler's <SQL procedure statement>
,
in effect propagating the exception to the enclosing context without
further action by the currently active handlers. By default, the message
for the exception is taken from the predefined exception message for the
specified SQLSTATE. A custom message can be specified with the optional
SET clause.
<resignal statement> ::= RESIGNAL SQLSTATE
<state value> [ SET MESSAGE_TEXT = <character string
literal> ]
More than one version of a routine can be created.
For procedures, the different versions must have different parameter counts. When the procedure is called, the parameter count determines which version is called.
For functions, the different versions can have the same or different parameter counts. When the parameter count of two versions of a function is the same, the type of parameters must be different. When the function is called, the best matching version of the function is used, according to both the parameter count and parameter types. The return type of different versions of a function can be the same or different.
Two versions of an overloaded function are given below. One version accepts TIMESTAMP while the other accepts TIME arguments.
CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP) RETURNS TIMESTAMP IF t > CURRENT_TIMESTAMP THEN RETURN CURRENT_TIMESTAMP; ELSE RETURN t - 1 HOUR; END IF CREATE FUNCTION an_hour_before_or_now(t TIME) RETURNS TIME CASE t WHEN > CURRENT_TIME THEN RETURN CURRENT_TIME; WHEN >= TIME'01:00:00' THEN RETURN t - 1 HOUR; ELSE RETURN CURRENT_TIME; END CASE
It is perfectly possible to have different versions of the routine as SQL/JRT or SQL/PSM routines.
The OUT or INOUT parameters of a PROCEDURE are used to assign simple values to dynamic parameters or to variables in the calling context.
According to the Standard, an SQL/PSM or SQL/JRT procedure may also return result sets to the calling context. These result sets are dynamic in the sense that a procedure may return a different number of result sets or none at all in different invocations. The SQL Standard uses a mechanism called CURSORS for accessing and modifying rows of a result set one by one. This mechanism is necessary when the database is accessed from an external application program. The JDBC ResultSet interface allows this method of access from Java programs and is supported by HyperSQL.
HyperSQL support this method of returning single or multiple result sets from SQL/PSM procedures only via the JDBC CallableStatement interface. Cursors are declared and opened within the body of the procedure. No further operation is performed on the cursors within the procedure. When the execution of the procedure is complete, the cursors become available as Java ResultSet objects via the CallableStatement instance that called the SQL/PSM procedure.
The JDBC CallableStatement class is used with the SQL statement
CALL <routine name> ( <argument 1>, ... )
to call procedures (also to call functions). After the call to
execute(), the getXXX()
methods can be used to
retrieve INOUT or OUT arguments after the call. The getMoreResults()
method and the getResultSet()
method can be used to
access the ResultSet(s) returned by a procedure that returns one or more
results. If the procedure returns more than one result set, the
getMoreResults()
call moves to the next
result.
In the example below, the procedure inserts a row into the
customer table. It then performs the SELECT statement to return the
latest inserted row as a result set. Therefore the definition includes
the DYNAMIC RESULT SETS 1
clause. You must specify
correctly the maximum number of result sets that the procedure may
return.
CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50)) MODIFIES SQL DATA DYNAMIC RESULT SETS 1 BEGIN ATOMIC DECLARE result CURSOR FOR SELECT * FROM CUSTOMERS WHERE ID = IDENTITY(); INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP); OPEN result; END
The above procedure is called in Java using a CallableStatement
Connection conn = ...; CallableStatement call = conn.prepareCall("call new_customer(?, ?)"); call.setString(1, "Paul"); call.setString(2, "Smith"); call.execute(); if (call.getMoreResults()) ResultSet result = call.getResultSet();
In the example below a procedure has one IN argument and two OUT arguments. The JDBC CallableStatement is used to retrieve the values returned in the OUT arguments.
CREATE PROCEDURE get_customer(IN id INT, OUT firstname VARCHAR(50), OUT lastname VARCHAR(50)) READS SQL DATA BEGIN ATOMIC -- this statement uses the id to get firstname and lastname SELECT first_name, last_name INTO firstname, lastname FROM customers WHERE cust_id = id; END Connection conn = ...; CallableStatement call = conn.prepareCall("call get_customer(?, ?, ?)"); call.setInt(1, 121); // only the IN (or INOUT) arguments should be set before the call call.execute(); String firstname = call.getString(2); // the OUT (or INOUT) arguments are retrieved after the call String lastname = call.getString(3);
SQL/JRT procedures are discussed in the Java Language Procedures section below. Those routines are called exactly the same way as SQL/PSM procedures, using the JDBC CallableStatement interface.
It is also possible to use a JDBC Statement or PreparedStatement object to call a procedure if the procedure arguments are constant. If the procedure returns one or more result sets, the Statement#getMoreResults() method should be called before retrieving the ResultSet.
Java functions are called from JDBC similar to procedures. With functions, the getMoreResuls() method should not be called at all.
Routines can be recursive. Recursive functions are often functions that return arrays or tables. To create a recursive routine, the routine definition must be created first with a dummy body. Then the ALTER ROUTINE statement is used to define the routine body.
In the example below, the table contains a tree of rows each with a parent. The routine returns an array containing the id list of all the direct and indirect children of the given parent. The routine appends the array variable id_list with the id of each direct child and for each child appends the array with the id array of its children by calling the routine recursively.
The routine can be used in a SELECT statement as the example shows.
CREATE TABLE ptree (pid INT, id INT); INSERT INTO ptree VALUES (NULL, 1) ,(1,2), (1,3),(2,4),(4,5),(3,6),(3,7); -- the function is created and always throws an exception when used CREATE FUNCTION child_arr(p_pid INT) RETURNS INT ARRAY SPECIFIC child_arr_one READS SQL DATA SIGNAL SQLSTATE '45000' -- the actual body of the function is defined, replacing the statement that throws the exception ALTER SPECIFIC ROUTINE child_arr_one BEGIN ATOMIC DECLARE id_list INT ARRAY DEFAULT ARRAY[]; for_loop: FOR SELECT id FROM ptree WHERE pid = p_pid DO SET id_list[CARDINALITY(id_list) + 1] = id; SET id_list = id_list || child_arr(id); END FOR for_loop; RETURN id_list; END -- the function can now be used in SQL statements SELECT * FROM TABLE(child_arr(2))
In the next example, a table with two columns is returned instead of an array. In this example, a local table variable is declared and filled with the children and the children's children.

The general features of SQL-Invoked Routines are shared between PSM and JRT routines. These features are covered in the previous section. This section deals with specific aspects of JRT routines.
The body of a Java language routine is a static method of a Java
class, specified with a fully qualified method name in the routine
definition. A simple CREATE FUNCTION example is given below, which defines
the function to call the java.lang.Math.sinh(double d)
Java method. The function can be called in SQL statements just like any
built-in function.
CREATE FUNCTION sinh(v DOUBLE) RETURNS DOUBLE LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:java.lang.Math.sinh' SELECT sinh(doublecolumn) FROM mytable
In the example below, the static method named
toZeroPaddedString
is specified to be called when
the function is invoked.
CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT) RETURNS CHAR VARYING(100) LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString'
The signature of the Java method (used in the Java code but not in SQL code to create the function) is given below:
public static String toZeroPaddedString(long value, int precision, int maxSize)
The parameter and return types of the SQL routine definition must match those of the Java method according to the table below:
|
SMALLINT |
short or Short |
|
INT |
int or Integer |
|
BIGINT |
long or Long |
|
NUMERIC or DECIMAL |
BigDecimal |
|
FLOAT or DOUBLE |
double or Double |
|
CHAR or VARCHAR |
String |
|
DATE |
java.sql.Date |
|
TIME |
java.sql.Time |
|
TIMESTAMP |
java.sql.Timestamp |
|
BINARY |
Byte[] |
|
BOOLEAN |
boolean or Boolean |
| ARRAY of any type | java.sql.Array |
|
TABLE |
java.sql.ResultSet |
If the specified Java method is not found or its parameters and
return types do not match the definition, an exception is raised. If more
than one version of the Java method exist, then the one with matching
parameter and return types is found and registered. If two “equivalent”
methods exist, the first one is registered. (This situation arises only
when a parameter is a primitive in one version and an Object in another
version, e.g. long
and
java.lang.Long
.).
When the Java method of an SQL/JRT routine returns a value, it
should be within the size and precision limits defined in the return type
of the SQL-invoked routine, otherwise an exception is raised. The scale
difference are ignored and corrected. For example, in the above example,
the RETURNS CHAR VARYING(100)
clause limits the length
of the strings returned from the Java method to 100. But if the number of
digits after the decimal point (scale) of a returned BigDecimal value is
larger than the scale specified in the RETURNS clause, the decimal
fraction is silently truncated and no exception of warning is
raised.
When the function is specified as RETURNS TABLE(...) the static Java method should return a JDBCResultSet instance. For an example of how to construct a JDBCResultSet for this purpose, see the source code for the org.hsqldb.jdbc.JDBCArray class.
If two versions of the same SQL invoked routine with different parameter types are required, they can be defined to point to the same method name or different method names, or even methods in different classes. In the example below, the first two definitions refer to the same method name in the same class. In the Java class, the two static methods are defined with corresponding method signatures.
In the third example, the Java function returns a result set and the SQL declaration includes RETURNS TABLE.
CREATE FUNCTION an_hour_before_or_now(t TIME) RETURNS TIME NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour' CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP) RETURNS TIMESTAMP NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour' CREATE FUNCTION testquery(i INTEGER) RETURNS TABLE(n VARCHAR(20), i INT) READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.TestJavaFunctions.getQueryResult'
In the Java class the definitions are as follows. Note the definition of the getQueryResult method begins with a java.sql.Connection parameter. This parameter is ignored when choosing the Java method. The parameter is used to pass the current JDBC connection to the Java method.
public static java.sql.Time nowLessAnHour(java.sql.Time value) { ... } public static java.sql.Timestamp nowLessAnHour(java.sql.Timestamp value) ... } public static ResultSet getQueryResult(Connection connection, int i) throws SQLException { Statement st = connection.createStatement(); return st.executeQuery("SELECT * FROM T WHERE I < " + i); }
Java procedures are defined similarly to functions. The differences are:
The return type of the Java static method must be void.
If a parameter is defined as OUT or INOUT, the corresponding Java static method parameter must be defined as an array of the JDBC non-primitive type.
When the Java static method is invoked, the OUT and INOUT arguments are passed as a single-element array.
The static method can modify the OUT or INOUT argument by assigning a value to the sole element of the argument array.
A procedure can return one or more result sets. These are instantiated as JDBC ResultSet objects by the Java static and returned in array arguments of the method. The signature of the Java method for a procedure that has N declared parameters and returns M result sets has the following pattern. The N parameters corresponding to the signature of the declared SQL procedure are defined first, followed by M parameters as ResultSet arrays.
When the SQL procedure is executed, the Java method is called with single element array arguments passed for OUT and INOUT SQL parameters, and single element arrays of ResultSet for the returned ResultSet objects. The Java method may call the execute() or executeQuery() methods of JDBC Statement or PreparedStatement objects that are declared within the method and assign the ResultSet objects to the first element of each ResultSet[] argument. For the returned ResultSet objects, the Java method should not call the methods of java.sql.ResultSet before returning.
void methodName(<arg1>, ... <argN>,
ResultSet[] r1, ..., ResultSet[] rM)
If the procedure contains SQL statements, only statements for data access and manipulation are allowed. The Java method should not perform commit or rollback. The SQL statements should not change the session settings and should not include statements that create or alter tables or other database objects. These rules are generally enforced by the engine, but additional enforcement may be added in future versions
An example of a procedure definition, together with its Java signature, is given below. This procedure is the SQL/JRT version of the example discussed above for SQL/PSM.
CREATE PROCEDURE get_customer(IN id INT, OUT firstname VARCHAR(50), OUT lastname VARCHAR(50)) READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.getCustomerProcedure' public static void getCustomerProcedure(int id, String[] firstn, String[] lastn) throws java.sql.SQLException { firstn[0] = somevalue; lastn[0] = somevalue; }
In the next example a procedure is defined to return a result set. The signature of the Java method is also given. The Java method assigns a ResultSet object to the zero element of the result parameter.
CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50)) MODIFIES SQL DATA LANGUAGE JAVA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.newCustomerProcedure' public static void newCustomerProcedure(String firstn, String lastn, String addr, ResultSet[] result) throws java.sql.SQLException { result[0] = someresultset; }
Java language procedures SQL/JRT are used in an identical manner to SQL/PSM routines. See the section under SQL/PSM routines, Returning Data From Procedures, on how to use the JDBC CallableStatement interface to call the procedure and to get the OUT and INOUT arguments and to use the ResultSet objects returned by the procedure.
The static methods that are used for procedures and functions must be declared in a public class. The methods must be declared as public static. For functions, the method return type must be one of the JDBC supported types. The IN parameters of the method must be declared as one of the supported types. The OUT and INOUT parameters must be declared as Java arrays of supported types. If the SQL definition of a function includes RETURNS NULL ON NULL INPUT, then the IN parameters of the Java static function can be int or long primitives, otherwise, they must be Integer or Long. The declared Java arrays for OUT and INOUT parameters for SQL INTEGER or BIGINT must be Integer[] or Long[] respectively.
If the SQL definition of the routine includes NO SQL, then no JDBC method call is allowed to execute in the method body. Otherwise, a JDBC Connection can be used within the Java method to access the database. If the definition includes CONTAINS SQL, then no table data can be read. If the definition includes READS SQL DATA, then no table data can be modified. If the definition includes MODIFIES SQL DATA, then data can be modified. In all modes, it is not allowed to execute DDL statements that change the schema definition.
It is possible to use DECLARE LOCAL TEMPORARY TABLE in a Java method, as this is in the session scope.
There are two ways to use the JDBC Connection object.
Define the Java method with a Connection parameter as the first parameter. This parameter is "hidden" and only visible to the engine. The rest of the parameters, if any, are used to choose the method according to the required types of parameters.
Use the SQL/JRT Standard
"jdbc:default:connection"
method. With this
approach, the Java method does not include a Connection parameter.
In the method body, the connection is established with a method call
to DriverManager, as in the example below:
Connection con =
DriverManager.getConnection("jdbc:default:connection");
Both methods return a connection that is based on the current session. This connection has some extra properties, for example, the Close() method does not actually close it.
An example of an SQL PROCEDURE with its Java method definition is given below. The CREATE PROCEDURE statement is the same with or without the Connection parameter:
CREATE PROCEDURE proc1(IN P1 INT, IN P2 INT, OUT P3 INT) SPECIFIC P2 LANGUAGE JAVA DETERMINISTIC MODIFIES SQL DATA EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procTest2'");
In the first example, the
"jdbc:default:connection"
method is used. In the
second example, a connection parameter is used
public static void procTest2(int p1, int p2, Integer[] p3) throws java.sql.SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); java.sql.Statement stmt = conn.createStatement(); stmt.execute("INSERT INTO MYTABLE VALUES(" + p1 + ",'test1')"); stmt.execute("INSERT INTO MYTABLE VALUES(" + p2 + ",'test2')"); java.sql.ResultSet rs = stmt.executeQuery("select * from MYTABLE"); java.sql.ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); p3[0] = Integer.valueOf(cols); rs.close(); stmt.close(); } // alternative declaration with Connection parameter // public static void procTest2(Connection conn, int p1, int p2, // Integer[] p3) throws java.sql.SQLException {
When the stored procedure is called by the user's program, the value of the OUT parameter can be read after the call.
// a CallableStatement is used to prepare the call // the OUT parameter contains the return value CallableStatement c = conn.prepareCall("call proc1(1,2,?)"); c.execute(); int value = c.getInt(1);
The legacy HyperSQL statement, CREATE ALIAS <name>
FOR <fully qualified Java method name>
is no longer
supported directly. It is supported when importing databases and
translates to a special CREATE FUNCTION <name>
statement that creates the function in the PUBLIC schema.
The direct use of a Java method as a function is still supported
but deprecated. It is internally translated to a special CREATE
FUNCTION
statement where the name of the function is the
double quoted, fully qualified name of the Java method used.
By default, the static methods of any class that is on the
classpath are available to be used. This can compromise security in some
systems. The optional Java system property
hsqldb.method_class_names
allows preventing access to
classes other than java.lang.Math
or specifying a
semicolon-separated list of allowed classes. A property value that ends
with .* is treated as a wild card and allows access to all class or
method names formed by substitution of the * (asterisk).
In the example below, the property has been included as an argument to the Java command.
java -Dhsqldb.method_class_names="org.me.MyClass;org.you.YourClass;org.you.lib.*" [the rest of the command line]
The above example allows access to the methods in the two classes:
org.me.MyClass
and
org.you.YourClass
together with all the classes
in the org.you.lib
package. Note that if the
property is not defined, no access control is performed at this
level.
Once the routine has been defined, the normal database access control still applies. The routine can be executed only by the users who have been granted EXECUTE privileges on it. The user who executes a Java routine must also have the relevant access privileges on the tables that are used inside the Java method.
The definition of SQL/JRT routines referencing the user's Java static methods is stored in the .script file of the database.
If the database is opened in a Java environment that does not have access to the referenced Java static methods on its classpath, the SQL/JRT routines are not created when the database is opened. When the database is closed, the routine definitions are lost.
There is a workaround to prevent opening the database when the
static method are not on the classpath. You can create an SQL/PSM
procedure which calls all the SQL/JRT functions and procedures in your
database. The calls should have the necessary dummy arguments. This
procedure will fail to be created when the referenced methods are not
accessible and will return "Error in script file". There is no need ever
to execute the procedure. However, to avoid accidental use, you can
ensure that it does not execute the SQL/JRT routines by adding a line
such as IF TRUE THEN RETURN;
before any references to
the SQL/JRT routines.
HyperSQL adds an extension to the SQL Standard to allow user-defined aggregate functions. A user-defined aggregate function has a single parameter when it is used in SQL statements. Unlike the predefined aggregate functions, the keyword DISTINCT cannot be used when a user defined aggregate function is invoked. Like all user-defined functions, an aggregate function belongs to a schema and can be polymorphic (using multiple function definitions with the same name but different parameter types).
A user defined aggregate function can be used in SQL statements where a predefined aggregate function is allowed.
An aggregate function is always defined with 4 parameters. The first parameter is the parameter that is used when the function is invoked in SQL statements, the rest of the parameter are invisible to the invoking SQL statement. The type of the first parameter is user defined. The type of the second parameter must be BOOLEAN. The third and fourth parameters have user defined types and must be defined as INOUT parameters. The defined return type of the function determines the type of the value returned when the function is invoked.
CREATE AGGREGATE FUNCTION
user defined aggregate function definition
Aggregate function definition is similar to normal function
definition and has the mandatory <returns
clause>
. The BNF is given below.
<user defined aggregate function> ::= CREATE
AGGREGATE FUNCTION <schema qualified routine name> <SQL
aggregate parameter declaration list> <returns clause>
<routine characteristics> <routine body>
The parameter declaration list BNF is given below. The type of the first parameter is used when the function is invoked as part of an SQL statement. When multiple versions of a function are required, each version will have the first parameter of a different type.
<SQL aggregate declaration list> ::= <left
paren> [IN] [ <SQL parameter name> ] <parameter type>
<comma> [IN] [ <SQL parameter name> ] BOOLEAN <comma>
INOUT [ <SQL parameter name> ] <parameter type>
<comma> INOUT [ <SQL parameter name> ] <parameter
type> <right paren>
The return type is user defined. This is the type of the resulting value when the function is called. Usually an aggregate function is defined with CONTAINS SQL, as it normally does not read the data in database tables, but it is possible to define the function with READS SQL DATA and access the database tables.
When a SQL statement that uses the aggregate function is executed, HyperSQL invokes the aggregate function, with all the arguments set, once per each row in order to compute the values. Finally, it invokes the function once more to return the final result.
In the computation phase, the first argument is the value of the user argument as specified in the SQL statement, computed for the current row. The second argument is the boolean FALSE. The third and fourth argument values can have any type and are initially null, but they can be updated in the body of the function during each invocation. The third and fourth arguments act as registers and hold their values between invocations. The return value of the function is ignored during the computation phase (when the second parameter is FALSE).
After the computation phase, the function is invoked once more to get the final result. In this invocation, the first argument is NULL and the second argument is boolean TRUE. The third and fourth arguments hold the values they held at the end of the last invocation. The value returned by the function in this invocation is used as the result of the aggregate function computation in the invoking SQL statement. In SQL queries with GROUP BY, the call sequence is repeated separately for each separate group.
The example below features a user defined version of the Standard
AVG(<value expression>)
aggregate function for
INTEGER input and output types. This function behaves differently from
the Standard AVG function as it returns 0 when all the input values are
null.
CREATE AGGREGATE FUNCTION udavg(IN x INTEGER, IN flag BOOLEAN, INOUT addup BIGINT, INOUT counter INT) RETURNS INTEGER CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN addup / counter; ELSE SET counter = COALESCE(counter, 0) + 1; SET addup = COALESCE(addup, 0) || COALESCE(x, 0); RETURN NULL; END IF; END
The user defined aggregate function is used in a select statement in the example below. Only the first parameter is visible and utilised in the select statement.
SELECT udavg(id) FROM customers GROUP BY lastname;
In the example below, the function returns an array that contains all the values passed for the aggregated column. For use with longer arrays, you can optimise the function by defining a larger array in the first iteration, and using the TRIM_ARRAY function on the RETURN to cut the array to size. This function is similar to the built-in ARRAY_AGG function
CREATE AGGREGATE FUNCTION array_aggregate(IN val VARCHAR(100), IN flag boolean, INOUT buffer VARCHAR(100) ARRAY, INOUT counter INT) RETURNS VARCHAR(100) ARRAY CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN buffer; ELSE IF val IS NULL THEN RETURN NULL; END IF; IF counter IS NULL THEN SET counter = 0; END IF; SET counter = counter + 1; IF counter = 1 THEN SET buffer = ARRAY[val]; ELSE SET buffer[counter] = val; END IF; RETURN NULL; END IF; END
The tables and data for the select statement below are created with the DatabaseManager or DatabaseManagerSwing GUI apps. (You can find the SQL in the TestSelf.txt file in the zip). Part of the output is shown. Each row of the output includes an array containing the values for the invoices for each customer.
SELECT ID, FIRSTNAME, LASTNAME, ARRAY_AGGREGATE(CAST(INVOICE.TOTAL AS VARCHAR(100))) FROM customer JOIN INVOICE ON ID =CUSTOMERID GROUP BY ID, FIRSTNAME, LASTNAME 11 Susanne Karsen ARRAY['3988.20'] 12 John Peterson ARRAY['2903.10','4382.10','4139.70','3316.50'] 13 Michael Clancy ARRAY['6525.30'] 14 James King ARRAY['3665.40','905.10','498.00'] 18 Sylvia Clancy ARRAY['634.20','4883.10'] 20 Bob Clancy ARRAY['3414.60','744.60']
In the example below, the function returns a string that contains the comma-separated list of all the values passed for the aggregated column. This function is similar to the built in GROUP_CONCAT function.
CREATE AGGREGATE FUNCTION group_concatenate (IN val VARCHAR(100), IN flag BOOLEAN, INOUT buffer VARCHAR(1000), INOUT counter INT) RETURNS VARCHAR(1000) CONTAINS SQL BEGIN ATOMIC IF FLAG THEN RETURN BUFFER; ELSE IF val IS NULL THEN RETURN NULL; END IF; IF buffer IS NULL THEN SET BUFFER = ''; END IF; IF counter IS NULL THEN SET COUNTER = 0; END IF; IF counter > 0 THEN SET buffer = buffer || ','; END IF; SET buffer = buffer + val; SET counter = counter + 1; RETURN NULL; END IF; END
The same tables and data as for the previous example is used. Part of the output is shown. Each row of the output is a comma-separated list of names.
SELECT group_concatenate(firstname || ' ' || lastname) FROM customer GROUP BY lastname Laura Steel,John Steel,John Steel,Robert Steel Robert King,Robert King,James King,George King,Julia King,George King Robert Sommer,Janet Sommer Michael Smith,Anne Smith,Andrew Smith Bill Fuller,Anne Fuller Laura White,Sylvia White Susanne Clancy,Michael Clancy,Sylvia Clancy,Bob Clancy,Susanne Clancy,John Clancy
A Java aggregate function is defined similarly to PSM functions,
apart from the routine body, which is defined as EXTERNAL NAME
...
The Java function signature must follow the rules for both
nullable and INOUT parameters, therefore:
No argument is defined as a primitive or primitive array type. This allows nulls to be passed to the function. The second and third arguments must be defined as arrays of the JDBC non-primitive types listed in the table in the previous section.
In the example below, a user-defined aggregate function for geometric mean is defined.
CREATE AGGREGATE FUNCTION geometric_mean(IN val DOUBLE, IN flag BOOLEAN, INOUT register DOUBLE, INOUT counter INT) RETURNS DOUBLE NO SQL LANGUAGE JAVA EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.geometricMean'
The Java function definition is given below:
public static Double geometricMean(Double in, Boolean flag, Double[] register, Integer[] counter) { if (flag) { if (register[0] == null) { return null; } double a = register[0].doubleValue(); double b = 1 / (double) counter[0]; return Double.valueOf(java.lang.Math.pow(a, b)); } if (in == null) { return null; } if (in.doubleValue() == 0) { return null; } if (register[0] == null) { register[0] = in; counter[0] = Integer.valueOf(1); } else { register[0] = Double.valueOf(register[0].doubleValue() * in.doubleValue()); counter[0] = Integer.valueOf(counter[0].intValue() + 1); } return null; }
In a select statement, the function is used exactly like the built-in aggregate functions:
SELECT geometric_mean(age) FROM FROM customer
$Revision: 3042 $
Copyright 2010-2012 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
Trigger functionality first appeared in SQL:1999. Triggers embody the live database concept, where changes in SQL data can be monitored and acted upon. This means each time a DELETE, UPDATE or INSERT is performed, additional actions are taken by the declared triggers. SQL Standard triggers are imperative while the relational aspects of SQL are declarative. Triggers allow performing an arbitrary transformation of data that is being updated or inserted, or to prevent insert, updated or deletes, or to perform additional operations.
Some bad examples of SQL triggers in effect enforce an “integrity
constraint” which would better be expressed as a CHECK constraint. A
trigger that causes an exception if the value inserted in a column is
negative is such an example. A check constraint that declares
CHECK VALUE >= 0
(declarative) is a better way of
expressing an integrity constraint than a trigger that throws an exception
if the same condition is false.
Usage constraints cannot always be expressed by SQL’s integrity constraint statements. Triggers can enforce these constraints. For example, it is not possible to use a check constraint to prevent inserts or deletes on weekends. A trigger can be used to enforce the time when each operation is allowed.
A trigger is declared to activate when an UPDATE, INSERT or DELETE action is performed on a table. These actions may be direct or indirect. Indirect actions may arise from CASCADE actions of FOREIGN KEY constraints, or from data change statements performed on a VIEW that is based on the table that in.
It is possible to declare multiple triggers on a single table. The triggers activate one by one according to the order in which they were defined. HyperSQL supports an extension to the CREATE TRIGGER statement, which allows the user to specify the execution order of the new trigger.
A row level trigger allows access to the deleted or inserted rows. For UPDATE actions there is both an old and new version of each row. A trigger can be specified to activate before or after the action has been performed.
A trigger that is declared as BEFORE DELETE cannot modify the deleted row. In other words, it cannot decide to delete a different row by changing the column values of the row. A trigger that is declared as BEFORE INSERT and BEFORE UPDATE can modify the values that are inserted into the database. For example, a badly formatted string can be cleaned up by a trigger before INSERT or UPDATE.
BEFORE triggers cannot modify the other tables of the database. All BEFORE triggers can veto the action by throwing an exception.
Because BEFORE triggers can modify the inserted or updated rows, all constraint checks are performed after the execution of the BEFORE triggers. The checks include NOT NULL constraints, length of strings, CHECK constraints, and FOREIGN key constraints.
AFTER triggers can also perform additional data changes, for example inserting an additional row into a different table for data audits. These triggers cannot modify the rows that have been modified by the INSERT or UPDATE action.
A trigger that is declared on a VIEW, is an INSTEAD OF trigger. This term means when an INSERT, UPDATE or DELETE statement is executed with the view as the target, the trigger action is all that is performed, and no further data change takes place on the view. The trigger action can include all the statements that are necessary to change the data in the tables that underlie the view, or even other tables, such as audit tables. With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.
A trigger is declared on a specific table or view. Various trigger properties determine when the trigger is executed and how.
The trigger event specifies the type of SQL statement that causes the trigger to execute. Each trigger is specified to execute when an INSERT, DELETE or UPDATE takes place.
The event can be filtered by two separate means. For all triggers, the WHEN clause can specify a condition against the rows that are the subject of the trigger, together with the data in the database. For example, a trigger can activate when the size of a table becomes larger than a certain amount. Or it can activate when the values in the rows being modified satisfy certain conditions.
An UPDATE trigger can be declared to execute only when certain columns are the subject of an update statement. For example, a trigger declared as AFTER UPDATE OF (datecolumn) will activate only when the UPDATE statement that is executed includes the column, datecolumn, as one of the columns specified in its SET statements.
A statement level trigger is performed once for the executed SQL statement and is declared as FOR EACH STATEMENT.
A row level trigger is performed once for each row that is modified during the execution of an SQL statement and is declared as FOR EACH ROW. Note that an SQL statement can INSERT, UPDATE or DELETE zero or more rows.
If a statement does not apply to any row, then the trigger is not executed.
If FOR EACH ROW or FOR EACH STATEMENT is not specified, then the default is FOR EACH STATEMENT.
The granularity dictates whether the REFERENCING clause can specify OLD ROW, NEW ROW, or OLD TABLE, NEW TABLE.
A trigger declared as FOR EACH STATEMENT can only be an AFTER trigger.
A trigger is executed BEFORE, AFTER or INSTEAD OF the trigger event.
INSTEAD OF triggers are allowed only when the trigger is declared on a VIEW. With this type of trigger, the event (SQL statement) itself is not executed, only the trigger.
BEFORE or AFTER triggers are executed just before or just after the execution of the event. For example, just before a row is inserted into a table, the BEFORE trigger is activated, and just after the row is inserted, the AFTER trigger is executed.
BEFORE triggers can modify the row that is being inserted or updated. AFTER triggers cannot modify rows. They are usually used to perform additional operations, such as inserting rows into other tables.
A trigger declared as FOR EACH STATEMENT can only be an AFTER trigger.
If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names. The REFERENCING clause is used to give names to the old and new rows. The clause, REFERENCING OLD | NEW TABLE is used for statement level triggers. The clause, REFERENCING OLD | NEW ROW is used for row level triggers. If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names. In the SQL statements, the columns of the old or new rows are qualified with the specified names.
The WHEN clause can specify a condition for the columns of the row that is being changed. Using this clause you can simply avoid unnecessary trigger activation for rows that do not need it.
For UPDATE trigger, you can specify a list of columns of the table. If a list of columns is specified, then if the UPDATE statement does not change the columns with SET clauses, then the trigger is not activated at all.
The trigger action specifies what the trigger does when it is activated. This is usually written as one or more SQL statements.
When a row level trigger is activated, there is an OLD ROW, or a NEW ROW, or both. An INSERT statement supplies a NEW ROW row to be inserted into a table. A DELETE statement supplies an OLD ROW be deleted. An UPDATE statement supplies both OLD ROW and NEW ROW that represent the updated rows before and after the update. The REFERENCING clause gives names to these rows, so that the rows can be referenced in the trigger action.
In the example below, a name is given to the NEW ROW and it is used both in the WHEN clause and in the trigger action SQL to insert a row into a triglog table after each row insert into the testtrig table.
CREATE TRIGGER trig AFTER INSERT ON testtrig REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (newrow.id > 1) INSERT INTO TRIGLOG VALUES (newrow.id, newrow.data, 'inserted')
In the example blow, the trigger code modifies the updated data if a condition is true. This type of trigger is useful when the application does not perform the necessary checks and modifications to data. The statement block that starts with BEGIN ATOMIC is similar to an SQL/PSM block and can contain all the SQL statements that are allowed in an SQL/PSM block.
CREATE TRIGGER t BEFORE UPDATE ON customer REFERENCING NEW AS newrow FOR EACH ROW BEGIN ATOMIC IF LENGTH(newrow.firstname) > 10 THEN SET newrow.firstname = LOWER(newrow.firstname); END IF; END
A trigger action can be written as a Java class that implements
the org.hsqldb.Trigger
interface. This interface
has a single method which is called when the trigger is activated,
either before or after the event. When the method is called by the
engine, it supplies the type of trigger as an int value defined by the
interface(as type argument), the name of the trigger (as trigName
argument), the name of the table (as tabName argument), the OLD ROW (as
oldRow argument) and the NEW ROW (as newRow argument). The oldRow
argument is null for row level INSERT triggers. The newRow argument is
null for row level DELETE triggers. For table level triggers, both
arguments are null (that is, there is no access to the data). The
triggerType argument is one of the constants in the org.hsqldb.Trigger
interface which indicate the type of trigger, for example,
INSERT_BEFORE_ROW or UPDATE_AFTER_ROW.
The Java class for the trigger can be reused for several triggers on different tables. The method code can distinguish between the different tables and triggers using the supplied arguments and take appropriate action.
fire (int triggerType, String name, String table, Object row1[], Object row2[])
The Java method for a synchronous trigger (see below) can modify the values in newRow2 in a BEFORE trigger. Such modifications are reflected in the row that is being inserted or updated. Any other modifications are ignored by the engine.
A Java trigger that uses an instance of
org.hsqldb.Trigger
has two forms, synchronous, or
asynchronous (immediate or queued). By default, or when QUEUE 0 is
specified, the action is performed immediately by calling the Java
method. This is similar to SQL trigger actions.
When QUEUE n is specified with n larger than 0, the engine uses a separate thread to execute the Java method, using a queue with the size n. For certain applications, such as real-time systems this allows asynchronous notifications to be sent by the trigger event, without introducing delays in the engine. With asynchronous triggers, an extra parameter, NOWAIT can be used in trigger definition. This overcomes the queue full condition. In this mode, old calls that are still in the queue are discarded one by one and replaced with new calls.
Java row level triggers that are declared with BEFORE trigger
action time can modify the row data. Triggers with AFTER trigger action
time can modify the database, e.g. insert new rows. If the trigger needs
to access the database, the same method as in Java Language Routines
SQL/JRT can be used. The Java code should connect to the URL
"jdbc:default:connection"
and use this connection to
access the database.
For sample trigger classes and test code see,
org.hsqldb.sample.TriggerSample
,
org.hsqldb.test.TestTriggers
,
org.hsqldb.test.TriggerClass
and the associated
text script TestTriggers.txt
in the
/testrun/hsqldb/
directory. In the example below, the
trigger is activated only if the update statement includes SET clauses
that modify any of the specified columns (c1, c2, c3). Furthermore, the
trigger is not activated if the c2 column in the updated row is
null.
CREATE TRIGGER TRIGBUR BEFORE UPDATE OF c1, c2, c3 ON testtrig referencing NEW ROW AS newrow FOR EACH ROW WHEN (newrow.c2 IS NOT NULL) CALL "org.hsqldb.test.TriggerClass"
Java functions can be called from an SQL trigger. So it is possible to define the Java function to perform any external communication that are necessary for the trigger, and use SQL code for checks and alterations to data.
CREATE TRIGGER t BEFORE UPDATE ON customer REFERENCING NEW AS newrow FOR EACH ROW BEGIN ATOMIC IF LENGTH(newrow.firstname) > 10 THEN CALL my_java_function(newrow.firstname, newrow.lastname); END IF; END
CREATE TRIGGER
trigger definition
<trigger definition> ::= CREATE TRIGGER
<trigger name> <trigger action time> <trigger event> ON
<table name> [BEFORE <other trigger name>] [ REFERENCING
<transition table or variable list> ] <triggered
action>
<trigger action time> ::= BEFORE | AFTER | INSTEAD
OF
<trigger event> ::= INSERT | DELETE | UPDATE [ OF
<trigger column list> ]
<trigger column list> ::= <column name
list>
<triggered action> ::= [ FOR EACH { ROW |
STATEMENT } ] [ <triggered when clause> ] <triggered SQL
statement>
<triggered when clause> ::= WHEN <left
paren> <search condition> <right paren>
<triggered SQL statement> ::= <SQL procedure
statement> | BEGIN ATOMIC { <SQL procedure statement>
<semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] CALL
<HSQLDB trigger class FQN>
<transition table or variable list> ::=
<transition table or variable>...
<transition table or variable> ::= OLD [ ROW ] [
AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new
transition variable name> | OLD TABLE [ AS ] <old transition table
name> | NEW TABLE [ AS ] <new transition table
name>
<old transition table name> ::= <transition
table name>
<new transition table name> ::= <transition
table name>
<transition table name> ::=
<identifier>
<old transition variable name> ::= <correlation
name>
<new transition variable name> ::= <correlation
name>
Trigger definition is a relatively complex statement. The
combination of <trigger action time>
and
<trigger event>
determines the type of the
trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF INSERT.
If the optional [ OF <trigger column list> ]
is
specified for an UPDATE trigger, then the trigger is activated only if one
of the columns that is in the <trigger column
list>
is specified in the UPDATE statement that activates the
trigger.
If a trigger is FOR EACH ROW
, which is the
default option, then the trigger is activated for each row of the table
that is affected by the execution of an SQL statement. Otherwise, it is
activated once only per statement execution. For FOR EACH
ROW
triggers, there is an OLD and NEW state for each row. For
UPDATE triggers, both OLD and NEW states exist, representing the row
before the update, and after the update. For DELETE, triggers, there is
only an OLD state. For INSERT triggers, there is only the NEW state. If a
trigger is FOR EACH STATEMENT
, then a transient table
is created containing all the rows for the OLD state and another transient
table is created for the NEW state.
The [ REFERENCING <transition table or variable>
]
is used to give a name to the OLD and NEW data row or table.
This name can be referenced in the <SQL procedure
statement>
to access the data.
The optional <triggered when clause>
is
a search condition, similar to the search condition of a DELETE or UPDATE
statement. If the search condition is not TRUE for a row, then the trigger
is not activated for that row.
The <SQL procedure statement>
is limited
to INSERT, DELETE, UPDATE and MERGE statements.
The <HSQLDB trigger class FQN>
is a
delimited identifier that contains the fully qualified name of a Java
class that implements the org.hsqldb.Trigger
interface.
Early releases of HyperSQL version 2.0 do not allow the use of OLD TABLE or NEW TABLE in statement level triggers.
TRIGGERED SQL STATEMENT
triggered SQL statement
The <triggered SQL statement>
has three
forms.
The first form is a single SQL procedure statement. This statement can reference the OLD ROW and NEW ROW variables. For example, it can reference these variables and insert a row into a separate table.
The second form is enclosed in a BEGIN ... END block and can include one or more SQL procedure statements. In BEFORE triggers, you can include SET statements to modify the inserted or updated rows. In AFTER triggers, you can include INSERT, DELETE and UPDATE statements to change the data in other database tables. SELECT and CALL statements are allowed in BEFORE and AFTER triggers. CALL statements in BEFORE triggers should not modify data.
The third form specifies a call to a Java method.
Two examples of a trigger with a block are given below. The block can include elements discussed in the SQL-Invoked Routines chapter, including local variables, loops and conditionals. You can also raise an exception in such blocks in order to terminate the execution of the SQL statement that caused the trigger to execute.
/* the trigger throws an exception if a customer with the given last name already exists */ CREATE TRIGGER trigone BEFORE INSERT ON customer REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (newrow.id > 100) BEGIN ATOMIC IF EXISTS (SELECT * FROM CUSTOMER WHERE CUSTOMER.LASTNAME = NEW.LASTNAME) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'already exists'; END IF; END
/* for each row inserted into the target, the trigger insert a row into the table used for logging */ CREATE TRIGGER trig AFTER INSERT ON testtrig BEFORE othertrigger REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (newrow.id > 1) BEGIN ATOMIC INSERT INTO triglog VALUES (newrow.id, newrow.data, 'inserted'); /* more statements can be included */ END
TRIGGER EXECUTION ORDER
trigger execution order
<trigger execution order> ::= BEFORE <other
trigger name>
HyperSQL extends the SQL Standard to allow the order of execution of a trigger to be specified by using [BEFORE <other trigger name>] in the definition. The newly defined trigger will be executed before the specified other trigger. If this clause is not used, the new trigger is executed after all the previously defined triggers of the same scope (BEFORE, AFTER, EACH ROW, EACH STATEMENT).
DROP TRIGGER
drop trigger statement
<drop trigger statement> ::= DROP TRIGGER
<trigger name>
Destroy a trigger.
$Revision: 5256 $
Copyright 2010-2012 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
HyperSQL supports a wide range of built-in functions and allows user-defined functions written in SQL and Java languages. User-defined functions are covered in the SQL-Invoked Routines chapter. If a built-in function is not available, you can write your own using procedural SQL or Java.
Built-in aggregate functions such as SUM,
MAX
, ARRAY_AGG
,
GROUP_CONCAT
are covered in the Data Access and Change chapter,
which covers SQL in general. SQL expressions such as
COALESCE
, NULLIF
and
CAST
are also discussed there.
The built-in functions fall into three groups:
SQL Standard Functions
A wide rang of functions defined by SQL/Foundation are supported. SQL/Foundation functions that have no parameter are called without empty parentheses. Functions with multiple parameters often use keywords instead of commas to separate the parameters. Many functions are overloaded. Among these, some have one or more optional parameters that can be omitted, while the return type of some functions is dependent upon the type of one of the parameters. The usage of SQL Standard Functions (where they can be used) is covered more extensively in the Data Access and Change chapter
JDBC Open Group CLI Functions
These functions were defined as an extension to the CLI
standard, which is the basis for ODBC and JDBC and supported by many
database products. JDBC supports an escape mechanism to specify
function calls in SQL statements in a manner that is independent of
the function names supported by the target database engine. For
example SELECT {fn DAYOFMONTH (dateColumn)} FROM
myTable
can be used in JDBC and is translated to Standard
SQL as SELECT EXTRACT (DAY_OF_MONTH FROM dateColumn) FROM
myTable
if a database engine supports the Standard syntax.
If a database engine does not support Standard SQL, then the
translation will be different. HyperSQL supports all the function
names specified in the JDBC specifications as native functions.
Therefore, there is no need to use the {fn FUNC_NAME ( ...
) }
escape with HyperSQL. If a JDBC function is supported
by the SQL Standard in a different form, the SQL Standard form is
the preferred form to use.
HyperSQL Built-In Functions
Many additional built-in functions are available for some useful operations. Some of these functions return the current setting for the session and the database. The General Functions accept arguments of different types and return values based on comparison between the arguments.
In the BNF specification used here, words in capital letters are
actual tokens. Syntactic elements such as expressions are enclosed in
angle brackets. The <left paren>
and
<right paren>
tokens are represented with the
actual symbol. Optional elements are enclosed with square brackets (
<left bracket>
and <right
bracket>
). Multiple options for a required element are
enclosed with braces ( <left brace>
and
<right brace>
).
Alternative
tokens are separated with the vertical bar ( <vertical
bar>
). At the end of each function definition, the standard
which specifies the function is noted in parentheses as JDBC or HyperSQL,
or the SQL/Foundation part of the SQL Standard.
In SQL, there are three kinds of string: character, binary and bit.
The units are respectively characters, octets, and bits. Each kind of
string can be in different data types. CHAR, VARCHAR and CLOB are the
character data types. BINARY, VARBINARY and BLOB are the binary data
types. BIT and BIT VARYING are the bit string types. In all string
functions, the position of a unit of the string within the whole string is
specified from 1 to the length of the whole string. In the BNF,
<char value expr>
indicates any valid SQL
expression that evaluates to a character type. Likewise,
<binary value expr>
indicates a binary type
and <num value expr>
indicates a numeric
type.
ASCII
ASCII ( <char value expr> )
Returns an INTEGER equal to the ASCII code value of the first
character of <char value expr>
. (JDBC)
BIT_LENGTH
BIT_LENGTH ( <string value expression>
)
BIT_LENGTH can be used with character, binary and bit strings. It return a BIGINT value that measures the bit length of the string. (Foundation)
See also CHARACTER_LENGTH and OCTET_LENGTH.
CHAR
CHAR ( <UNICODE code> )
The argument is an INTEGER. Returns a character string containing a
single character that has the specified <UNICODE
code>
, which is an integer. ASCII codes are a subset of the
allowed values for <UNICODE code>
. (JDBC)
CHARACTER_LENGTH
{ CHAR_LENGTH | CHARACTER_LENGTH } ( <char value
expression> [ USING { CHARACTERS | OCTETS } ] )
The CHAR_LENGTH or CHARACTER_LENGTH function can be used with character strings, while OCTET_LENGTH can be used with character or binary strings and BIT_LENGTH can be used with character, binary and bit strings.
All functions return a BIGINT value that measures the length of the
string in the given unit. CHAR_LENGTH counts characters, OCTET_LENGTH
counts octets and BIT_LENGTH counts bits in the string. For CHAR_LENGTH,
if [ USING OCTETS ]
is specified, the octet count is
returned, which is twice the normal length. (Foundation)
CONCAT
CONCAT ( <char value expr 1>, <char value expr
2> [, ...] )
CONCAT ( <binary value expr 1>, <binary value expr
2> [, ...] )
The arguments are character strings or binary strings. Returns a
string formed by concatenation of the arguments. Minimum number of
arguments is 2. Equivalent to the SQL concatenation expression
<value expr 1> || <value expr 2> [ || ...]
.
Handling of null values in the CONCAT function depends on the
database property sql.concat_nulls
( SET
DATABASE SQL SYNTAX CONCAT NULLS { TRUE || FALSE }
). By
default, any null value will cause the function to return null. If the
property is set false, then NULL values are replaced with empty
strings.
(JDBC)
CONCAT_WS
CONCAT_WS ( <char value separator>, <char value
expr 1>, <char value expr 2> [, ...] )
The arguments are character strings. Returns a string formed by
concatenation of the arguments from the second argument, using the
separator from the first argument. Minimum number of arguments is 3.
Equivalent to the SQL concatenation expression <value expr
1> || <separator> || <value expr 2> [ || ...]
.
The function ignores null values and returns an empty string if all values
are null. It returns null only if the separator is null.
This function is similar to a MySQL function of the same name.
(HyperSQL)
DIFFERENCE
DIFFERENCE ( <char value expr 1>, <char value expr
2> )
The arguments are character strings. Converts the arguments into SOUNDEX codes, and returns an INTEGER between 0-4 which indicates how similar the two SOUNDEX value are. If the values are the same, it returns 4, if the values have no similarity, it returns 0. In-between values are returned for partial similarity. (JDBC)
INSERT
INSERT ( <char value expr 1>, <offset>,
<length>, <char value expr 2> )
Returns a character string based on <char value expr
1>
in which <length>
characters have
been removed from the <offset>
position and in
their place, the whole <char value expr 2>
is
copied. Equivalent to SQL/Foundation OVERLAY( <char value
expr1> PLACING < char value expr2> FROM <offset> FOR
<length> )
. (JDBC)
INSTR
INSTR ( <char value expr 1>, <char value expr
2> [ , <offset> ] )
Returns as a BIGINT value the starting position of the first
occurrence of <char value expr 2>
within
<char value expr 1>
. If
<offset
> is specified, the search begins with the
position indicated by <offset>
. If the search is
not successful, 0 is returned. Similar to the LOCATE
function but the order of the arguments is reversed. (HyperSQL)
HEXTORAW
HEXTORAW( <char value expr> )
Returns a BINARY string formed by translation of hexadecimal digits
and letters in the <char value expr>
. Each
character of the <char value expr>
must be a
digit or a letter in the A | B | C | D | E | F set. Each byte of the
retired binary string is formed by translating two hex digits into one
byte. (HyperSQL)
LCASE
LCASE ( <char value expr> )
Returns a character string that is the lower case version of the
<char value expr>
. Equivalent to SQL/Foundation
LOWER (<char value expr>)
. (JDBC)
LEFT
LEFT ( <char value expr>, <count> )
Returns a character string consisting of the first
<count>
characters of <char value
expr>
. Equivalent to SQL/Foundation
SUBSTRING(<char value expr> FROM 0 FOR <count>)
.
(JDBC)
LENGTH
LENGTH ( <char value expr> )
Returns as a BIGINT value the number of characters in
<char value expr>
. Equivalent to SQL/Foundation
CHAR_LENGTH(<char value expr>)
. (JDBC)
LOCATE
LOCATE ( <char value expr 1>, <char value expr
2> [ , <offset> ] )
Returns as a BIGINT value the starting position of the first
occurrence of <char value expr 1>
within
<char value expr 2>
. If
<offset
> is specified, the search begins with the
position indicated by <offset>
. If the search is
not successful, 0 is returned. Without the third argument,
LOCATE
is equivalent to the SQL Standard function
POSITION(<char value expr 1> IN <char value expr
2>)
. (JDBC)
LPAD
LPAD ( <char value expr 1>, <length> [,
<char value expr 2> ] )
Returns a character string with the length of
<length>
characters. The string contains the
characters of <char value expr 1>
padded to the
left with spaces. If <length>
is smaller than the
length of the string argument, the argument is truncated. If the optional
<char value expr 2>
is specified, this string is
used for padding, instead of spaces. (HyperSQL)
LTRIM
LTRIM ( <char value expr> )
Returns a character string based on <char value
expr>
with the leading space characters removed. Equivalent
to SQL/Foundation TRIM( LEADING ' ' FROM <char value expr>
)
. (JDBC)
OCTET_LENGTH
OCTET_LENGTH ( <string value expression>
)
The OCTET_LENGTH function can be used with character or binary strings.
Return a BIGINT value that measures the length of the string in
octets. When used with character strings
, the octet
count is returned, which is twice the normal length. (Foundation)
OVERLAY
OVERLAY ( <char value expr 1> PLACING <char value
expr 2>
FROM <start position> [ FOR <string length> ] [
USING CHARACTERS ] )
OVERLAY ( <binary value expr 1> PLACING <binary
value expr 2>
FROM <start position> [ FOR <string length> ]
)
The character version of OVERLAY returns a character string based on
<char value expr 1>
in which <string
length>
characters have been removed from the
<start position>
and in their place, the whole
<char value expr 2>
is copied.
The binary version of OVERLAY returns a binary string formed in the same manner as the character version. (Foundation)
POSITION
POSITION ( <char value expr 1> IN <char value expr
2> [ USING CHARACTERS ] )
POSITION ( <binary value expr 1> IN <binary value
expr 2> )
The character and binary versions of POSITION search the string value of the second argument for the first occurrence of the first argument string. If the search is successful, the position in the string is returned as a BIGINT. Otherwise zero is returned. (Foundation)
RAWTOHEX
RAWTOHEX( <binary value expr> )
Returns a character string composed of hexadecimal digits
representing the bytes in the <binary value
expr>
. Each byte of the <binary value
expr>
is translated into two hex digits. (HyperSQL)
REGEXP_MATCHES
REGEXP_MATCHES ( <char value expr>, <regular
expression> )
Returns true if the <char value expr>
matches the <regular expression>
as a whole. The
<regular expression>
is defined according to Java
language regular expression rules. (HyperSQL)
REGEXP_SUBSTRING
REGEXP_SUBSTRING ( <char value expr>, <regular
expression> )
Returns the first region in the <char value
expr>
that matches the <regular
expression>
. The <regular
expression>
is defined according to Java language regular
expression rules. (HyperSQL)
REGEXP_SUBSTRING_ARRAY
REGEXP_SUBSTRING_ARRAY ( <char value expr>,
<regular expression> )
Returns all the regions in the the <char value
expr>
that match the <regular
expression>
. The <regular
expression>
is defined according to Java language regular
expression rules. Returns an array containing the matching regions
(HyperSQL)
REPEAT
REPEAT ( <char value expr>, <count> )
Returns a character string based on <char value
expr>
, repeated <count>
times.
(JDBC)
REPLACE
REPLACE ( <char value expr 1>, <char value expr
2> [, <char value expr 3> ] )
Returns a character string based on <char value expr
1>
where each occurrence of <char value expr
2>
has been replaced with a copy of <char value
expr 3>
. If the function is called with just two arguments,
the <char value expr 3> defaults to the empty string and calling the
function simply removes the occurrences of <char value expr
2>
from the first string.(JDBC)
REVERSE
REVERSE ( <char value expr> )
Returns a character string based on <char value
expr>
with characters in the reverse order. (HyperSQL)
RIGHT
RIGHT ( <char value expr>, <count> )
Returns a character string consisting of the last
<count>
characters of <char value
expr>
. (JDBC)
RPAD
RPAD ( <char value expr 1>, <length> [,
<char value expr 2> ] )
Returns a character string with the length of
<length>
characters. The string begins with the
characters of <char value expr 1>
padded to the
right with spaces. If <length>
is smaller than
the length of the string argument, the argument is truncated. If the
optional <char value expr 2>
is specified, this
string is used for padding, instead of spaces. (HyperSQL)
RTRIM
RTRIM ( <char value expr> )
Returns a character string based on <char value
expr>
with the trailing space characters removed. Equivalent
to SQL/Foundation TRIM(TRAILING ' ' FROM <character
string>)
. (JDBC)
SOUNDEX
SOUNDEX ( <char value expr> )
Returns a four character code representing the sound of
<char value expr>
. The US census algorithm is
used. For example the soundex value for Washington is W252. (JDBC)
SPACE
SPACE ( <count> )
Returns a character string consisting of <count>
spaces. (JDBC)
SUBSTR
{ SUBSTR | SUBSTRING } ( <char value expr>,
<offset>, <length> )
The JDBC version of SQL/Foundation SUBSTRING
returns a character string that consists of
<length>
characters from <char value
expr>
starting at the <offset>
position. (JDBC)
SUBSTRING
SUBSTRING ( <char value expr> FROM <start
position> [ FOR <string length> ] [ USING CHARACTERS ]
)
SUBSTRING ( <binary value expr> FROM <start
position> [ FOR <string length> ] )
The character version of SUBSTRING returns a character string that
consists of the characters of the <char value expr>
from <start position>
. If the
optional <string length>
is specified, only
<string length>
characters are returned.
The binary version of SUBSTRING returns a binary string in the same manner. (Foundation)
TRIM
TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim
character> ] FROM ] <char value expr> )
TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim octet>
] FROM ] <binary value expr> )
The character version of TRIM returns a character string based on
<char value expr>
. Consecutive instances of
<trim character>
are removed from the beginning,
the end or both ends of the<char value expr>
depending on the value of the optional first qualifier
[ LEADING | TRAILING | BOTH ]
. If no qualifier is
specified, BOTH
is used as default. If [
<trim character> ]
is not specified, the space character
is used as default.
The binary version of TRIM returns a binary string based on
<binary value expr>
. Consecutive instances of
<trim octet>
are removed in the same manner as in
the character version. If [ <trim octet> ]
is not
specified, the 0 octet is used as default. (Foundation)
TRANSLATE
TRANSLATE( <char value expr1>, <char value
expr2>, <char value expr3> )
Returns a character string based on <char value
expr1>
source. Each character of the source is checked
against the characters in <char value expr2>
. If
the character is not found, it is not modified. If the character is found,
then the character in the same position in <char value
expr3>
is used. If <char value
expr2>
is longer than <char value
expr3>
, then those characters at the end that have no
counterpart in <char value expr3>
are dropped
from the result. (HyperSQL)
-- in this example any accented character in acolumn is replaced with one without an accent TRANSLATE( acolumn, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü', 'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu');
UCASE
UCASE ( <char value expr> )
Returns a character string that is the upper case version of the
<char value expr>
. Equivalent to SQL/Foundation
UPPER( <char value expr> )
. (JDBC)
UPPER
UPPER ( <char value expr> )
Returns a character string that is the upper case version of the
<char value expr>
. (Foundation)
ABS
ABS ( <num value expr> | <interval value expr>
)
Returns the absolute value of the argument as a value of the same type. (JDBC and Foundation)
ACOS
ACOS ( <num value expr> )
Returns the arc-cosine of the argument in radians as a value of DOUBLE type. (JDBC)
ASIN
ASIN ( <num value expr> )
Returns the arc-sine of the argument in radians as a value of DOUBLE type. (JDBC)
ATAN
ATAN ( <num value expr> )
Returns the arc-tangent of the argument in radians as a value of DOUBLE type. (JDBC)
ATAN2
ATAN2 ( <num value expr 1>, <num value expr 2>
)
The <num value expr 1>
and <num
value expr 2>
express the x
and
y
coordinates of a point. Returns the angle, in
radians, representing the angle coordinate of the point in polar
coordinates, as a value of DOUBLE type. (JDBC)
CEILING
{ CEIL | CEILING } ( <num value expr> )
Returns the smallest integer greater than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (JDBC and Foundation)
BITAND
BITAND ( <num value expr 1>, <num value expr 2>
)
BITAND ( <bit value expr 1>, <bit value expr 2>
)
BITANDNOT
BITANDNOT ( <num value expr 1>, <num value expr
2> )
BITANDNOT ( <bit value expr 1>, <bit value expr
2> )
BITNOT
BITNOT ( <num value expr 1> )
BITNOT ( <bit value expr 1> )
BITOR
BITOR ( <num value expr 1>, <num value expr 2>
)
BITOR ( <bit value expr 1>, <bit value expr 2>
)
BITXOR
BITXOR ( <num value expr 1>, <num value expr 2>
)
BITXOR ( <bit value expr 1>, <bit value expr 2>
)
These functions bit operations on two values, or in the case of BITNOT on a single values. The values are either integer values, or bit strings. The result is an integer value of the same type as the arguments, or a bit string of the same length as the argument. Each bit of the result is formed by performing the operation on corresponding bits of the arguments. The names of the function indicate NOT, OR, AND, XOR operations. The BITANDNOT performs NOT on the second argument, then performs AND on result and the first argument. (HyperSQL)
COS
COS ( <num value expr> )
Returns the cosine of the argument (an angle expressed in radians) as a value of DOUBLE type. (JDBC)
COT
COT ( <num value expr> )
Returns the cotangent of the argument as a value of DOUBLE type. The
<num value expr>
represents an angle expressed in
radians. (JDBC)
DEGREES
DEGREES ( <num value expr> )
Converts the argument (an angle expressed in
radians
) into degrees and returns the value in the DOUBLE type.
(JDBC)
EXP
EXP ( <num value expr> )
Returns the exponential value of the argument as a value of DOUBLE type. (JDBC and Foundation)
FLOOR
FLOOR ( <num value expr> )
Returns the largest integer that is less than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (JDBC and Foundation)
LN
LN ( <num value expr> )
Returns the natural logarithm of the argument, as a value of DOUBLE type. (Foundation)
LOG
LOG ( <num value expr> )
Returns the natural logarithm of the argument, as a value of DOUBLE type. (JDBC)
LOG10
LOG10 ( <num value expr> )
Returns the base 10 logarithm of the argument as a value of DOUBLE type. (JDBC)
MOD ( <num value expr 1>, <num value expr 2> )
MOD
Returns the remainder (modulus) of <num value expr
1>
divided by <num value expr 2>.
The data type of the returned value is the same as the second argument.
(JDBC and Foundation)
PI
PI ()
Returns the constant pi as a value of DOUBLE type. (JDBC)
POWER
POWER ( <num value expr 1>, <num value expr 2>
)
Returns the value of <num value expr 1>
raised to the power of <int value expr 2>
as a
value of DOUBLE type. (JDBC and Foundation)
RADIANS
RADIANS ( <num value expr> )
Converts the argument (an angle expressed in
degrees
) into radians and returns the value in the DOUBLE type.
(JDBC)
RAND
RAND ( [ <int value expr> ] )
Returns a random value in the DOUBLE type. The optional [
<int value expr> ]
is used as seed value. In HyperSQL each
session has a separate random number generator. The first call that uses a
seed parameter sets the seed for subsequent calls that do not include a
parameter. (JDBC)
ROUND
ROUND ( <num value expr>, <int value expr> )
The <num value expr>
is of the DOUBLE type
or DECIMAL type. The function returns a DOUBLE or DECIMAL value which is
the value of the argument rounded to <int value
expr>
places right of the decimal point. If <int
value expr>
is negative, the first argument is rounded to
<int value expr>
places to the left of the
decimal point.
This function rounds values ending with .5 or larger away from zero for DECIMAL arguments and results. When the value ends with .5 or larger and the argument and result are DOUBLE, It rounds the value towards the closest even value.
The datetime version is discussed in the next section. (JDBC)
SIGN
SIGN ( <num value expr> )
Returns an INTEGER, indicating the sign of the argument. If the argument is negative then -1 is returned. If it is equal to zero then 0 is returned. If the argument is positive then 1 is returned. (JDBC)
SIN
SIN ( <num value expr> )
Returns the sine of the argument (an angle expressed in radians) as a value of DOUBLE type. (JDBC)
SQRT
SQRT ( <num value expr> )
Returns the square root of the argument as a value of DOUBLE type. (JDBC and Foundation)
TAN
TAN ( <num value expr> )
Returns the tangent of the argument (an angle expressed in radians) as a value of DOUBLE type. (JDBC)
TO_NUMBER
TO_NUMBER ( <char value expr> )
Performs a cast from character to DECIMAL number. The character string must consist of digits and can have a decimal point. Use the SQL Standard CAST expression instead of this non-standard function. (HyperSQL)
TRUNC
TRUNC ( <num value expr> [, <int value expr>] )
This is a similar to the TRUNCATE
function when
the first argument is numeric. If the second argument is omitted, zero is
used in its place.
The datetime version is discussed in the next section. (HyperSQL)
TRUNCATE
TRUNCATE ( <num value expr> [, <int value
expr>] )
Returns a value in the same type as <num value
expr>
but may reduce the scale of DECIMAL and NUMERIC values.
The value is rounded by replacing digits with zeros from <int
value expr>
places right of the decimal point to the end. If
<int value expr>
is negative, ABS(
<int value expr> )
digits to left of the decimal point and
all digits to the right of the decimal points are replaced with zeros.
Results of calling TRUNCATE with 12345.6789 with (-2, 0, 2, 4) are (12300,
12345, 12345.67, 12345.6789). The function does not change the number if
the second argument is larger than or equal to the scale of the first
argument.
If the second argument is not a constant (when it is a parameter or column reference) then the type of the return value is always the same as the type of the first argument. In this case, the discarded digits are replaced with zeros. (JDBC)
WIDTH_BUCKET
WIDTH_BUCKET ( <value expr 1> , <value expr 2>,
<value expr 3>, <int value expr> )
Returns an integer value between 0 and <int value
expr> + 1
. The initial three parameters are of the same
numeric or datetime type. The range, ( <value expr 2> ,
<value expr 3>
) is divided into <int value
expr>
equal sections (buckets). The returned integer value
indicates the index of the bucket where <value expr
1>
can be placed. If the <value expr
1>
falls before or after the range, the return value is 0 or
<value expr 1> + 1
respectively.
This function can be used with numeric or datetime values. Invalid
arguments, including <int value expr>
smaller
than 1, or equal values for <value expr 2>
and
<value expr 3>
will cause an exception.
(Foundation)
An example is given below:
WIDTH_BUCKET( 5, 10, 110, 10) 0 WIDTH_BUCKET( 23, 10, 110, 10) 2 WIDTH_BUCKET( 100, 10, 110, 10) 10 WIDTH_BUCKET( 200, 10, 110, 10) 11
Functions to report the time zone.
TIMEZONE
TIMEZONE()
Returns the current time zone for the session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
SESSION_TIMEZONE
SESSION_TIMEZONE()
Returns the default time zone for the current session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
SESSIONTIMEZONE
SESSIONTIMEZONE()
Same as SESSION_TIMEZONE. (HyperSQL)
DATABASE_TIMEZONE
DATABASE_TIMEZONE()
Returns the time zone for the database engine. This is based on where the database server process is located. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
DBTIMEZONE
DBTIMEZONE()
Same as DATABASE_TIMEZONE. (HyperSQL)
CURRENT_DATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIME [ ( <time precision> )
]
LOCALTIME
LOCALTIME [ ( <time precision> ) ]
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP [ ( <timestamp precision> )
]
LOCALTIMESTAMP
LOCALTIMESTAMP [ ( <timestamp precision> )
]
These datetime functions return the datetime value representing
the moment the function is called. CURRENT_DATE returns a value of DATE
type. CURRENT_TIME returns a value of TIME WITH TIME ZONE type.
LOCALTIME returns a value of TIME type. CURRENT_TIMESTAMP returns a
value of TIMESTAMP WITH TIME ZONE type. LOCALTIMESTAMP returns a value
of TIMESTAMP type. If the optional [ ( <time precision> )
]
or [ ( <timestamp precision> ) ]
is
used, then the returned value has the specified fraction of the second
precision. (Foundation)
NOW
NOW ()
This function is equivalent to LOCALTIMESTAMP
.
It can be used as a no-arg function as the parens are optional.
(HyperSQL)
CURDATE
CURDATE ()
This function is equivalent to CURRENT_DATE
.
(JDBC)
CURTIME
CURTIME ()
This function is equivalent to LOCALTIME
.
(JDBC)
SYSDATE
SYSDATE
This function is equivalent to LOCALTIMESTAMP.
(HyperSQL)
SYSTIMESTAMP
SYSTIMESTAMP
This no-arg function is equivalent to
CURRENT_TIMESTAMP
and is enabled in ORA syntax mode
only. (HyperSQL)
TODAY
TODAY
This no-arg function is equivalent to CURRENT_DATE.
(HyperSQL)
DAYNAME
DAYNAME ( <datetime value expr> )
This function is equivalent to EXTRACT ( DAY_NAME FROM
... )
Returns a string in the range of Sunday - Saturday.
(JDBC)
DAYOFMONTH
DAYOFMONTH ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_MONTH
FROM ... )
Returns an integer value in the range of 1-31.
(JDBC)
DAYOFWEEK
DAYOFWEEK ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_WEEK FROM
... )
Returns an integer value in the range of 1-7. The first
day of the week is Sunday. (JDBC)
DAYOFYEAR
DAYOFYEAR ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_YEAR FROM
... )
Returns an integer value in the range of 1-366.
(JDBC)
DAYS
DAYS ( <datetime value expr> )
The <datetime value expr>
is of DATE or
TIMESTAMP type. This function returns the DAY number since the first day
of the calendar. The first day is numbered 1. (HyperSQL)
HOUR
HOUR ( <datetime value expr> )
This function is equivalent to EXTRACT ( HOUR FROM ... )
Returns an integer value in the range of 0-23. (JDBC)
MINUTE
MINUTE ( <datetime value expr> )
This function is equivalent to EXTRACT ( MINUTE FROM ...
)
Returns an integer value in the range of 0 - 59.
(JDBC)
MONTH
MONTH ( <datetime value expr> )
This function is equivalent to EXTRACT ( MONTH FROM ... )
Returns an integer value in the range of 1-12. (JDBC)
MONTHNAME
MONTHNAME ( <datetime value expr>
)
This function is equivalent to EXTRACT ( NAME_OF_MONTH
FROM ... )
Returns a string in the range of January -
December. (JDBC)
QUARTER
QUARTER ( <datetime value expr> )
This function is equivalent to EXTRACT ( QUARTER FROM ...
)
Returns an integer in the range of 1 - 4. (JDBC)
SECOND
SECOND ( <datetime value expr> )
This function is equivalent to EXTRACT ( SECOND FROM ...
)
Returns an integer or decimal in the range of 0 - 59, with
the same precision as the <datetime value expr>. (JDBC)
SECONDS_SINCE_MIDNIGHT
SECONDS_SINCE_MIDNIGHT ( <datetime value expr>
)
This function is equivalent to EXTRACT (
SECONDS_SINCE_MIDNIGHT FROM ... )
Returns an integer in the
range of 0 - 86399. (HyperSQL)
UNIX_TIMESTAMP
UNIX_TIMESTAMP ( [ <datetime value expression> ] )
This function returns a BIGINT value. With no parameter, it returns the number of seconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of seconds since 1970-01-01. The TIMESTAMP ( <num value expression> function returns a TIMESTAMP from a Unix timestamp. (HyperSQL)
WEEK
WEEK ( <datetime value expr> )
This function is equivalent to EXTRACT ( WEEK_OF_YEAR
FROM ... )
Returns an integer in the range of 1 - 54.
(JDBC)
YEAR
YEAR ( <datetime value expr> )
This function is equivalent to EXTRACT ( YEAR FROM ... )
Returns an integer in the range of 1 - 9999. (JDBC)
EXTRACT
EXTRACT ( <extract field> FROM <extract
source> )
<extract field> ::= YEAR | MONTH | DAY | HOUR |
MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR |
DAY_OF_MONTH |
TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND |
SECONDS_SINCE_MIDNIGHT |
DAY_NAME | MONTH_NAME
<extract source> ::= <datetime value expr> |
<interval value expr>
The EXTRACT function returns a field or element of the
<extract source>
. The <extract
source>
is a datetime or interval expression. The type of
the return value is BIGINT for most of the <extract
field>
options. The exceptions is SECOND
where a DECIMAL value is returned which has the same precision
as the datetime or interval expression. The field values
DAY_NAME
or MONTH_NAME
result in a
character string. When MONTH_NAME
is specified, a
string in the range January - December is returned. When
DAY_NAME
is specified, a string in the range Sunday
-Saturday is returned.
If the <extract source>
is FROM
<datetime value expr>
, different groups of
<extract source>
can be used depending on the
data type of the expression. The TIMEZONE_HOUR |
TIMEZONE_MINUTE
options are valid only for TIME WITH TIMEZONE
and TIMESTAMP WITH TIMEZONE data types. The HOUR | MINUTE |
SECOND | SECONDS_MIDNIGHT
options, are valid for TIME and
TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP
types.
If the <extract source>
is FROM
<interval value expr>
, the <extract
field>
must be one of the fields of the INTERVAL type of
the expressions. The YEAR | MONTH
options may be
valid for INTERVAL types based on months. The DAY | HOUR |
MINUTE | SECOND | SECONDS_MIDNIGHT
options may be valid for
INTERVAL types based on seconds. For example, DAY | HOUR |
MINUTE
are the only valid fields for the INTERVAL DAY TO
MINUTE data type. (Foundation with HyperSQL extensions)
ADD_MONTHS
ADD_MONTHS ( <datetime value expr>, <numeric
value expr>)
This function is similar but different to simple addition a MONTH
interval to a datetime value. The SQL Standard expression,
<datetime value expr> + n MONTH
, when used with
the last day of a short month such as February, returns a date that has
the same day of the month in the target month. The
ADD_MONTHS
function adjusts the target day to the
last day of the target month. For all other days, the behaviour is the
same. This function always returns a TIMESTAMP(0) value, regardless of
the type of the argument. (HyperSQL)
The example below compares the output of the function and the expression.
VALUES ADD_MONTHS ( DATE '2012-02-29' , 1), DATE '2012-02-29' + 1 MONTH C1 C2 ------------------- ---------- 2012-03-31 00:00:00 2012-03-29
LAST_DAY
LAST_DAY ( <datetime value expr> )
Returns the last day of the month for the given argument, preserving the year, month, hour, minute and secend fields of the date. The type of the result is always TIMESTAMP(0). (HyperSQL)
VALUES LAST_DAY ( TIMESTAMP '2012-02-14 12:30:44') C1 ------------------- 2012-02-29 12:30:44
MONTHS_BETWEEN
MONTHS_BETWEEN ( <datetime value expr1> ,
<datetime value expr2> )
Returns the a number (not an INTERVAL) possibly with a fraction, representing the number of months between two days. If both dates have the same day of month, or are on the last day of the month, the result is an exact numeric. Otherwise, the fraction is calculated base on 31 days per month. You can cast the resulting value into INTERVAL MONTH and use it for datetime arithmetic. (HyperSQL)
VALUES MONTHS_BETWEEN ( TIMESTAMP '2013-02-14 12:30:44', TIMESTAMP '2012-01-04 12:30:44') C1 ----------------------------------- 13.32258064516129000000000000000000
TIMESTAMPADD
TIMESTAMPADD ( <tsi datetime field>, <numeric
value expression>, <datetime value expr>)
TIMESTAMPDIFF
TIMESTAMPDIFF ( <tsi datetime field>, <datetime
value expr 1>, <datetime value expr 2>)
<tsi datetime field> ::= SQL_TSI_FRAC_SECOND |
SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY |
SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER |
SQL_TSI_YEAR
HyperSQL supports full SQL Standard datetime features. It supports
adding integers representing units of time directly to datetime values
using the arithmetic plus operator. It also supports subtracting one
<datetime value expr>
from another in the given
units of date or time using the minus operator. An example of
<datetime value expr> + <numeric value expression>
<datetime field>
is LOCALTIMESTAMP + 5
DAY
. An example of ( <datetime value expr> -
<numeric value expression> ) <datetime field>
is
(CURRENT_DATE - DATE '2008-08-8') MONTH
which returns
the number of calendar months between the two dates.
The two JDBC functions, TIMESTAMPADD
and
TIMESTAMPDIFF
perform the same function as above SQL
expressions. The field names are keywords and are different from those
used in the EXTRACT functions. These names are valid for use only when
calling these two functions. The return value for TIMESTAMPADD is of the
same type as the datetime argument used. The return type for
TIMESTAMPDIFF is always BIGINT, regardless of the type of arguments. The
two datetime arguments of TIMESTAMPDIFF should be of the same
type.
TIMESTAMPDIFF
is evaluated as <datetime
value expr 2> - <datetime value expr 1>. (JDBC)
TIMESTAMPADD ( SQL_TSI_MONTH, 3, DATE '2008-11-22' ) TIMESTAMPDIFF ( SQL_TSI_HOUR, TIMESTAMP '2008-11-20 20:30:40', TIMESTAMP '2008-11-21 21:30:40' )
DATE_ADD
DATE_ADD ( <datetime value expr> , <interval
value expr> )
DATE_SUB
DATE_SUB ( <datetime value expr> , <interval
value expr> )
These functions are equivalent to arithmetic addition and subtraction, <datetime value expr> + <interval value expr> and <datetime value expr> - <interval value expr>. The functions are provided for compatibility with other databases. The supported interval units are the standard SQL interval unit listed in other chapters of this guide. (HyperSQL)
DATE_ADD ( DATE '2008-11-22', INTERVAL 3 MONTH ) DATE_SUB ( TIMESTAMP '2008-11-22 20:30:40', INTERVAL 20 HOUR )
DATEADD
DATEADD ( <field>, <numeric value expr>,
<datetime value expr> )
DATEDIFF
DATEDIFF ( <field>, <datetime value expr 1>,
<datetime value expr 2> )
<field> ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' |
'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' |
'millisecond'
The DATEADD and DATEDIFF functions are alternatives to
TIMESTAMPADD and TIMESTAMPDIFF, with fewer available field options. The
field names are specified as strings, rather than keywords. The fields
translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and MILLISECOND.
DATEDIFF
is evaluated as <datetime value expr
2> - <datetime value expr 1>. (HyperSQL}
DATEDIFF ( <datetime value expr 1>, <datetime
value expr 2> )
This special form of DATEDIFF
does not have a
field parameter and return the number of days between two dates. This
form is evaluated as <datetime value expr 1> -
<datetime value expr 2>
, which is different from the
main form. This form is compatible with some other database engines.
(HyperSQL}
DATEADD ( 'month', 3, DATE '2008-11-22' ) DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' )
ROUND
ROUND ( <datetime value expr> [ , <char value
expr> ] )
The <datetime value expr>
is of DATE,
TIME or TIMESTAMP type. The <char value expr>
is a format string for YEAR, MONTH, WEEK OF YEAR, DAY, HOUR, MINUTE or
SECOND as listed in the table for TO_CHAR and TO_DATE format elements
(see below). The datetime value is rounded up or down after the
specified field and the rest of the fields to the right are set to one
for MONTH and DAY, or zero, for the rest of the fields. For example
rounding a timestamp value on the DAY field results in midnight the same
date or midnight the next day if the time is at or after 12 noon. If the
second argument is omitted, the datetime value is rounded to the nearest
day. (HyperSQL)
TRUNC
TRUNC ( <datetime value expr> [ , <char value
expr> ] )
Similar to the ROUND function, the <num value expr>
is of DATE, TIME or TIMESTAMP type. The <char
value expr>
is a format string (such as 'YY' or 'MM') for
YEAR, MONTH, WEEK OF YEAR, DAY, HOUR, MINUTE or SECOND as listed in the
table for TO_CHAR and TO_DATE format elements (see below). The datetime
value is truncated after the specified field and the rest of the fields
to the right are set to one for MONTH and DAY, or zero, for the rest of
the fields. For example applying TRUNC to a timestamp value on the DAY
field results in midnight the same date. Examples of ROUND and TRUNC
functions are given below. If the second argument is omitted, the
datetime value is truncated to midnight the same date. (HyperSQL)
ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' ) TIMESTAMP '2009-01-01 00:00:00' TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' ) TIMESTAMP '2008-01-01 00:00:00'
TIMESTAMP
TIMESTAMP ( <num value expr> )
TIMESTAMP ( <char value expr> )
TIMESTAMP ( <char value expr>, <char value
expr> )
TIMESTAMP ( <date value expr>, <time value
expr> )
This function translates the arguments into a TIMESTAMP WIHOUT TIME ZONE value.
When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.
When the single argument is a formatted date or timestamp string, it is translated to a TIMESTAMP.
When two arguments are used, the first argument is the date part and the second argument is the time part of the returned TIMESTAMP value. An example, including the result, is given below:
TIMESTAMP ( '2008-11-22', '20:30:40' ) TIMESTAMP '2008-11-22 20:30:40.000000'
TIMESTAMP_WITH_ZONE
TIMESTAMP_WITH_ZONE ( <num value expr> )
TIMESTAMP_WITH_ZONE ( <char value expr> )
This function translates the arguments into a TIMESTAMP WITH TIME ZONE value.
When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.
When the single argument is TIMESTAMP, it is converted to TIMESTAMP WITH TIME ZONE.
The time zone of the returned value is the local time zone at the time of the timestamp argument. This accounts for daylight saving times. For example, if the local time zone was +4:00 at the time of the given Unix timestamp, the returned value is local timestamp at the time with time zone +4:00.
TO_CHAR
TO_CHAR ( <datetime value expr>, <char value
expr> )
This function formats a datetime or numeric value to the format given in the second argument. The format string can contain pattern elements from the list given below, plus punctuation and space characters. An example, including the result, is given below:
TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC MONTH, DAY HH' ) 2008 AD February, Friday 8 TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', '"The Date is" YYYY BC MONTH, DAY HH' ) The Date is 2008 AD February, Friday 8
The format is internally translated to a
java.text.SimpleDateFormat
format string.
Separator characters (space, comma, period, hyphen, colon, semicolon,
forward slash) can be included between the pattern elements. Unsupported
format strings should not be used. You can include a string literal
inside the format string by enclosing it in double quotes (see the
second example above). (HyperSQL)
TO_DATE
TO_DATE ( <char value expr>, <char value
expr> )
This function translates a formatted datetime sting to a TIMESTAMP(0) according to the format given in the second argument. See TO_TIMESTAMP below for further details.
TO_TIMESTAMP
TO_TIMESTAMP ( <char value expr>, <char value
expr> )
This function translates a formatted datetime sting to a TIMESTAMP(6) according to the format given in the second argument. The format string can contain pattern elements from the list given below, plus punctuation and space characters. The pattern should contain all the necessary fields to construct a date, including, year, month, day of month, etc. The returned timestamp can then be cast into DATE or TIME types if necessary. An example, including the result, is given below:
TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' ) TIMESTAMP '2008-11-22 20:30:40.000000'
The format strings that can be used for TO_DATE and TO_TIMESTAMP are more restrictive than those used for TO_CHAR, because the format string must contain the elements needed to build a full DATE or TIMESTAMP value. For example, you cannot use the 'WW', 'W', 'HH' or 'HH12' format elements with TO_DATE or TO_TIMESTAMP
The format is internally translated to a
java.text.SimpleDateFormat
format string.
Unsupported format strings should not be used. With TO_CHAR, you can
include a string literal inside the format string by enclosing it in
double quotes. (HyperSQL)
The supported format components are all uppercase as follows:
Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
BC | B.C. | AD | A.D. | Returns AD for common era and
BC for before common era |
RRRR |
4-digit year |
YYYY |
4-digit year |
IYYY |
4-digit year, corresponding to ISO week of the year. The reported year for the last few days of the calendar year may be the next year. |
YY |
2 digit year |
IY |
2 digit year, corresponding to ISO week of the year |
MM |
Month (01-12) |
MON |
Short three-letter name of month |
MONTH |
Name of month |
WW |
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year (not a calendar week). |
W |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh (not a calendar week). |
IW |
Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday. The first week may start near the end of previous year. |
DAY |
Name of day. |
DD |
Day of month (01-31). |
DDD |
Day of year (1-366). |
DY |
Short three-letter name of day. |
HH |
Hour of day (00-11). |
HH12 |
Hour of day (00-11). |
HH24 |
Hour of day (00-23). |
MI |
Minute (00-59). |
SS |
Second (00-59). |
FF |
Fractional seconds. |
Array functions are specialised functions with ARRAY parameters or return values. For the ARRAY_AGG aggregate function, see the Data Access and Change chapter.
CARDINALITY
CARDINALITY( <array value expr> )
Returns the element count for the given array argument. (Foundation)
MAX_CARDINALITY
MAX_CARDINALITY( <array value expr>
)
Returns the maximum allowed element count for the given array argument. (Foundation)
POSITION_ARRAY
POSITION_ARRAY( <value expression> IN <array value
expr> [ FROM <int value expr> ] )
Returns the position of the first match for the <value
expression>
in the array. By default the search starts from
the beginning of the array. The optional <int value
expr>
specifies the start position. Positions are counted
from 1. Returns zero if no match is found. (HyperSQL)
SORT_ARRAY
SORT_ARRAY( <array value expr> [ { ASC | DESC } ] [
NULLS { FIRST | LAST } ] )
Returns a sorted copy of the array. By default, sort is performed in ascending order and NULL elements are sorted first. (HyperSQL)
TRIM_ARRAY
TRIM_ARRAY( <array value expr>, <num value
expr> )
Returns a new array that contains the elements of the
<array value expr>
minus the number of elements
specified by the <num value expr>.
Elements are
discarded from the end of the array. (Foundation)
SEQUENCE_ARRAY
SEQUENCE_ARRAY( <value expr 1>, <value expr 2>,
<value expr 3 )
Returns a new array that contains a sequence of values. The
<value expr 1>
is the lower bound of the range.
The <value expr 2>
is the upper bound of the
range. The <value expr 3>
is the increment. The
elments of the array are within the inclusive range. The first element is
<value expr 1>
and each subsequent element is the
sum of the previous element and the increment. If the increment is zero,
only the first element is returned. When the increment is negative, the
lower bound should be larger than the upper bound. The type of the
arguments can be all number types, or a datetime range and an interval for
the third argument (HyperSQL)
In the examples below, a number sequence and a date sequence are shown. The UNNEST table expression is used to form a table from the array.
SEQUENCE_ARRAY(0, 100, 5) ARRAY[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100] SELECT * FROM UNNEST(SEQUENCE_ARRAY(10, 12, 1)) C1 -- 10 11 12 SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I) D I ---------- - 2010-08-01 1 2010-08-02 2 2010-08-03 3 2010-08-04 4 2010-08-05 5 2010-08-06 6 2010-08-07 7
General functions can take different types of arguments. Some General Functions accept a variable number of arguments.
Also see the Data Access and Change chapter for SQL expressions that are similar to functions, for example CAST and NULLIF.
CASEWHEN
CASEWHEN( <boolean value expr>, <value expr 2>,
<value expr 3> )
If the <boolean value expr>
is true,
returns <value expr 2>
otherwise returns
<value expr 3>. Use a CASE WHEN expression instead for more
extensive capabilities and options.
CASE WHEN is documented in
the Data Access and Change chapter. (HyperSQL)
COALESCE
COALESCE( <value expr 1>, <value expr 2> [,
...] )
Returns <value expr 1>
if it is not null,
otherwise returns <value expr 2>
if not null and
so on. The type of both arguments must be comparable. (Foundation)
CONVERT
CONVERT ( <value expr> , <data type>
)
<data type> ::= { SQL_BIGINT | SQL_BINARY | SQL_BIT
|SQL_BLOB | SQL_BOOLEAN | SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL |
SQL_DATALINK |SQL_DOUBLE | SQL_FLOAT | SQL_INTEGER | SQL_LONGVARBINARY |
SQL_LONGNVARCHAR | SQL_LONGVARCHAR | SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC |
SQL_NVARCHAR | SQL_REAL | SQL_ROWID | SQL_SQLXML | SQL_SMALLINT | SQL_TIME
| SQL_TIMESTAMP | SQL_TINYINT | SQL_VARBINARY | SQL_VARCHAR} [ (
<precision, length or scale parameters> ) ]
The CONVERT function is a JDBC escape function, equivalent to the
SQL standard CAST expression. It converts the <value
expr>
into the given <data type>
and
returns the value. The <data type>
options are
synthetic names made by prefixing type names with SQL_
.
Some of the <data type>
options represent valid
SQL types, but some are based on non-standard type names, namely
{ SQL_LONGNVARCHAR | SQL_LONGVARBINARY |SQL_LONGVARCHAR |
SQL_TINYINT }
. None of the synthetic names can be used in any
other context than the CONVERT function.
The definition of CONVERT in the JDBC Standard does not allow the precision, scale or length to be specified. This is required by the SQL standard for BINARY, BIT, BLOB, CHAR, CLOB, VARBINARY and VARCHAR types and is often needed for DECIMAL and NUMERIC. Defaults are used for precision.
HyperSQL also allows the use of real type names (without the
SQL_
prefix). In this usage, HyperSQL allows the use of
precision, scale or length for the type definition when they are valid for
the type definition.
When MS SQL Server compatibility mode is on, the parameters of CONVERT are switched and only the real type names with required precision, scale or length are allowed. (JDBC)
DECODE
DECODE( <value expr main>, <value expr match
1>, <value expr result 1> [...,] [, <value expr default>]
)
DECODE takes at least 3 arguments. The <value expr
main>
is compared with <value expr match
1>
and if it matches, <value expr result
1>
is returned. If there are additional pairs of
<value expr match n>
and <value expr
result n>
, comparison is repeated until a match is found the
result is returned. If no match is found, the <value expr
default>
is returned if it is specified, otherwise NULL is
returned. The type of the return value is a combination of the types of
the <value expr result ... >
arguments.
(HyperSQL)
GREATEST
GREATEST( <value expr 1>, [<value expr ...>,
...] )
The GREATEST function takes one or more arguments. It compares the arguments with each other and returns the greatest argument. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are comparable. (HyperSQL)
IFNULL
ISNULL
IFNULL | ISNULL ( <value expr 1>, <value expr
2> )
Returns <value expr 1>
if it is not null,
otherwise returns <value expr 2>
. The type of the
return value is the type of <value expr 1>
.
Almost equivalent to SQL Standard COALESCE(<value expr 1>,
<value expr 2>)
function, but without type modification.
(JDBC)
LEAST
LEAST( <value expr 1>, [<value expr ...>, ...]
)
The LEAST function takes one or more arguments. It compares the arguments with each other and returns the smallest argument. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are comparable. (HyperSQL)
LOAD_FILE
LOAD_FILE ( <char value expr 1> [, <char value
expr 2>] )
Returns a BLOB or CLOB containing the URL or file path specified in the first argument. If used with a single argument, the function returns a BLOB. If used with two arguments, the function returns a CLOB and the second argument is the character encoding of the file.
The file path is interpreted the same way as a TEXT TABLE source
file location. The hsqldb.allow_full_path
system
property must be set true
in order to access files
outside the directory structure of the database files.
(HyperSQL)
NULLIF
NULLIF( <value expr 1>, <value expr 2>
)
Returns <value expr 1>
if it is not equal
to <value expr 2>
, otherwise returns null. The
type of both arguments must be the same. This function is a shorthand for
a specific CASE expression. (Foundation)
NVL
NVL( <value expr 1>, <value expr 2>
)
Returns <value expr 1>
if it is not null,
otherwise returns <value expr 2>
. The type of the
return value is the type of <value expr 1>
. For
example, if <value expr 1>
is an INTEGER column
and <value expr 2>
is a DOUBLE constant, the
return type is cast into INTEGER. This function is similar to IFNULL.
(HyperSQL)
NVL2