Oracle SQL Funktionen

Die Text sind alle oder teilweise in englisch und werden noch ins deutsche übersetz.

ABS | ACOS | ADD_MONTHS | ALL | ASCII | ASIN | ATAN | ATAN2 | AVG | CEIL | CHARTOROWID | CHR | CONCAT | CONVERT | COS | COSH | COUNT | DISTINCT | DUMP | EXP | FLOOR | GLB | GREATEST | GREATEST_LB | HEXTORAW | INITCAP | INSTR | INSTRB | LAST_DAY | LEAST | LEAST_UB | LENGTH | LENGTHB | LN | LOG | LOWER(char) | LPAD | LTRIM | LUB | MAX | MIN | MOD | MONTHS_BETWEEN | NEW_TIME | NEXT_DAY | NLSSORT | NLS_INITCAP | NLS_LOWER | NLS_UPPER | NVL | POWER | RAWTOHEX | REPLACE | ROUND | ROUND and TRUNC (Date) | ROWIDTOCHAR | RPAD | RTRIM | SIGN | SIN | SINH | SOUNDEX(char) | SQRT | STDDEV | SUBSTR | SUBSTRB | SUM | SYSDATE | TAN | TANH | TO_CHAR, date conversion | TO_CHAR, label conversion | TO_CHAR, number conversion | TO_DATE | TO_LABEL | TO_MULTI_BYTE(char) | TO_NUMBER | TO_SINGLE_BYTE(char) | TRANSLATE | TRUNC | UID | UPPER | USER | USERENV | VARIANCE | VSIZE |

ABS

Syntax:

ABS(n)

Erklärung:

Returns the absolute value of n

Beispiel:


SELECT ABS(-15) "Absolute" FROM DUAL

  Absolute
----------
        15

Seitenanfang

ACOS

Syntax:

ACOS(n)

Erklärung:

Returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.

Beispiel:


SELECT ACOS(.3) "Arc_Cosine" FROM DUAL

Arc_Cosine
----------
1.26610367

Seitenanfang

ADD_MONTHS

Syntax:

ADD_MONTHS(d,n)

Erklärung:

Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

Beispiel:


SELECT TO_CHAR(
	ADD_MONTHS(hiredate,1),
	'DD-MON-YYYY') "Next month"
	FROM emp
	WHERE ename = 'SMITH'

Next Month
-----------
17-JAN-1981

Seitenanfang

ALL

Syntax:


Erklärung:

This option causes a group function to consider all values including all duplicates. For example, the DISTINCT average of 1, 1, 1, and 3 is 2; the ALL average is 1.5. If neither option is specified, the default is ALL. All group functions except COUNT(*) ignore nulls. You can use the NVL in the argument to a group function to substitute a value for a null. If a query with a group function returns no rows or only rows with nulls for the argument to the group function, the group function returns null.

Beispiel:



Seitenanfang
ASCII
Syntax:
ASCII(char)
Erklärung:
Returns the decimal representation in the database character set of the first byte of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.
Beispiel:

SELECT ASCII('Q') FROM DUAL

ASCII('Q')
----------
        81

Seitenanfang
ASIN
Syntax:
ASIN(n)
Erklärung:
Returns the arc sine of n. Inputs are in the range of -1 to 1, and outputs are in the range of -pi/2 to pi/2 and are expressed in radians.
Beispiel:

SELECT ASIN(.3) "Arc_Sine" FROM DUAL

  Arc_Sine
----------
.304692654

Seitenanfang
ATAN
Syntax:
ATAN^(n)
Erklärung:
Returns the arc tangent of n. Inputs are in an unbounded range, and outputs are in the range of -pi/2 to pi/2 and are expressed in
Beispiel:

SELECT ATAN(.3) "Arc_Tangent" FROM DUAL

Arc_Tangent
-----------
 .291456794

Seitenanfang
ATAN2
Syntax:
ATAN2(n, m)
Erklärung:
Returns the arc tangent of n and m. Inputs are in an unbounded range, and outputs are in the range of -pi to pi, depending on the signs of x and y, and are expressed in radians. Atan2(x,y) is the same as atan2(x/y)
Beispiel:

SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL

Arc_Tangent2
------------
  .982793723

Seitenanfang
AVG
Syntax:
AVG([DISTINCT|ALL] n)
Erklärung:
Returns average value of n.
Beispiel:

SELECT AVG(sal) "Average"
	FROM emp

   Average
----------
2077.21429

Seitenanfang
CEIL
Syntax:
CEIL(n)
Erklärung:
Returns smallest integer greater than or equal to n.
Beispiel:

SELECT CEIL(15.7) "Ceiling" FROM DUAL

   Ceiling
----------
        16

Seitenanfang
CHARTOROWID
Syntax:
CHARTOROWID(char)
Erklärung:
Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.
Beispiel:

SELECT ename
	FROM emp
	WHERE ROWID = CHARTOROWID('0000000F.0003.0002')

ENAME
-----
SMITH

Seitenanfang
CHR
Syntax:
CHR(n)
Erklärung:
Returns the character having the binary equivalent to n in the database character set.
Beispiel:

SELECT CHR(67)||CHR(65)||CHR(84) "Dog"  FROM DUAL

Dog
---
CAT

Seitenanfang
CONCAT
Syntax:
CONCAT(char1, char2)
Erklärung:
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see the section "Character"
Beispiel:

This example uses nesting to concatenate three character strings: 

SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job" FROM emp WHERE empno = 7900

Job
-------------------------
JAMES is a CLERK

Seitenanfang
CONVERT
Syntax:
CONVERT(char, dest_char_set [,source_char_set] )
Erklärung:
Converts a character string from one character set to another. 
The char argument is the value to be converted. 
The dest_char_set argument is the name of the character set to which char is converted. 
The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set. 
Both the destination and source character set arguments can be either literals or columns containing the name of the character set. 
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition. 
Common character sets include: 
<table border><td align=left valign=top><tt>US7ASCII</tt>
<td align=left valign=top>US 7-bit ASCII character set
<tr>
<td align=left valign=top><tt>WE8DEC</tt>
<td align=left valign=top>DEC West European 8-bit character set
<tr>
<td align=left valign=top><tt>WE8HP</tt>
<td align=left valign=top>HP West European Laserjet 8-bit character set
<tr>
<td align=left valign=top><tt>F7DEC</tt>
<td align=left valign=top>DEC French 7-bit character set
<tr>
<td align=left valign=top><tt>WE8EBCDIC500</tt>
<td align=left valign=top>IBM West European EBCDIC Code Page 500
<tr>
<td align=left valign=top><tt>WE8PC850</tt>
<td align=left valign=top>IBM PC Code Page 850
<tr>
<td align=left valign=top><tt>WE8ISO8859P1</tt>
<td align=left valign=top>ISO 8859-1 West European 8-bit character set
<tr>

</table>
Beispiel:

SELECT CONVERT('Groß', 'WE8HP', 'WE8DEC') "Conversion" FROM DUAL

Conversion
----------
Groß

Seitenanfang
COS
Syntax:
COS(n)
Erklärung:
Returns the cosine of n (an angle expressed in radians).
Beispiel:

SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees"  FROM DUAL

Cosine of 180 degrees
---------------------
                   -1

Seitenanfang
COSH
Syntax:
COSH(n)
Erklärung:
Returns the hyperbolic cosine of n.
Beispiel:

SELECT COSH(0) "Hyperbolic cosine of 0"  FROM DUAL

Hyperbolic cosine of 0
----------------------
                     1

Seitenanfang
COUNT
Syntax:
COUNT({* | [DISTINCT|ALL] expr})
Erklärung:
Returns the number of rows in the query. 

If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr. 

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.
Beispiel:

SELECT COUNT(*) "Total"
	FROM emp

     Total
----------
        18
SELECT COUNT(job) "Count"
	FROM emp

     Count
----------
        14

SELECT COUNT(DISTINCT job) "Jobs"
	FROM emp

      Jobs
----------
         5

Seitenanfang
DISTINCT
Syntax:

Erklärung:
This option causes a group function to consider only distinct values of the argument expression.
Beispiel:


Seitenanfang
DUMP
Syntax:
DUMP(expr[,return_format[,start_position[,length]] ] )
Erklärung:
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The argument return_format specifies the format of the return value and can have any of these values: 

8 returns result in octal notation. 
10 returns result in decimal notation. 
16 returns result in hexadecimal notation. 
17 returns result as single characters. 

The arguments start_position and length combine to determine which portion of the internal representation to return. The default is to return the entire internal representation in decimal notation. 

If expr is null, this function returns 'NULL'.
Beispiel:

SELECT DUMP(ename, 8, 3, 2) "OCTAL"
	FROM emp
	WHERE ename = 'SCOTT'

OCTAL
---------------------------------
Type=1 Len=5: 117,124

SELECT DUMP(ename, 10, 3, 2) "ASCII"
	FROM emp
	WHERE ename = 'SCOTT'

ASCII
----------------------------
Type=1 Len=5: 79,84

Seitenanfang
EXP
Syntax:
EXP(n)
Erklärung:
Returns e raised to the nth power; e = 2.71828183 ...
Beispiel:

SELECT EXP(4) "e to the 4th power" FROM DUAL

e to the 4th power
------------------
          54.59815

Seitenanfang
FLOOR
Syntax:
FLOOR(n)
Erklärung:
Returns largest integer equal to or less than n.
Beispiel:

SELECT FLOOR(15.7) "Floor" FROM DUAL

     Floor
----------
        15

Seitenanfang
GLB
Syntax:
GLB([DISTINCT|ALL] label)
Erklärung:
Returns the greatest lower bound of label. For the definitions of greatest lower bound and example usage, see Trusted Oracl Server Administrator's Guide.
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
GREATEST
Syntax:
GREATEST(expr [,expr] ...)
Erklärung:
Returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first exprs before the comparison. Oracle7 compares the exprs using non-padded comparison semantics. Character comparison is based on the value of the character in the database character set. One character is greater than another if it has a higher value. If the value returned by this function is character data, its datatype is always VARCHAR2.
Beispiel:

SELECT GREATEST('HARRY','HARRIOT','HAROLD') "GREATEST"
	FROM DUAL

GREATEST
--------
HARRY

Seitenanfang
GREATEST_LB
Syntax:
GREATEST_LB(label [,label] ...)
Erklärung:
Returns the greatest lower bound of the list of labels. Each label must either have datatype MLSLABEL or RAW MLSLABEL or be a quoted literal in the default label format. The return value has datatype RAW MLSLABEL. 

For the definition of greatest lower bound and examples of this function, see Trusted Oracle7 Server Administrator's Guide.
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
HEXTORAW
Syntax:
HEXTORAW(char)
Erklärung:
Converts char containing hexadecimal digits to a raw value.
Beispiel:

INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL

Seitenanfang
INITCAP
Syntax:
INITCAP(char)
Erklärung:
Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.
Beispiel:

SELECT INITCAP('the soap') "Capitals" FROM DUAL

Capitals
--------
The Soap

Seitenanfang
INSTR
Syntax:
INSTR(char1,char2[,n[,m]])
Erklärung:
Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle7 counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle7 begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.
Beispiel:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL

Instring
 ---------
        14

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL

Reversed Instring
-----------------
                2

Seitenanfang
INSTRB
Syntax:
INSTRB(char1,char2[,n[,m]])
Erklärung:
The same as INSTR, except that n and the return value are expressed in bytes, rather than in characters. For a single-byte database character set, INSTRB is equivalent to INSTR.
Beispiel:

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL

Instring in bytes
-----------------
               27

Seitenanfang
LAST_DAY
Syntax:
LAST_DAY(d)
Erklärung:
Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.
Beispiel:

SELECT SYSDATE,
	LAST_DAY(SYSDATE) "Last",
	LAST_DAY(SYSDATE) - SYSDATE "Days Left"
	FROM DUAL

SYSDATE   Last       Days Left
--------- --------- ----------
10-APR-95 30-APR-95         20

SELECT TO_CHAR(
	ADD_MONTHS(
		LAST_DAY(hiredate),5),
		'DD-MON-YYYY') "Five months"
	FROM emp
	WHERE ename = 'MARTIN'

Five months
-----------
28-FEB-1982

SELECT TO_CHAR(ADD_MONTHS(hiredate,1),

'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH' Next month ----------- 17-JAN-1981

Seitenanfang
LEAST
Syntax:
LEAST(expr [,expr] ...)
Erklärung:
Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle7 compares the exprs using non-padded comparison semantics. If the value returned by this function is character data, its datatype is always VARCHAR2.
Beispiel:

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"
	FROM DUAL

LEAST
------
HAROLD

Seitenanfang
LEAST_UB
Syntax:
LEAST_UB(label [,label] ...)
Erklärung:
Returns the least upper bound of the list of labels. Each label must have datatype MLSLABEL or be a quoted literal in the default label format. The return value has datatype RAW MLSLABEL. For the definition of least upper bound and examples of this function, see Trusted Oracle7 Server Administrator's Guide
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
LENGTH
Syntax:
LENGTH(char)
Erklärung:
Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.
Beispiel:

SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL

Length in characters
--------------------
                   7

Seitenanfang
LENGTHB
Syntax:
LENGTHB(char)
Erklärung:
Returns the length of char in bytes. If char is null, this function returns null. For a single-byte database character set, LENGTHB is equivalent to LENGTH.
Beispiel:

Assume a double-byte database character set:

SELECT LENGTH('CANDIDE') "Length in bytes" FROM DUAL

Length in bytes
---------------
             14

Seitenanfang
LN
Syntax:
LN(n)
Erklärung:
Returns the natural logarithm of n, where n is greater than 0.
Beispiel:

SELECT LN(95) "Natural log of 95" FROM DUAL

Natural log of 95
-----------------
       4.55387689

Seitenanfang
LOG
Syntax:
LOG(m, n)
Erklärung:
Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1 and n can be any positive number.
Beispiel:

SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL

Log base 10 of 100
------------------
                 2

Seitenanfang
LOWER(char)
Syntax:
LOWER
Erklärung:
Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).
Beispiel:

SELECT LOWER('MR. SAMUEL HILLHOUSE') "Lowercase" FROM DUAL

Lowercase
--------------------
mr. samuel hillhouse

Seitenanfang
LPAD
Syntax:
 LPAD(char1,n [,char2])
Erklärung:
Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n. 



The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
Beispiel:

SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL

LPAD example
---------------
*.*.*.*.*Page 1

Seitenanfang
LTRIM
Syntax:
LTRIM(char1,n [,set])
Erklärung:
Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle7 begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
Beispiel:

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"  FROM DUAL

LTRIM example
-------------
Xxy LAST WORD

Seitenanfang
LUB
Syntax:
LUB([DISTINCT|ALL] label)
Erklärung:
Returns the least upper bound of label. 

The return values have datatype MLSLABEL. For the definitions of greatest least upper bound and example usage, see Trusted Oracle  Server Administrator's Guide.
Beispiel:


Seitenanfang
MAX
Syntax:
MAX([DISTINCT|ALL] expr)
Erklärung:
Returns maximum value of expr.
Beispiel:

SELECT MAX(sal) "Maximum"
	FROM emp

   Maximum
----------
      5004

Seitenanfang
MIN
Syntax:
MIN([DISTINCT|ALL] expr)
Erklärung:
Returns minimum value of expr. Note. The DISTINCT and ALL options have no effect on the MAX and MIN functions.
Beispiel:

SELECT MIN(hiredate) "Minimum Date"
	FROM emp

Minimum Date
------------
17-DEC-80

Seitenanfang
MOD
Syntax:
Syntax MOD(m,n)
Erklärung:
Returns remainder of m divided by n. Returns m if n is 0.
Beispiel:

SELECT MOD(11,4) "Modulus" FROM DUAL

   Modulus
----------
         3
Note 

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula: 

m - n * FLOOR(m/n)

The following statement illustrates the difference between the MOD function and the classical modulus: 

SELECT m, n, MOD(m, n),
m - n * FLOOR(m/n) "Classical Modulus" FROM test_mod_table

  M   N MOD (M,N) Classical Modulus
--- ---- -------- --------- -------
 11   4        3
-11   4       -3                  1
 11  -4        3                 -1
-11  -4       -3                 -3

Seitenanfang
MONTHS_BETWEEN
Syntax:
MONTHS_BETWEEN(d1, d2)
Erklärung:
Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle7 calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.
Beispiel:

SELECT MONTHS_BETWEEN(
	TO_DATE('02-02-1995','MM-DD-YYYY'),
	TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
	FROM DUAL

    Months
----------
1.03225806

Seitenanfang
NEW_TIME
Syntax:
NEW_TIME(d, z1, z2)
Erklärung:
Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:
Beispiel:

AST
ADT
Atlantic Standard or Daylight Time


BST
BDT
Bering Standard or Daylight Time


CST
CDT
Central Standard or Daylight Time


EST
EDT
Eastern Standard or Daylight Time


GMT
Greenwich Mean Time


HST
HDT
Alaska-Hawaii Standard Time or Daylight Time.


MST
MDT
Mountain Standard or Daylight Time


NST
Newfoundland Standard Time


PST
PDT
Pacific Standard or Daylight Time


YST
YDT
Yukon Standard or Daylight Time





Seitenanfang
NEXT_DAY
Syntax:
NEXT_DAY(d, char)
Erklärung:
Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language. The return value has the same hours, minutes, and seconds component as the argument d.
Beispiel:

This example returns the date of the next Tuesday after March 15, 1992.



SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY"
	FROM DUAL

NEXT DAY
---------
17-MAR-92

Seitenanfang
NLSSORT
Syntax:
NLSSORT(char [, 'nlsparams'])
Erklärung:
Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form 



			'NLS_SORT = sort'


where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char.
Beispiel:

This function can be used to specify comparisons based on a linguistic sort sequence rather on the binary value of a string: 

SELECT * FROM emp WHERE NLSSORT(ename,'NLS_SORT = German') > NLSSORT('B','NLS_SORT = German')

Seitenanfang
NLS_INITCAP
Syntax:
NLS_INITCAP(char [, 'nlsparams'] )
Erklärung:
Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of 'nlsparams' can have this form: 



'NLS_SORT = sort'



where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparams', this function uses the default sort sequence for your session.
Beispiel:

SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "Capitalized" FROM DUAL

Capital
-------
IJsland

Seitenanfang
NLS_LOWER
Syntax:
NLS_LOWER(char [, 'nlsparams'] )
Erklärung:
Returns char, with all letters lowercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.
Beispiel:

SELECT NLS_LOWER('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL

Lower
-----
città 

Seitenanfang
NLS_UPPER
Syntax:
NLS_UPPER(char [, 'nlsparams'] )
Erklärung:
Returns char, with all letters uppercase. The 'nlsparams' can have the same form and serve the same purpose as in the NLS_INITCAP function.
Beispiel:

SELECT NLS_UPPER('gro?e', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL

Upper
-----
GROSS

Seitenanfang
NVL
Syntax:
NVL(expr1, expr2)
Erklärung:
If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle7 converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data in which case the return value's datatype is VARCHAR2.
Beispiel:

SELECT ename, NVL(TO_CHAR(COMM),'NOT APPLICABLE') "COMMISSION"
	FROM emp
	WHERE deptno = 30

ENAME     COMMISSION
--------- -----------
ALLEN     300
WARD      500
MARTIN    1400
BLAKE     NOT APPLICABLE
TURNER    0
JAMES     NOT APPLICABLE

Seitenanfang
POWER
Syntax:
POWER(m, n)
Erklärung:
Returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, n must be an integer.
Beispiel:

SELECT POWER(3,2) "Raised" FROM DUAL

    Raised
----------
         9

Seitenanfang
RAWTOHEX
Syntax:
RAWTOHEX(raw)
Erklärung:
Converts raw to a character value containing its hexadecimal equivalent.
Beispiel:

SELECT RAWTOHEX(raw_column) "Graphics" FROM graphics

Graphics
--------
7D

Seitenanfang
REPLACE
Syntax:
 REPLACE(char, search_string[,replacement_string])
Erklärung:
Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single character, one to one, substitution. REPLACE allows you to substitute one string for another as well as to remove character strings.
Beispiel:

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL

Changes
--------------
BLACK and BLUE

Seitenanfang
ROUND
Syntax:
ROUND(n[,m]); ROUND(d[,fmt])
Erklärung:
Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer. Format 2: Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day.
Beispiel:

SELECT ROUND(15.193,1) "Round" FROM DUAL

     Round
----------
      15.2

SELECT ROUND(15.193,-1) "Round" FROM DUAL

     Round
----------
        20

SELECT ROUND(TO_DATE('27-OCT-92'),'YEAR')
"FIRST OF THE YEAR"
	FROM DUAL

FIRST OF THE YEAR
-----------------
01-JAN-93

Seitenanfang
ROUND and TRUNC (Date)
Syntax:

Erklärung:
The table below lists the format models to be used with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.
Beispiel:

Format Model
Rounding or Truncating Unit


CC
SCC
Century


SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
Year (rounds up on July 1)


IYYY
IY
IY
I
ISO Year


Q
Quarter (rounds up on the sixteenth day of the second month of the quarter)


MONTH
MON
MM
RM
Month (rounds up on the sixteenth day)


WW
Same day of the week as the first day of the year.


IW
Same day of the week as the first day of the ISO year.


W
Same day of the week as the first day of the month.


DDD
DD
J
Day


DAY
DY
D
Starting day of the week


HH
HH12
HH24
Hour


MI
Minute


Seitenanfang
ROWIDTOCHAR
Syntax:
ROWIDTOCHAR(rowid)
Erklärung:
Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.
Beispiel:

SELECT ROWID FROM graphics WHERE ROWIDTOCHAR(ROWID) LIKE '%F38%'

ROWID
------------------
00000F38.0001.0001

Seitenanfang
RPAD
Syntax:
RPAD(char1, n [,char2])
Erklärung:
Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n. 



The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multi-byte character sets, the display length of a character string can differ from the number of characters in the string.
Beispiel:

SELECT RPAD(ename,12,'ab') "RPAD example" FROM emp WHERE ename = 'TURNER'

RPAD example
------------
TURNERababab

Seitenanfang
RTRIM
Syntax:
RTRIM(char [,set]
Erklärung:
Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.
Beispiel:

SELECT RTRIM('TURNERyxXxy','xy') "RTRIM e.g." FROM DUAL

RTRIM e.g
---------
TURNERyxX

Seitenanfang
SIGN
Syntax:
SIGN(n)
Erklärung:
If n<0, the function returns -1; if n=0, the function returns 0; if n>0, the function returns 1.
Beispiel:

SELECT SIGN(-15) "Sign" FROM DUAL

      Sign
----------
        -1

Seitenanfang
SIN
Syntax:
SIN(n)
Erklärung:
Returns the sine of n (an angle expressed in radians).
Beispiel:

SELECT SIN(30 * 3.14159265359/180)  "Sine of 30 degrees" FROM DUAL

Sine of 30 degrees
------------------
                .5

Seitenanfang
SINH
Syntax:
SINH(n)
Erklärung:
Returns the hyperbolic sine of n.
Beispiel:

SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL

Hyperbolic sine of 1
--------------------
          1.17520119

Seitenanfang
SOUNDEX(char)
Syntax:
SOUNDEX
Erklärung:
Returns a character string containing the phonetic representation of char. This function allows you to compare words that are spelled differently, but sound alike in English. 



The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:



retain the first letter of the string and remove the following letters: a, e, h, i, o, w, y

assign the numbers to the remaining letters as follows: 0 = a, e, h, i, o, w, y 1 = b, f, p, v 2 = c, e, g, j, k, q, s, x, z 3 = d, t = 3 4 = l 5 = m, n r = 6

if two or more of the numbers are in sequences, remove all but the first

return the first four bytes padded with 0
Beispiel:

SELECT ename FROM emp WHERE SOUNDEX(ename)  = SOUNDEX('SMYTHE')

ENAME
----------
SMITH

Seitenanfang
SQRT
Syntax:
SQRT(n)
Erklärung:
Returns square root of n. The value n cannot be negative. SQRT returns a "real" result.
Beispiel:

SELECT SQRT(26) "Square root" FROM DUAL

Square root
-----------
 5.09901951

Seitenanfang
STDDEV
Syntax:
STDDEV([DISTINCT|ALL] x)
Erklärung:
Returns standard deviation of x, a number. Oracle7 calculates the standard deviation as the square root of the variance defined for the VARIANCE group function.
Beispiel:

SELECT STDDEV(sal) "Deviation"
	FROM emp

 Deviation
----------
1182.50322

Seitenanfang
SUBSTR
Syntax:
SUBSTR(char, m [,n])
Erklärung:
Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle7 counts from the beginning of char to find the first character. If m is negative, Oracle7 counts backwards from the end of char. If n is omitted, Oracle7 returns all characters to the end of char. If n is less than 1, a null is returned.



Floating point numbers passed as arguments to substr are automatically converted to integers.
Beispiel:

SELECT SUBSTR('ABCDEFG',3.1,4) "Subs" FROM DUAL

Subs
----
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Subs" FROM DUAL

Subs
----
CDEF

Seitenanfang
SUBSTRB
Syntax:
SUBSTRB(char, m [,n])
Erklärung:
The same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than in characters. For a single-byte database character set, SUBSTRB is equivalent to SUBSTR. 



Floating point numbers passed as arguments to substrb are automatically converted to integers.
Beispiel:

Assume a double-byte database character set: 



SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL

Sub
---
CD

Seitenanfang
SUM
Syntax:
SUM([DISTINCT|ALL] n)
Erklärung:
Returns sum of values of n.
Beispiel:

SELECT SUM(sal) "Total"
	FROM emp

     Total
----------
     29081

Seitenanfang
SYSDATE
Syntax:
SYSDATE
Erklärung:
Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.
Beispiel:

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') NOW
	FROM DUAL

NOW
-------------------
10-29-1993 20:27:11.

Seitenanfang
TAN
Syntax:
TAN(n)
Erklärung:
Returns the tangent of n (an angle expressed in radians).
Beispiel:

SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL

Tangent of 135 degrees
----------------------
                    -1

Seitenanfang
TANH
Syntax:
TANH(n)
Erklärung:
Returns the hyperbolic tangent of n.
Beispiel:

SELECT TANH(.5) "Hyperbolic tangent of .5" FROM DUAL

Hyperbolic tangent of .5
------------------------
              .462117157

Seitenanfang
TO_CHAR, date conversion
Syntax:
TO_CHAR(d [, fmt [, 'nlsparams'] ])
Erklärung:
Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see the section "Format Models" . 
The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form: 
'NLS_DATE_LANGUAGE = language' 
If you omit nlsparams, this function uses the default date language for your session.
Beispiel:

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY') "New date format"  FROM emp WHERE ename = 'SMITH'

New date format
-------------------------------
December 17, 1980

Seitenanfang
TO_CHAR, label conversion
Syntax:
Syntax TO_CHAR(label [, fmt])
Erklärung:
Converts label of MLSLABEL datatype to a value of VARCHAR2 datatype, using the optional label format fmt. If you omit fmt, label is converted to a VARCHAR2 value in the default label format. 
For more information on this function, see Trusted Oracle7 Server Administrator's Guide.
Beispiel:

. Kein Beispiel vorhanden -

Seitenanfang
TO_CHAR, number conversion
Syntax:
TO_CHAR(n [, fmt [, 'nlsparams'] ])
Erklärung:
Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see the section "Format Models" . 

The 'nlsparams' specifies these characters that are returned by number format elements: 

decimal character 

group separator 

local currency symbol 

international currency symbol 

This argument can have this form: 

'NLS_NUMERIC_CHARACTERS = ''dg''
 NLS_CURRENCY = ''text''
 NLS_ISO_CURRENCY = territory '

The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.
Beispiel:

Example I 

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount" FROM DUAL Amount
--------------------
         $10,000.00-


Note how the output above is blank padded to the left of the currency symbol. 

Example II 

SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL

Amount
--------------------
AusDollars10.000,00-

Seitenanfang
TO_DATE
Syntax:
TO_DATE(char [, fmt [, 'nlsparams'] ])
Erklärung:
Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. For information on date formats, see the section "Format Models" . 
The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion. 
Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format.
Beispiel:

INSERT INTO bonus (bonus_date)
	SELECT TO_DATE(
		'January 15, 1989, 11:00 A.M.',
		'Month dd, YYYY, HH:MI A.M.',
		'NLS_DATE_LANGUAGE = American')
		FROM DUAL

Seitenanfang
TO_LABEL
Syntax:
TO_LABEL(char [,fmt])
Erklärung:
Converts char, a value of datatype CHAR or VARCHAR2 containing a label in the format specified by the optional parameter fmt, to a value of MLSLABEL datatype. If you omit fmt, char must be in the default label format.
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
TO_MULTI_BYTE(char)
Syntax:
TO_MULTI_BYTE
Erklärung:
Returns char with all of its single-byte characters converted to their corresponding multi-byte characters. Any single-byte characters in char that have no multi-byte equivalents appear in the output string as single-byte characters. This function is only useful if your database character set contains both single-byte and multi-byte characters.
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
TO_NUMBER
Syntax:
TO_NUMBER(char [,fmt [, 'nlsparams'] ])
Erklärung:
Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.
Beispiel:

UPDATE emp
 SET sal = sal +
   TO_NUMBER('100.00', '9G999D99')
 WHERE ename = 'BLAKE'

The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for number conversion.

Example 

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY           = ''AusDollars''
   ') "Amount" FROM DUAL

    Amount
----------
      -100

Seitenanfang
TO_SINGLE_BYTE(char)
Syntax:
TO_SINGLE_BYTE
Erklärung:
Returns char with all of its multi-byte character converted to their corresponding single-byte characters. Any multi-byte characters in char that have no single-byte equivalents appear in the output as multi-byte characters. This function is only useful if your database character set contains both single-byte and multi-byte characters.
Beispiel:

- kein Beispiel vorhanden -

Seitenanfang
TRANSLATE
Syntax:
TRANSLATE(char, from, to)
Erklärung:
Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle7 interprets the empty string as null, and if this function has a null argument, it returns null.
Beispiel:

The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012...9' are translated to '9': 



SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence"
	FROM DUAL

Translate example
-----------------
9XXX999 


The following statement returns a license number with the characters removed and the digits remaining: 

SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
"Translate example" FROM DUAL

Translate example
-----------------
2229

Seitenanfang
TRUNC
Syntax:
TRUNC(n[,m]); TRUNC(d,[fmt])
Erklärung:
Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point. Format 2:Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See the next section "ROUND and TRUNC."
Beispiel:

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL

  Truncate
----------
      15.7

SELECT TRUNC(TO_DATE('27-OCT-92', 'DD-MON-YY'), 'YEAR') "First Of The Year"
	FROM DUAL

FIRST OF THE YEAR
-----------------
01-JAN-92

Seitenanfang
UID
Syntax:
UID
Erklärung:
Returns an integer that uniquely identifies the current user.
Beispiel:

- kein beispiel vorhanden -

Seitenanfang
UPPER
Syntax:
UPPER(char)
Erklärung:
Returns char, with all letters uppercase. The return value has the same datatype as the argument char.
Beispiel:

SELECT UPPER('Large') "Uppercase" FROM DUAL

Uppercase
---------
LARGE

Seitenanfang
USER
Syntax:
USER
Erklärung:
Returns the current Oracle7 user with the datatype VARCHAR2. Oracle7 compares values of this function with blank-padded comparison semantics. 
In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint
Beispiel:

SELECT USER, UID
	FROM DUAL

USER                                  UID
------------------------------ ----------
OPS$BQUIGLEY                           46

Seitenanfang
USERENV
Syntax:
USERENV(option)
Erklärung:
Returns information of VARCHAR2 datatype about the current session. This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session. You cannot use USERENV in the condition of a CHECK constraint. The argument option can have any of these values: 

'OSDBA' returns 'TRUE' if you currently have the OSDBA role enabled and 'FALSE' if you do not. 

'LABEL' returns your current session label. This option is only applicable for Trusted Oracle7. For more information on this option, see Trusted Oracle7 Server Administrator's Guide. 

'LANGUAGE' returns the language and territory currently used by your session along with the database character set in this form: language_territory.characterset

'TERMINAL' returns the operating system identifier for your current session's terminal. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECTs, not for remote INSERTs, UPDATEs, or DELETEs.

'SESSIONID' returns your auditing session identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to TRUE. 

'ENTRYID' returns available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to TRUE. 

'CLIENT_INFO' Returns the value of the client_info field of the current session, as the last value set by the dbms_application_info.set_client_info procedure. 

'LANG' Returns the ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
Beispiel:

SELECT USERENV('LANGUAGE') "Language"
	FROM DUAL

Language
----------------------------------------------------
AMERICAN_AMERICA.WE8DEC

Seitenanfang
VARIANCE
Syntax:
VARIANCE([DISTINCT|ALL]x)
Erklärung:
Returns variance of x, a number. Oracle7 calculates the variance of x using this formula: 
where:  xi is one of the elements of x. 
n is the number of elements in the set x. If n is 1, the variance is defined to be 0.
Beispiel:

SELECT VARIANCE(sal) "Variance"
	FROM emp

Variance
----------
1389313.87

Seitenanfang
VSIZE
Syntax:
VSIZE(expr)
Erklärung:
Returns the number of bytes in the internal representation of expr. If expr is null, this function returns null.
Beispiel:

SELECT ename, VSIZE(ename) "BYTES"
	FROM emp
	WHERE deptno = 10

ENAME          BYTES
---------- ---------
CLARK              5
KING               4
MILLER             6

Seitenanfang
Stand 05.06.2005