Difference between revisions of "Built-in functions and Stored Procedures"
DrewJensen (Talk | contribs) |
DrewJensen (Talk | contribs) |
||
Line 1: | Line 1: | ||
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries. | The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries. | ||
− | The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL | + | The Query component in Base allows for SQL commands that return a result set. |
− | + | i.e. SELECT and CALL | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | The supports two views: | |
− | + | *GUI Query Designer (Designer View) | |
− | + | *Text editor (SQL View) | |
− | + | ||
+ | Queries created and executed in Designer and SQL View support the use of named replaceable parameters in these statements, in most cases. Use of this feature is controlled by the option 'Escape Processing'. | ||
+ | |||
+ | Escape Processing is always ENABLED when using Designer View. | ||
+ | |||
+ | Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button. | ||
+ | |||
+ | The SQL window in Base (opened with the menu commands TOOLS > SQL) allows any valid SQL statement to be executed, but it does not return any data results to the user. | ||
+ | Statements executed in this window serve two main purposes. | ||
+ | *Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT commands. | ||
+ | *Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc. | ||
+ | Multiple statements may be executed as a batch by ending each statement with a semi-colon. | ||
− | |||
− | |||
All functions listed below are available for use in the SQL window. | All functions listed below are available for use in the SQL window. | ||
+ | |||
+ | Each table below is formatted as follows: | ||
+ | *Function Name - this is the actual function, along with required or optional parameters | ||
+ | *Designer - Yes in this column means it may be used in Designer View. No, the function must be used in SQL View. | ||
+ | *Esc Proc - Yes in this column means it may be used in SQL View, with escape processing ENABLED. No, the function is only available in SQL View with escape processing DISABLED. | ||
+ | *Comments - A brief description of what the function does, the parameters expected and the type of output generated. | ||
+ | |||
Line 21: | Line 33: | ||
|+ '''Numerical Functions''' | |+ '''Numerical Functions''' | ||
|- | |- | ||
− | | Function || Desinger || | + | | Function Name || Desinger || Esc Proc || Comments |
|- | |- | ||
| ABS(d) | | ABS(d) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
| ACOS(d) | | ACOS(d) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
| ASIN(d) | | ASIN(d) | ||
− | |YES ||YES || | + | |YES ||YES || |
|- | |- | ||
| ATAN(d) | | ATAN(d) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
| ATAN2(a,b) | | ATAN2(a,b) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
| BITAND(a,b) | | BITAND(a,b) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
| BITOR(a,b) | | BITOR(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
| CEILING(d) | | CEILING(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
| COS(d) | | COS(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|COT(d) | |COT(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|DEGREES(d) | |DEGREES(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|EXP(d) | |EXP(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|FLOOR(d) | |FLOOR(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|LOG(d) | |LOG(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|LOG10(d) | |LOG10(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|MOD(a,b) | |MOD(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|PI() | |PI() | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|POWER(a,b) | |POWER(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|RADIANS(d) | |RADIANS(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|RAND() | |RAND() | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|ROUND(a,b) | |ROUND(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|ROUNDMAGIC(d) | |ROUNDMAGIC(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|SIGN( d ) | |SIGN( d ) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|SIN(d) | |SIN(d) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|SQRT( d ) | |SQRT( d ) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|TAN( A ) | |TAN( A ) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|TRUNCATE(a,b) | |TRUNCATE(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || |
|- | |- | ||
|} | |} | ||
Line 110: | Line 122: | ||
|+ '''Statistical Functions''' | |+ '''Statistical Functions''' | ||
|- | |- | ||
− | | Function || Desinger || | + | | Function Name || Desinger || Esc Proc || Comments |
|- | |- | ||
|VAR_POP | |VAR_POP | ||
+ | | || || | ||
|- | |- | ||
|VAR_SAMP | |VAR_SAMP | ||
+ | | || || | ||
|- | |- | ||
|STDDEV_POP | |STDDEV_POP | ||
+ | | || || | ||
|- | |- | ||
|STDDEV_SAMP | |STDDEV_SAMP | ||
+ | | || || | ||
|- | |- | ||
|} | |} | ||
Line 126: | Line 142: | ||
|+ '''String Fnctions''' | |+ '''String Fnctions''' | ||
- | - | ||
− | | Function || Desinger || | + | | Function Name || Desinger || Esc Proc || Comments |
|- | |- | ||
|ASCII(s) | |ASCII(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|BIT_LENGTH(str) | |BIT_LENGTH(str) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CHAR(c) | |CHAR(c) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CHAR_LENGTH(str) | |CHAR_LENGTH(str) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CONCAT(str1,str2) | |CONCAT(str1,str2) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DIFFERENCE(s1,s2) | |DIFFERENCE(s1,s2) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|HEXTORAW(s1) | |HEXTORAW(s1) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|INSERT(s,start,len,s2) | |INSERT(s,start,len,s2) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LCASE(s) | |LCASE(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LEFT(s,count) | |LEFT(s,count) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LENGTH(s) | |LENGTH(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LOCATE(search,s,[start]) | |LOCATE(search,s,[start]) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LTRIM(s) | |LTRIM(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|OCTET_LENGTH(str) | |OCTET_LENGTH(str) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|RAWTOHEX(s1) | |RAWTOHEX(s1) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|POSITION (... IN ..) | |POSITION (... IN ..) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|REPEAT(s,count) | |REPEAT(s,count) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|REPLACE(s,replace,s2) | |REPLACE(s,replace,s2) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|RIGHT(s,count) | |RIGHT(s,count) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|RTRIM(s) | |RTRIM(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SOUNDEX(s) | |SOUNDEX(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SPACE(count) | |SPACE(count) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SUBSTR(s,start[,len]) | |SUBSTR(s,start[,len]) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SUBSTRING(s,start[,len]) | |SUBSTRING(s,start[,len]) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SUBSTRING(... FROM ... FOR ...) | |SUBSTRING(... FROM ... FOR ...) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|TRIM( LEADING TRAILING BOTH FROM <COLUMN> ) | |TRIM( LEADING TRAILING BOTH FROM <COLUMN> ) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|UCASE(s) | |UCASE(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|LOWER(s) | |LOWER(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|UPPER(s) | |UPPER(s) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|} | |} | ||
Line 221: | Line 237: | ||
|+ '''Date and Time Functions''' | |+ '''Date and Time Functions''' | ||
|- | |- | ||
− | | Function || Desinger || | + | | Function Name || Desinger || Esc Proc || Comments |
|- | |- | ||
|CURDATE() | |CURDATE() | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CURTIME() | |CURTIME() | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DATEDIFF(string, datetime1, datetime2) | |DATEDIFF(string, datetime1, datetime2) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DAYNAME(date) | |DAYNAME(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DAYOFMONTH(date) | |DAYOFMONTH(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DAYOFWEEK(date) | |DAYOFWEEK(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|DAYOFYEAR(date) | |DAYOFYEAR(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|HOUR(time) | |HOUR(time) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|MINUTE(time) | |MINUTE(time) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|MONTH(date) | |MONTH(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|MONTHNAME(date) | |MONTHNAME(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|NOW() | |NOW() | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|QUARTER(date) | |QUARTER(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|SECOND(time) | |SECOND(time) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|WEEK(date) | |WEEK(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|YEAR(date) | |YEAR(date) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CURRENT_DATE | |CURRENT_DATE | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CURRENT_TIME | |CURRENT_TIME | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|CURRENT_TIMESTAMP | |CURRENT_TIMESTAMP | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|TO_CHAR( <date column>, format String ) | |TO_CHAR( <date column>, format String ) | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|} | |} | ||
Line 289: | Line 305: | ||
|+ '''System Functions''' | |+ '''System Functions''' | ||
|- | |- | ||
− | | Function || Desinger || | + | | Function Name || Desinger || Esc Proc || Comments |
|- | |- | ||
|DATABASE() | |DATABASE() | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|USER() | |USER() | ||
− | | NO || NO || | + | | NO || NO || |
|- | |- | ||
|CURRENT_USER | |CURRENT_USER | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|IDENTITY() | |IDENTITY() | ||
− | | YES || YES || | + | | YES || YES || |
|- | |- | ||
|} | |} |
Revision as of 01:07, 26 August 2007
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries.
The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL
The supports two views:
- GUI Query Designer (Designer View)
- Text editor (SQL View)
Queries created and executed in Designer and SQL View support the use of named replaceable parameters in these statements, in most cases. Use of this feature is controlled by the option 'Escape Processing'.
Escape Processing is always ENABLED when using Designer View.
Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button.
The SQL window in Base (opened with the menu commands TOOLS > SQL) allows any valid SQL statement to be executed, but it does not return any data results to the user. Statements executed in this window serve two main purposes.
- Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT commands.
- Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc.
Multiple statements may be executed as a batch by ending each statement with a semi-colon.
All functions listed below are available for use in the SQL window.
Each table below is formatted as follows:
- Function Name - this is the actual function, along with required or optional parameters
- Designer - Yes in this column means it may be used in Designer View. No, the function must be used in SQL View.
- Esc Proc - Yes in this column means it may be used in SQL View, with escape processing ENABLED. No, the function is only available in SQL View with escape processing DISABLED.
- Comments - A brief description of what the function does, the parameters expected and the type of output generated.
Function Name | Desinger | Esc Proc | Comments |
ABS(d) | YES | YES | |
ACOS(d) | YES | YES | |
ASIN(d) | YES | YES | |
ATAN(d) | YES | YES | |
ATAN2(a,b) | YES | YES | |
BITAND(a,b) | YES | YES | |
BITOR(a,b) | YES | YES | |
CEILING(d) | YES | YES | |
COS(d) | YES | YES | |
COT(d) | YES | YES | |
DEGREES(d) | YES | YES | |
EXP(d) | YES | YES | |
FLOOR(d) | YES | YES | |
LOG(d) | YES | YES | |
LOG10(d) | YES | YES | |
MOD(a,b) | YES | YES | |
PI() | YES | YES | |
POWER(a,b) | YES | YES | |
RADIANS(d) | YES | YES | |
RAND() | YES | YES | |
ROUND(a,b) | YES | YES | |
ROUNDMAGIC(d) | YES | YES | |
SIGN( d ) | YES | YES | |
SIN(d) | YES | YES | |
SQRT( d ) | YES | YES | |
TAN( A ) | YES | YES | |
TRUNCATE(a,b) | YES | YES |
Function Name | Desinger | Esc Proc | Comments |
VAR_POP | |||
VAR_SAMP | |||
STDDEV_POP | |||
STDDEV_SAMP |
Function Name | Desinger | Esc Proc | Comments |
ASCII(s) | YES | YES | |
BIT_LENGTH(str) | YES | YES | |
CHAR(c) | YES | YES | |
CHAR_LENGTH(str) | YES | YES | |
CONCAT(str1,str2) | YES | YES | |
DIFFERENCE(s1,s2) | YES | YES | |
HEXTORAW(s1) | YES | YES | |
INSERT(s,start,len,s2) | YES | YES | |
LCASE(s) | YES | YES | |
LEFT(s,count) | YES | YES | |
LENGTH(s) | YES | YES | |
LOCATE(search,s,[start]) | YES | YES | |
LTRIM(s) | YES | YES | |
OCTET_LENGTH(str) | YES | YES | |
RAWTOHEX(s1) | YES | YES | |
POSITION (... IN ..) | YES | YES | |
REPEAT(s,count) | YES | YES | |
REPLACE(s,replace,s2) | YES | YES | |
RIGHT(s,count) | YES | YES | |
RTRIM(s) | YES | YES | |
SOUNDEX(s) | YES | YES | |
SPACE(count) | YES | YES | |
SUBSTR(s,start[,len]) | YES | YES | |
SUBSTRING(s,start[,len]) | YES | YES | |
SUBSTRING(... FROM ... FOR ...) | YES | YES | |
TRIM( LEADING TRAILING BOTH FROM <COLUMN> ) | YES | YES | |
UCASE(s) | YES | YES | |
LOWER(s) | YES | YES | |
UPPER(s) | YES | YES |
Function Name | Desinger | Esc Proc | Comments |
CURDATE() | YES | YES | |
CURTIME() | YES | YES | |
DATEDIFF(string, datetime1, datetime2) | YES | YES | |
DAYNAME(date) | YES | YES | |
DAYOFMONTH(date) | YES | YES | |
DAYOFWEEK(date) | YES | YES | |
DAYOFYEAR(date) | YES | YES | |
HOUR(time) | YES | YES | |
MINUTE(time) | YES | YES | |
MONTH(date) | YES | YES | |
MONTHNAME(date) | YES | YES | |
NOW() | YES | YES | |
QUARTER(date) | YES | YES | |
SECOND(time) | YES | YES | |
WEEK(date) | YES | YES | |
YEAR(date) | YES | YES | |
CURRENT_DATE | YES | YES | |
CURRENT_TIME | YES | YES | |
CURRENT_TIMESTAMP | YES | YES | |
TO_CHAR( <date column>, format String ) | YES | YES |
Function Name | Desinger | Esc Proc | Comments |
DATABASE() | YES | YES | |
USER() | NO | NO | |
CURRENT_USER | YES | YES | |
IDENTITY() | YES | YES |