Oracle的常用函数
来源:互联网 发布:dede织梦wap手机模板 编辑:程序博客网 时间:2024/04/30 23:06
Oracle的常用函数
1、字符函数
lower:把字符转成小写
upper:把字符转成大写
initcap:把单词的第一个字母变成大写
concat:连接字符 concat(‘good’,’morning’)=goodmoring
SUBSTR (columnexpression, m[,n]) 用于对字符串进行截取操作,从第m 个位置开始,把其后的连续n个字符的部分截取下来,如果m位负值,则从末尾开始计算。
eg:substr(‘string’,1,3) =str
substr(‘string’-3,3)=ing
INSTR('String', 'r')=3
LPAD(sal,10,'*') =*******sal RPAD()
length: 用于返回表达式中的字符数,注意返回的是NUMBER。
NVL(expression1, expression2)
NVL 函数用以把一个空值转换为一个实值,如:
NVL(100/quantity, 0) ,要是quantity 为空值,该函数返回一个0。
2、数学函数
round:四舍五入
round(2.566,2)=2.27
round(45,-1)=50
trunc: 截断
trunc(2.566,2)=2.56
trunc(45,-1)=40
mod: mod(m,n)
floor(m/n),ceil(m/n)
3、日期函数:
a) months_between(date1,date2):算date1 和date2 之间的月的数量,可以是小数可以是负数
months_between(’01-sep-95’,’11-jan-94’)=1.9774194
b) add_months(date,n):为date 加上N 个月,N 只可以是整数
c) next_date(date,’char’):查找date 的下一个星期Nnext_date(’01-sep-95’,’FRIDAY’)=08-SEP-95
d) last_day(date):查找date 月的最后一天。
e) round(date):把日期四舍五入
f) round(25-MAY-95’,’MONTH’)=01-JUN-95
g) round(25-MAY-95’,’YEAR’)=01-JAN-95
h) trunc(date):把日期截断
i) trunc (25-MAY-95’,’MONTH’)=01-MAY-95
j) trunc (25-MAY-95’,’YEAR’)=01-JAN-95
4、转换函数
TO_CHAR:
TO_CHAR(date,’fmt’):fm前缀用来去除首尾的空字符或0
TO_CHAR(total,’fm$999999’)
如果想转成$0.25,那就要写成fm$9999990.99
to_char(1234,’09999’)
可以把日期转换成字符
TO_CHAR(log_time,’MM/YY’)
SELECT to_char(SYSDATE,'yyyy"年"mm"月"dd"日"') FROM dual
具体格式如下
Number Format Elements Results of Number Conversionshttp://xsb.itpub.net/post/419/31722
, (comma)
9,999
Returns a comma in the specified position. You can specify multiple commas in a number format model.
Restrictions:
A comma element cannot begin a number format model.
A comma cannot appear to the right of a decimal character or period in a number format model.
. (period)
99.99
Returns a decimal point, which is a period (.) in the specified position.
Restriction: You can specify only one period in a number format model.
$
$9999
Returns value with a leading dollar sign.
0
0999
9990
Returns leading zeros.
Returns trailing zeros.
9
9999
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.
Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B
B9999
Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C
C999
Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY
parameter).
D
99D99
Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER
parameter. The default is a period (.).
Restriction: You can specify only one decimal character in a number format model.
EEEE
9.9EEEE
Returns a value using in scientific notation.
G
9G999
Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER
parameter). You can specify multiple group separators in a number format model.
Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L
L999
Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY
parameter).
MI
9999MI
Returns negative value with a trailing minus sign (-).
Returns positive value with a trailing blank.
Restriction: The MI format element can appear only in the last position of a number format model.
PR
9999PR
Returns negative value in <angle brackets>.
Returns positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.
RN
rn
RN
rn
Returns a value as Roman numerals in uppercase.
Returns a value as Roman numerals in lowercase.
Value can be an integer between 1 and 3999.
S
S9999
9999S
Returns negative value with a leading minus sign (-).
Returns positive value with a leading plus sign (+).
Returns negative value with a trailing minus sign (-).
Returns positive value with a trailing plus sign (+).
Restriction: The S format element can appear only in the first or last position of a number format model.
TM
TM
The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive.
The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.
Restrictions:
You cannot precede this element with any other element.
You can follow this element only with one 9 or one E (or e), but not with any combination of these. The following statement returns an error:
SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;
U
U9999
Returns in the specified position the Euro (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY
parameter).
V
999V99
Returns a value multiplied by 10n (and if necessary, round it up), where n
is the number of 9's after the V
.
X
XXXX
xxxx
Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.
Restrictions:
This element accepts only positive values or 0. Negative values return an error.
You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has 1 leading blank.
shows the results of the following query for different values of number
and 'fmt'
:
SELECT TO_CHAR(number, 'fmt')
FROM DUAL;
-1234567890
9999999999S
'1234567890-'
0
99.99
' .00'
+0.1
99.99
' .10'
-0.2
99.99
' -.20'
0
90.99
' 0.00'
+0.1
90.99
' 0.10'
-0.2
90.99
' -0.20'
0
9999
' 0'
1
9999
' 1'
0
B9999
' '
1
B9999
' 1'
0
B90.99
' '
+123.456
999.999
' 123.456'
-123.456
999.999
'-123.456'
+123.456
FM999.009
'123.456'
+123.456
9.9EEEE
' 1.2E+02'
+1E+123
9.9EEEE
' 1.0E+123'
+123.456
FM9.9EEEE
'1.2E+02'
+123.45
FM999.009
'123.45'
+123.0
FM999.009
'123.00'
+123.45
L999.99
' $123.45'
+123.45
FML999.99
'$123.45'
+1234567890
9999999999S
'1234567890+'
Datetime Format Elements
-/,.;:"text"
Yes
Punctuation and quoted text is reproduced in the result.
ADA.D.
Yes
AD indicator with or without periods.
AMA.M.
Yes
Meridian indicator with or without periods.
BCB.C.
Yes
BC indicator with or without periods.
CCSCC
No
Century.
If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.
For example, 2002 returns 21; 2000 returns 20.
D
Yes
Day of week (1-7).
DAY
Yes
Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.
DD
Yes
Day of month (1-31).
DDD
Yes
Day of year (1-366).
DL
Yes
Returns a value in the long date format, which is an extension of Oracle Database's DATE
format (the current value of the NLS_DATE_FORMAT
parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY
and NLS_LANGUAGE
parameters. For example, in theAMERICAN_AMERICA
locale, this is equivalent to specifying the format 'fmDay,
Month
dd,
yyyy'
. In the GERMAN_GERMANY
locale, it is equivalent to specifying the format 'fmDay, dd.
Month yyyy
'.
Restriction: You can specify this format only with the TS
element, separated by white space.
DS
Yes
Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY
andNLS_LANGUAGE
parameters. For example, in the AMERICAN_AMERICA
locale, this is equivalent to specifying the format 'MM/DD/RRRR
'. In the ENGLISH_UNITED_KINGDOM
locale, it is equivalent to specifying the format 'DD/MM/RRRR
'.
Restriction: You can specify this format only with the TS
element, separated by white space.
DY
Yes
Abbreviated name of day.
E
No
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
No
Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes
Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime datatype or the datatype's default precision.
Examples: 'HH:MI:SS.FF'
SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
FM
Yes
Returns a value with no leading or trailing blanks.
See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference
FX
Yes
Requires exact matching between the character data and the format model.
See Also: Additional discussion on this format model modifier in the Oracle Database SQL Reference
HH
Yes
Hour of day (1-12).
HH12
No
Hour of day (1-12).
HH24
Yes
Hour of day (0-23).
IW
No
Week of year (1-52 or 1-53) based on the ISO standard.
IYYIYI
No
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
No
4-digit year based on the ISO standard.
J
Yes
Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes
Minute (0-59).
MM
Yes
Month (01-12; January = 01).
MON
Yes
Abbreviated name of month.
MONTH
Yes
Name of month, padded with blanks to display width of the widest name of month in the date language used for this element.
PMP.M.
No
Meridian indicator with or without periods.
Q
No
Quarter of year (1, 2, 3, 4; January - March = 1).
RM
Yes
Roman numeral month (I-XII; January = I).
RR
Yes
Lets you store 20th century dates in the 21st century using only two digits.
See Also: Additional discussion on RR
datetime format element in the Oracle Database SQL Reference
RRRR
Yes
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes
Second (0-59).
SSSSS
Yes
Seconds past midnight (0-86399).
TS
Yes
Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY
and NLS_LANGUAGE
initialization parameters.
Restriction: You can specify this format only with the DL
or DS
element, separated by white space.
TZD
Yes
Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR.
Example: PST
(for US/Pacific standard time); PDT
(for US/Pacific daylight time).
TZH
Yes
Time zone hour. (See TZM
format element.)
Example: 'HH:MI:SS.FFTZH:TZM'
.
TZM
Yes
Time zone minute. (See TZH
format element.)
Example: 'HH:MI:SS.FFTZH:TZM'
.
TZR
Yes
Time zone region information. The value must be one of the time zone regions supported in the database.
Example: US/Pacific
WW
No
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W
No
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X
Yes
Local radix character.
Example: 'HH:MI:SSXFF'
.
Y,YYY
Yes
Year with comma in this position.
YEARSYEAR
No
Year, spelled out; S
prefixes BC dates with a minus sign (-).
YYYYSYYYY
Yes
4-digit year; S
prefixes BC dates with a minus sign.
YYYYYY
Yes
Last 3, 2, or 1 digit(s) of year.
- oracle的常用函数
- Oracle常用的函数
- Oracle的常用函数
- oracle 常用的函数
- Oracle常用的函数
- Oracle 常用的函数
- Oracle 常用的函数
- oracle的常用函数
- oracle 常用的函数
- oracle的常用函数
- Oracle 常用的函数
- Oracle常用的函数
- oracle常用的函数
- 常用的Oracle函数查询
- Oracle常用的OCI函数
- Oracle常用的OCI函数
- Oracle 常用的字符函数
- Oracle常用的OCI函数
- [如何做Inventor插件和做插件的安装程序]网络培训
- 想法
- Java学习路线图,专为新手定制的Java学习计划建议
- 获得文件夹下文件名列表的方法
- OpenCasCade 简介
- Oracle的常用函数
- [转]speed tree技术分析
- 利用ssh在linux系统之间在局域网内传输文件
- MYSQL删除某个字段多余重复记录
- 分享多年美工工作收集的小工具和经验 [转]
- C/C++头文件一览
- struts 2 BaseAction
- java中==与equals的区别
- 关于Light Box被FLASH遮住的问题