Oracle's most common function function classic summary Source: chinaitlab Collection 2004-6-14 10:58:00
* SQL Group Function * S (Num Can Be a Column or Expression)
(Null Values Are Ign * Ored, Default Between Distinct and All IS All)
************************ ******** ************************* ****************************
AVG ([DISTINCT or ALL] NUM - AVERAGE VALUE
Count (Distinct or All] Num) - Number of Values
Max ([Distinct or All] Num) - Maximum Value
Max ([Distinct or All] Num) - Minimum Value
STDDEV ([Distinct or All] Num) - Standard Deviation
SUM ([Distinct or All] Num) - Sum of Values
Variance ([Distinct or All] Num) - Variance of Values - VARIANCE OF VALUES
*********************************************************** *****************************
* MiscellaneAous functions: *
************************ ******** ************************* ****************************
Decode (expr, srch1, return1 [, srch2, return2 ...], default]
- if no search matches the expression of the default is returned,
- OtherWise, The First Search That Matches Will Cause
- The Corresponding Return Value to Be Returned
DUMP (Column_name [, FMT [, start_pos [, length]]])
- Returns An Column Info About A of Returns An Column Info About A
- Format Options: 8 = Octal, 10 = Decimel, 16 = HEX, 17 = CHARACTERS
- Return Typecodes: 1 = varchar2, 2 = Number, 8 = long, 12 = DATE,
- 23 = RAW, 24 = long Raw, 69 = ROWID, 96 = Char, 106 = mlslabel
Greatest (expr [, expr2 [, expr3 ...]]
- Returns The Largest Value of All Expressions
Least (expr [, expr2 [, expr3 ...]]
- Returns themallest value of all expressions
NVL (expr1, expr2
- IF EXPR1 IS NOT NULL, IT IS RETURNED, OTHERWISE EXPR2 IS RETURNED
Sqlcode
- Returns SQL ERROR CODE Query, of Last Error. CAN NOT BE Used Directly in - Value Must Be Set To Local Variable First
SQlerrm
- Returns Sqlin Query, Error Message of Last Error. CAN NOT BE Used Directly
- Value Must Be Set To Local Variable First
UID
- Returns the user id of the user you are logged on as
- Useful in Selecting Information from Low Level Sys Tables
User
- Returns theuser name of the user you are logged on as
Userenv ('Option')
- Returns Information About The User You Are Logged On AS
- Options: Entryid, Sessionid, Terminal, Language, Label, OSDBA
- (All Options Not Available INALL ORACLE VERSIONS)
VSIZE (EXPR)
- Returns the number of bytes buy by the expression
- Useful in Selecting Information About Table Space Requirements
************************ ******** ************************* ****************************
* SQL DATE FUNCTIONS (DT Represe * NTS Oracle Date and Time)
* (functions return * an oracle date unless Otherwise specified)
*********************************************************** *****************************
Add_MONTHS (DT, NUM) - Adds Num Months to DT (Num Can Be NEGATIVE)
Last_day (DT) - Last Day of Month Inmonth Containing DT
MONTHS_BETWEEN (DT1, DT2) - RETUDT2 RNS FRACTIONAL VALUE OF MONTHS BETWEEN DT1,
New_Time (DT, TZ1, TZZONE 2 2) - DT = DATE IN TIME ZONE 1, RETURns DATE IN TIME
Next_day (dt, str) - datetc ..) OF FIRST (STR) After DT (Str = 'Monday',
Sysdate - Present System Date
Round (DT [, FMT] - ROUNDS DT As Specified by Format FMT
Trunc (DT [, FMT] - Truncates DT As Specified by Format FMT
*********************************************************** *****************************
* Number functions: *
*********************************************************** ***************************** ABS (NUM) - ABSOLUTEVALUE OF NUM
CEIL (NUM) - Smallst Integer> OR = NUM
COS (NUM) - Cosine (NUM), Num in Radians
Cosh (NUM) - Hyperbolic Cosine (NUM)
Exp (Num) - E Raised to the Num Power
FLOOR (NUM) - Largest Integer LN (Num) - Natural Logarithm of Num LOG (Num2, Num1) - Logarithm Base Num2 of Num1 MOD (Num2, Num1) - Remainder of Num2 / Num1 Power (Num2, Num1) - Num2 raised to the Num1Power Round (Num1 [, Num2] - Num1 Rounded to Num2 Decimel Places (Default 0) Sign (NUM) - SIGN OF Num * 1, 0 if Num = 0 Sin (Num) - SIN (NUM), NUM IN RADIANS SINH (NUM) - Hyperbolic Sine (NUM) SQRT (NUM) - Square Root of Num Tan (Num) - Tangent (NUM), Num in Radians Tanh (NUM) - Hyperbolic Tangent (NUM) Trunc (Num1 [, Num2] - Truncatenum1 to Num2 Decimel Places (Default 0) *********************************************************** ***************************** * String functions, * String Result: *********************************************************** ***************************** (NUM) - ASCIICHARACTER for Num CHR (NUM) - ASCII Character for Num Concat (str1, str2) - str1 controlnated with str2 (Same as str1 || str2) INITCAP (STR) - Capitalize First Letter of Each Word in Str Lower (STR) - STR with all letters in LowerCase LPAD (str1, num [, str2]) - Left spaces) Pad str1 to length num with str2 (Default LTRIM (Str [, set]) - Remove Set from Leftside of Str (Default Spaces) NLS_INITCAP (STR [, NLS_VAL]) - Same as INITCAP for Different Languages NLS_LOWER (STR [, NLS_VAL]) - Same As Lower FordIfferent Languages Replace (str1, str2 [, str3]) - Replaces Str2 with str3 in str1-- deletes str2 from str1 if str3 is omitted RPAD (STR1, NUM [, STR (Default Spaces) 2]) - Right Pad Str1 ToLength Num with str2 RTRIM (STR [, SET]) Spaces - Remove Set from Right Side of Str (Default Soundex (STR) - Phonetic Representation OF STR Substr (Str, Num2 [, Num1]) - Substring of Str, Starting with Num2, - omitted) Num1 Characters (to end of striff " Substrb (Str, Num2 [, Bytes Num1]) - Same as Substr But Num1, Num2 Expressed in Translate (STR, SET1, SET2) - Replace Set1 in Str with set2 - Truncated if set2 is for than set1, IT Will B Upper (str) - str with all letters in Uppercase ************************ ******** ************************* **************************** * String functions, * Numeric Result: *********************************************************** ***************************** ASCII (STR) - ASCII Value of Str INSTR (str1, str2 [, num1 [, num2]]) - Position of Num2th Occurrence of - Str2 in str1, start at num1 - (Num1, Num2 Default to 1) INSTRB (STR1, STR2 [, Num1 [Num2]]) - Same as INSTR, BYTE VALUES for NUM1, NUM2 Length (STR) - Number of Characters in Str Lengthb (str) - Number of Bytes in Str NLSSORT (STR [, NLS_VAL]) - NLS_VAL BYTE VALUE OF STR *********************************************************** ***************************** * SQL Conversion functions * *********************************************************** ***************************** Chartorowid (STR) - Converts Str To RowId Convert (str, chr_set2 [, chr_set1]) - Converts str to chr_set2 Character Set - Chr_set1 Default Is The Datbase HEXTORAW (STR) - Converts Hex String Value To Internal Raw ValueSrawToHex (Raw_VAL) - Converts Raw Hex Value to Hex String Value Rowidtochar (RowID) - Converts Rowid to 18 Character String Format TO_CHAR (expr [, fmt]) FMT - Converts expr (date or number) to format specified by TO_DATE (STR [, FMT]) - Converts String to Date TO_MULTI_BYTE (STR) - Converts Single Byte String To Multi Byte String TO_NUMBER (STR [, FMT]) - Converts str to a number formatted by FMT TO_SINGLE_BYTE (STR) - Converts Multi Byte String To Single Byte String *********************************************************** ***************************** * SQL DATE FORMATS * ************************ ******** ************************* **************************** BC, B.C. BC Indicator AD, A.D. AD Indicator CC, SCC CENTURY CODE (SCC Includes Spaceor - SIGN) YYYY, SYYYY 4 DIGIT YEAR (Syyyy Includes Space Or - SIGN) Iyyy 4 Digit Iso Year Y, YYY 4 Digit Year with Comma YYY, YY, OR Y Last 3, 2, or 1 Digit of Year Year, Syear Year Spelled Out (Syear Includes Space Or - Sign) RR Last 2 Digits Ofyear in Prior or Next Century Q Quarter or Year, 1 to 4 MM MONTH - FROM 01 TO 12 Month Month Spelled Out Mon Month 3 Letter Abbreviation RM Roman Numeral for Month WW Week of Year, 1 to 53 IW iso week of year, 1 to 52 or 1 to 53 W Week of Month, 1TO 5 (Week 1 Begins 1st Day of the Month) D day of week, 1 to 7 DD days of MONTH, 1 to 31 DDD day of year, 1 to 366 Day day of week spelled out, nine characters right padded Dy day abbreviation J # OFDAYS SINCE JAN 1, 4712 BC HH, HH12 HOUR of Day, 1 to 12 HH24 HOUR of Day, 0 to 23 Mi Minute of Hour, 0 to 59ss Second of Minute, 0 to 59 SSSSS Seconds Past Midnight, 0 to 86399 AM, A.M. AM Indicator PM, P.M. PM Indicator Any Puctuation Punctuation Between Format Items, AS IN 'DD / MM / YY' Any Text TEXT BETWEEN FORMAT ITEMS TH Converts 1 to '1st', 2 to '2nd', Ando ON SP Converts 1 To 'One', 2 to 'Two', Ando ON SPTH Converts 1 To 'First', 2 to 'Second', Ando ON FX Fillexact: Uses Exact Pattern mathing FM Fill Mode: Toggles Suppression Of Blanks in Output
