oracle常用功能函数汇总.doc

oracle 常用功能函数汇总* * SQL Group Functions (num can be a column or expression)* (null values are ignored, 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 * * Miscellaneaous Functions :* * DECODE(expr, srch1, return1 ,srch2, return2., default - if no search matches the expression then 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 internal oracle format, used for getting info about a column - format options : 8 = octal, 10 = decimel, 16 = hex, 17 = characters - return type codes : 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 the smallest value of all expressions NVL(expr1 ,expr2 - if expr1 is not null, it is returned, otherwise expr2 is returned SQLCODE - returns sql error code of last error.Can not be used directly in query, - value must be set to local variable first SQLERRM - returns sql error message of last error.Can not be used directly in query, - 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 the user 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 in all Oracle versions) VSIZE(expr) - returns the number of bytes used by the expression - useful in selecting information about table space requirements * * SQL Date Functions (dt represents 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 in month containing dt MONTHS_BETWEEN(dt1, dt2) - returns fractional value of months between dt1, dt2 NEW_TIME(dt, tz1, tz2) - dt = date in time zone 1, returns date in time zone 2 NEXT_DAY(dt, str) - date of first (str) after dt (str = Monday, etc.) 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) - absolute value of num CEIL(num) - smallest 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 or = num 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 num1 power 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 - truncate num1 to num2 decimel places (default 0) * * String Functions, String Result :* * (num) - ASCII character for num CHR(num) - ASCII character for num CONCAT(str1, str2) - str1 concatenated 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 pad str1 to length num with str2 (default spaces) LTRIM(str ,set) - remove set from left side of str (default spaces) NLS_INITCAP(str ,nls_val) - same as initcap for different languages NLS_LOWER(str ,nls_val) - same as lower for different languages REPLACE(str1, str2 ,str3) - replaces str2 with str3 in str1 - deletes str2 from str1 if str3 is omitted RPAD(str1, num ,str2) - right pad str1 to length num with str2 (default spaces) RTRIM(str ,set) - remove set from right side of str (default spaces) SOUNDEX(str) - phonetic representation of str SUBSTR(str, num2 ,num1) - substring of str, starting with num2, - num1 characters (to end of str if num1 is omitted) SUBSTRB(str, num2 ,num1)- same as substr but num1, num2 expressed in bytes TRANSLATE(str, set1, set2)- replaces set1 in str with set2 - if set2 is longer than set1, it will be truncated 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, starting 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 - chr_set1 default is the datbase character set HEXTORAW(str) - converts hex string value to internal raw values RAWTOHEX(raw_val) - converts raw hex value to hex string value ROWIDTOCHAR(rowid) - converts rowid to 18 character string format TO_CHAR(expr ,fmt)- converts expr(date or number) to format specified by fmt 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 space or - 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 of year 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, 1 to 5 (week 1 begins 1st day of the month) D