Difference between revisions of "Built-in functions and Stored Procedures"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (→‎Numerical Functions)
(→‎Numerical Functions)
Line 43: Line 43:
 
|-
 
|-
 
! ASIN(d)
 
! ASIN(d)
−
|YES ||YES || Returns the arc sine of an angle, in the range of -pi/2 through pi/2. <BR> ASIN(.5) = 0.52
+
|YES ||YES || Returns the arc sine of an angle, in the range of -pi/2 through pi/2. <BR> ASIN( .5 ) = 0.52
 
|-
 
|-
 
! ATAN(d)
 
! ATAN(d)
−
| YES || YES || Returns the arc tangent of an angle, in the range of -pi/2 through pi/2. <BR> ATAN(.5) = 0.46
+
| YES || YES || Returns the arc tangent of an angle, in the range of -pi/2 through pi/2. <BR> ATAN( .5 ) = 0.46
 
|-
 
|-
 
! ATAN2(a,b)
 
! ATAN2(a,b)
−
| YES || YES || Returns the tangent of a/b. <BR> ATAN2(1,2) = 0.46
+
| YES || YES || Returns the tangent of a/b. <BR> ATAN2( 1, 2 ) = 0.46
 
|-
 
|-
 
! BITAND(a,b)
 
! BITAND(a,b)
−
| YES || YES || Returns a & b. <BR> BITAND(0,1) = 0
+
| YES || YES || Returns a & b. <BR> BITAND( 0, 1 ) = 0
 
|-
 
|-
 
! BITOR(a,b)
 
! BITOR(a,b)
−
| YES ||YES || <NOWIKI>Returns a | b </NOWIKI> <BR> BITOR(0,1) = 1  
+
| YES ||YES || <NOWIKI>Returns a | b </NOWIKI> <BR> BITOR( 0,1 ) = 1  
 
|-
 
|-
 
! CEILING(d)
 
! CEILING(d)
−
| YES ||YES || returns the smallest integer that is not less than d <BR> CEILING( 8.7 ) = 9
+
| YES ||YES || Rreturns the smallest integer that is not less than d <BR> CEILING( 8.7 ) = 9
 
|-
 
|-
 
! COS(d)
 
! COS(d)
Line 64: Line 64:
 
|-
 
|-
 
! COT(d)
 
! COT(d)
−
| YES ||YES ||  
+
| YES ||YES || Returns the cotangent of an angle. <br> COT( .5 ) = 1.83
 
|-
 
|-
 
! DEGREES(d)
 
! DEGREES(d)
−
| YES ||YES ||
+
| YES ||YES || converts radians to degrees. <BR> DEGREES( 1.5  ) = 85.94
 
|-
 
|-
 
! EXP(d)
 
! EXP(d)
−
| YES ||YES ||
+
| YES ||YES || Returns the exponential number e (i.e., 2.718...) raised to the power of a double value. <BR> EXP( 2 ) = 7.389056
 
|-
 
|-
 
! FLOOR(d)
 
! FLOOR(d)
−
| YES ||YES ||
+
| YES ||YES || Returns the largest double value that is not greater than the d. <BR> FLOOR( -8.7 ) = -9
 
|-
 
|-
 
! LOG(d)
 
! LOG(d)
−
| YES ||YES ||
+
| NO ||NO || Returns the natural logarithm (base e) of d. <BR> d must be greater than 0.0. <br> LOG( 1.5 ) = 0.41 <BR> LOG( -1 ) = -1.#NAN <br> SEE [http://www.openoffice.org/issues/show_bug.cgi?id=69054 ISSUE#69054]
 
|-
 
|-
 
! LOG10(d)
 
! LOG10(d)
−
| YES ||YES ||
+
| YES ||YES || returns the logarithm (base 10) of d. <br> d must be greater than 0.0. <br> LOG10( 1.5 ) = 0.18 <br> LOG10( -1 ) = -1.#NAN
 
|-
 
|-
 
! MOD(a,b)
 
! MOD(a,b)

Revision as of 10:45, 26 August 2007

Overview

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 component 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.
  • 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.


Numerical Functions

Function Name Desinger Esc Proc Comments
ABS(d) YES YES Returns the absolute value of a number.
ABS(-1) = ABS(1)
ACOS(d) YES YES Returns the arc cosine of an angle, in the range of 0.0 through pi.
ACOS(.5) = 1.05
ASIN(d) YES YES Returns the arc sine of an angle, in the range of -pi/2 through pi/2.
ASIN( .5 ) = 0.52
ATAN(d) YES YES Returns the arc tangent of an angle, in the range of -pi/2 through pi/2.
ATAN( .5 ) = 0.46
ATAN2(a,b) YES YES Returns the tangent of a/b.
ATAN2( 1, 2 ) = 0.46
BITAND(a,b) YES YES Returns a & b.
BITAND( 0, 1 ) = 0
BITOR(a,b) YES YES Returns a | b
BITOR( 0,1 ) = 1
CEILING(d) YES YES Rreturns the smallest integer that is not less than d
CEILING( 8.7 ) = 9
COS(d) YES YES Returns the trigonometric cosine of an angle.
COS( .5 ) = 0.88
COT(d) YES YES Returns the cotangent of an angle.
COT( .5 ) = 1.83
DEGREES(d) YES YES converts radians to degrees.
DEGREES( 1.5 ) = 85.94
EXP(d) YES YES Returns the exponential number e (i.e., 2.718...) raised to the power of a double value.
EXP( 2 ) = 7.389056
FLOOR(d) YES YES Returns the largest double value that is not greater than the d.
FLOOR( -8.7 ) = -9
LOG(d) NO NO Returns the natural logarithm (base e) of d.
d must be greater than 0.0.
LOG( 1.5 ) = 0.41
LOG( -1 ) = -1.#NAN
SEE ISSUE#69054
LOG10(d) YES YES returns the logarithm (base 10) of d.
d must be greater than 0.0.
LOG10( 1.5 ) = 0.18
LOG10( -1 ) = -1.#NAN
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

Statistical Functions

Function Name Desinger Esc Proc Comments
VAR_POP
VAR_SAMP
STDDEV_POP
STDDEV_SAMP


String Fnctions

-
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


Date and Time Functions

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


System Functions

Function Name Desinger Esc Proc Comments
DATABASE() YES YES
USER() NO NO
CURRENT_USER YES YES
IDENTITY() YES YES
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Built-in_functions_and_Stored_Procedures&oldid=43524"
Personal tools