- 浏览: 108445 次
- 性别:
- 来自: 武汉
文章分类
最新评论
-
ldwtill:
overwrite呢?
overload,overwrite和override比较 -
dafeiwang:
...
过滤器和拦截器的区别
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 day of week, 1 to 7
DD day 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 # of days 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 59
SS 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', and so on
SP converts 1 to 'one', 2 to 'two', and so on
SPTH converts 1 to 'FIRST', 2 to 'SECOND', and so on
FX fill exact : uses exact pattern matching
FM fill mode : toggles suppression of blanks in output
发表评论
-
oracle 110个常用的函数
2011-09-16 18:41 811110个Oracle常用函数整理 ... -
比较实用的sql总结
2011-09-01 10:00 809//可以用来查询字段为空或者为null 的字段 1.sele ... -
where 1=1 and where 1=0
2011-08-30 10:31 919where 1=1有什么用?在SQL语言中,写这么一句话就跟没 ... -
sql/plus是使用
2011-05-19 20:43 968(1)显示SQL缓冲区的命令 使用LIST命令可以显示当前S ... -
char、nchar、varchar、nvarchar的区别
2011-05-07 21:58 926一 、 nchar和nvarchar ... -
sql在不同数据库查询前几条数据
2011-05-02 19:44 966sql在不同数据库查询前几条数据 1. ORACLE SE ... -
SQL 查询效率(有点长,如耐心看完,对你会有帮助)
2011-05-02 09:38 869很多人不知道SQL语句 ... -
Oracle imp命令导入数据到指定表空间
2011-05-01 21:09 1141找了一个几百万行数据的库,准备导入的本地Oracle中: im ... -
oracle数据导入导出
2011-05-01 21:05 656Oracle数据导入导出imp/e ... -
Oracle 通过触发器实现主键自动增长
2011-05-01 21:03 10391,创建sequence: create sequenc ...
相关推荐
Oracle常用函数大全,每个函数都列举有例子,包括它的详细使用方法说明。
oracle 常用函数下载,工作中经常用到
oracle常用函数总结,跟大家分享一下
适合开发人员,内含Oracle常用函数,以及语句实例!
一些Oracle常用函数,希望对初学者有帮助
Oracle常用函数.pdf 看名知意 无需多说
oracle常用函数总结 oracle常用函数总结
ORACLE常用函数总结,与SQL SERVER对比,防止混淆,更容易记忆。
oracle常用函数oracle常用函数oracle常用函数oracle常用函数oracle常用函数oracle常用函数oracle常用函数
sql,mysql,oracle常用的函数
80个oracle常用函数总结
110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个oracle常用函数总结 110个...
SQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txtSQLServer和Oracle常用函数对比.txt
3.CONCAT:连接两个字符串; SQL> select concat('010-','88888888')||'转23' 张三电话 from dual; 张三电话 ...SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING ---------
oracle常用函数介绍
SQLServer和Oracle常用函数对比SQLServer和Oracle常用函数对比
Oracle创建函数是通过PL/SQL自定义编写的,通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中,函数提供一个返回值,返回给使用者。这样使用者就不需要去理解业务逻辑,把PL/SQL函数中的业务逻辑...
oracle 常用函数大全 ppt文档,包括字符串处理,日期处理 ,数据的处理