-
oralce函数大全
- ABS
- ACOS
- ADD_MONTHS
- Ascii
- ASIN
- ATAN
- ATAN2
- AVG
- BFILENAMEdirfile
- CEIL
- CHARTOROWID
- CHR
- CONCAT
- CONVERTcdsetsset
- COS
- COSH
- COUNT
- CURRENT_DATE
- CURRENT_TIMESTAMP
- DBTIMEZONE
- DECODE
- DEREF
- DUMPsfmtstartlength
- EMPTY_BLOB和EMPTY_CLOB
- EXP
- EXTRACT
- FLOOR
- FROM_TZ
- GREATEST
- GROUPING
- HEXTORAW
- INITCAP
- INSTRC1C2IJ
- INSTRB
- LAST_DAY
- LEAST
- LENGTH
- LENGTHB
- LN
- LOCALTIMESTAMP
- LOGn1n2
- LOWER
- LPAD粘贴字符
- LTRIM
- MAKE_REF
- MAX
- MIN
- MODn1n2
- MONTHS_BETWEENdate2date1
- NEW_TIMEdatethisthat
- NEXT_DAYdateday
- NEW_TIME
- NLSSORT
- NLS_CHARSET_DECL_LEN
- NLS_CHARSET_ID
- NLS_CHARSET_NAME
- NLS_INICAP
- NLS_LOWER
- NLS_UPPER
- NVL
- POWER
- RAWTOHEX
- REF
- REFTOHEX
- REPLACEstrings1s2
- ROUND
- ROWID
- ROWIDTOCHAR
- RPAD 粘贴字符
- RTRIM
- SESSIONTIMEZONE
- SIGH
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTRstringstartcount
- SUBSTRB
- SUM
- SYS_EXTRACT_UTC
- SYSDATE
- SYS_CONTEXT
- SYS_GUID
- SYSTIMESTAMP
- TAN
- TANH
- TO_CHARdateformat
- TO_DATEstringformat
- TO_LOB
- TO_MULTI_BYTE
- TO_NUMBER
- TO_SINGLE_BYTE
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_YMINTERVAL
- TRANSLATExyz
- TRIMkeyword s from string
- TRUNC
- TZ_OFFSET
- UID
- UPPER
- USER
- USEREVN
- UID
- VSIZE
- VARIANCE
- 常用技巧
- 常用数据词典
oralce函数大全
ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
ADD_MONTHS
ADD_MONTHS(,＜i＞)，增加或减去月份，返回日期d加上i个月后的结果。i可以使任意整数。如果i是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') fromdual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') fromdual;
TO_CHA
------
199910
Ascii
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space fromdual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
逆函数是CHR()
ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
ATAN2
Atan2(n,m)：该函数用于返回数字n除以数字m的反正切值。输入值除了m不能为0外，可以是任意数字(m不能为0)，输出值的单位为弧度。
AVG
返回数值的平均值。缺省设置为ALL
BFILENAME(dir,file)
指定一个外部二进制文件，dir是一个directory类型的对象，file为一文件名。函数返回一个空的BFILE位置值指示符，函数用于初始化BFILE变量或者是BFILE列。
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
CHARTOROWID
函数将c转换为RWID数据类型
SELECTtest_id from test_case where rowid=CHARTORWID(''AAAA0SAACAAAALiAAA'')
CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 fromdual;
ZH C
-- -
赵 A
CONCAT
连接两个字符串;
SQL>select concat('010-','88888888')||'转23' 高乾竞电话from dual
高乾竞电话
----------------
010-88888888转23
CONVERT(c,dset,sset)
将源字符串 sset的字符从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" fromdual;
conver
------
strutz
COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
COSH
返回n的双曲余玄值，n 为数字。
select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
COUNT
COUNT({*|DISTINCT|ALL})
返回查询中行的数目，缺省设置是ALL,*表示返回所有的行
select area_id,min(area_description),count(area_id)from code_city
group by area_id
order by count(*) desc
SQL> select count(null) from emp;
COUNT(NULL)
-----------
0
SQL> select count('') from emp;
COUNT('')
----------
0
SQL> select count('1') from emp;
COUNT('1')
----------
7
如果参数填上某个列，count会统计select返回结果集中这些列不为空的这些行。
Select count(*) ---slow
Count(1),--fast
Count(rowid)—fast
From emp;
CURRENT_DATE
该函数用于返回当前会话时区的日期时间。
CURRENT_TIMESTAMP
该函数用于返回当前会话时区的日期时间。
DBTIMEZONE
该函数用于返回数据库所在的时区。
DECODE
x是一个表达式，m1是一个匹配表达式，x与m1比较，如果m1等于x，那么返回r1,否则,x与m2比较，依次类推m3,m4,m5....直到有返回结果。
DEREF
DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string fromglobal_name;
GLOBAL_NAME DUMP_STRING
--------------------------------------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
EXTRACT
该函数用于从日期时间中取得所需的特定数据（年份，月份和天）
Sql>select extract(year from sysdate)from dual;
FLOOR
返回小于或等于数字n的最大整数。
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
FROM_TZ
该函数用于将特定时区的timestamp值转变为timestamp with time zone值。
SQL>select from_tz(timestamp'2003-03-2808:00:00','3:00') from dual;
28-3月 -03 08.00.00.000000000 上午 +03:00
GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
GROUPING
HEXTORAW
将一个十六进制构成的字符串转换为二进制
INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL>select initcap('smith') upp from dual;
UPP
-----
Smith
INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2)instring from dual;
INSTRING
---------
9
INSTRB
与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()
LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月-04
LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
LENGTH
返回字符串的长度;
SQL>select name,length(name),addr,length(addr),sal,length(to_char(sal)) fromgao.nchar_tst;
NAME LENGTH(NAME)ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ -----------------------------
高乾竞 3 北京市海锭区 6 9999.99 7
LENGTHB
与LENGTH()一样，返回字节
LN
返回一个数字的自然对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
LOCALTIMESTAMP
Fmt：该函数用于返回当前会话时区的日期时间。
LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')fromdual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
LTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ')from dual;
LTRIM(RTRIM('
-------------
gao qian jing
MAKE_REF
MAX
MIN
MOD(n1,n2)
返回一个n1除以n2的余数，如果数字n为0，则返回结果为m
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
MONTHS_BETWEEN(date2,date1)
返回date1和date2之间月的数目,如果date1和date2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999')mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))mon_betw from dual;
MON_BETW
---------
-60
NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss')bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles fromdual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月-01
NEW_TIME
FMT：new_time(date,zone1,zone2)该函数用于返回时区一的日期时间所对应的时区二的日期时间。
SQL>select to_char(new_time(to_date('20050725 14:34:50','yyyymmdd hh24:mi:ss'),
'AST','GMT'),'yyyymmddhh24:mi:ss') from dual;
NLSSORT
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLS_INICAP
Nls_inicap(char,’nls_param’)：其中char用于指定nchar或nvarchar2类型的字符串，其前面加上n，用单引号括起来，nls_param的格式为‘nls_sort=sort’，用于指定特定语言特征。
Declare
V_nls_initcap nChar(10);
Begin
V_nls_initcap:=nls_initcap(n’myword’);
End;
NLS_LOWER
NLS_UPPER
NVL
Nvl(n1,n2) 如果n1为null返回n2,否则返回n1
POWER
返回n1的n2次方根。
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
RAWTOHEX
将一个二进制构成的字符串转换为十六进制
REF
REFTOHEX
REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') fromdual;
REPLACE('H
----------
i love you
ROUND
将日期d按照fmt指定的格式舍入，fmt为字符串。
Round(n,[m])
如果m是负数，则四舍五入到小数点前m位，如果m是正数，则四舍五入到小数点后m位。
SQL>select round(12.34,'-1') from dual;
10
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
ROWID
ROWIDTOCHAR
将ROWID数据类型转换为字符类型
select rowidtochar(rowid),a.* fromcode_opponent a ;
RPAD (粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')fromdual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ')from dual;
LTRIM(RTRIM('
-------------
gao qian jing
SESSIONTIMEZONE
该函数返回当前会话所在的时区。
SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
SINH
返回n的双曲正玄值,n为弧度。
SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 wheresoundex(xm)=soundex('weather');
XM
--------
weather
wether
SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
STDDEV
返回选者的列表项目的标准差，所谓标准差是方差的平方根
SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
如果m是负数，则从尾部开始。
Sql>select substr('Morning',-3,2) fromdual;
SUBSTR('MORNING',-3,2)
----------------------
in
如果m是负数，超过字符串的最大长度，则返回null。
SUBSTRB
与SUBSTR大致相同，只是I,J是以字节计算。
SUM
SUM([{DISTINCT|ALL}])返回选择列表项目的数值的总和。
SYS_EXTRACT_UTC
Fmt：sys_extract_utc(datatimestamp_with_timezone)：该函数用于返回特定时区时间所对应的格林威治时间。
SYSDATE
用来得到系统的当前日期时间
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> selectto_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm fromdual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
SYS_CONTEXT
Sys_context(‘context’,’attribute’)
该函数用于返回应用上下文的特定属性值，其中context为应用上下文名，而attribute则用于指定属性名。示例如下：
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
SYS_GUID
SYSTIMESTAMP
该函数用于返回当前系统的日期时间及时区。
TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
TANH
返回数字n的双曲正切值,n为弧度
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
TO_CHAR(date,'format')
x是一个date或number数据类型，函数将x转换成fmt指定格式的char数据类型，
如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。
如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
SQL> select to_char(sysdate,'yyyy/mm/ddhh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
特殊格式的日期型数据
表7-10 常用日期数据格式
格式返回值样例显示
1． Y或YY或YYY 年的最后一位，两位或三位 Select to_char(sysdate,’YYY’) from dual； 002表示2002年
2． SYEAR或YEAR SYEAR使公元前的年份前加一负号 Select to_char(sysdate,’SYEAR’) from dual； -1112表示公元前111 2年
3． Q 季度，1～3月为第一季度 Select to_char(sysdate,’Q’)from dual； 2表示第二季度①
4． MM 月份数 Selectto_char(sysdate,’MM’) from dual； 12表示12月
5． RM 月份的罗马表示 Selectto_char(sysdate,’RM’) from dual； IV表示4月
6． Month 用9个字符长度表示的月份名 Select to_char(sysdate,’Month’) from dual； 12
7． WW 当年第几周 Selectto_char(sysdate,’WW’) from dual； 24表示2002年6月13日为第24周
8． W 本月第几周 Selectto_char(sysdate,’W’) from dual； 2002年10月1日为第1周
9． DDD 当年第几, 1月1日为001，2月1日为032 Selectto_char(sysdate,’DDD’) from dual； 363 2002年1 2月2 9日为第363天
10． DD 当月第几天 Select to_char(sysdate,’DD’) from dual； 04 10月4日为第4天
11． D 周内第几天 Select to_char(sysdate,’D’) from dual； 52002年3月14日为星期一
12． DY 周内第几天缩写 Select to_char(sysdate,’DY’) from dual； SUN 2002年3月24日为星期天，同DAY
13． HH或HH12 12进制小时数 Selectto_char(sysdate,’HH’) from dual； 02 午夜2点过8分为02
14． HH24 24小时制 Select to_char(sysdate,’HH24’)from dual； 14 下午2点08分为14
15． MI 分钟数(0～59) Select to_char(sysdate,’MI’)from dual； 17下午4点17分
16． SS 秒数(0～59) Select to_char(sysdate,’SS’)from dual； 22 11点3分22秒
17． AM （上午/下午）select to_char(sysdate,'am') from dual;
18． Year select to_char(sysdate, 'year') from dual;
常用的数字格式：
SQL>Select to_char(34534523.2222,'99999999.9$')from dual;
$34534523.2
select to_char(0.7,'990.99') from dual
四舍五入
TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0
TO_CHAR(total,’fm$999999’)
如果想转成$0.25,那就要写成fm$9999990.99
可以把日期转换成字符
TO_CHAR(log_time,’MM/YY’)
TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)
具体格式如下
HH24:MI:SS AM-----------15:24:32 pm
DD’’of’’MONTH-----------12 of MAY
Ddspth------------------------fourteenth
Ddsp--------------------------fourteen
ddth---------------------------4th
YYYY-----------------------1978
MM-----------------------------12
MONTH-------------------------MAY
TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
TO_LOB
TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
高
TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
TO_SINGLE_BYTE
将字符串c中的多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
TO_TIMESTAMP
To_timestamp(char[fmt[,’nls_param’]])：该函数用于将符合特定日期和时间格式的字符串转变为timestamp类型。
TO_TIMESTAMP_TZ
Fmt：to_timestamp_tz(char[fmt[,’nls_param’]])：该函数是oracle9i新增加的函数，用于将符合特定日期和时间格式的字符串转变为timestampwith time zone类型。
TO_YMINTERVAL
FMT：to_yminternal(char)该函数用于将字符串转变为internal year to month类型。
Select sysdate+to_yminterval(‘0-1’) from dual
TRANSLATE(x,y,z)
X,y,z都可以为数字串或字符串。
将c1中与c2相同的字符以c3代替
select TRANSLATE(''fumble'',''uf'',''ar'') test from dualTEXTramble
TRIM(keyword 's' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
TRUNC
按照指定的精度进行舍入
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
SQL> selectround(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
也可以截取日期
SQL>select trunc(sysdate,'mi') from dual;
12/08/2004 10:55:00
TZ_OFFSET
Fmt：tz_offset(time_zone_name||sessiontimezone||dbtimezone)：该函数是oracle9i新增加的函数，用于返回特定时区与utc(格林威治)相比的时区偏移。示例如下：
Sql>select tz_offset('est') from dual;
-05:00
UID
UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true，会话中SYSDBA脚色响应，返回TRUE
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SESSION
返回审计会话标示符
SQL>select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
&n
ENTRYID 返回可用的审计项标示符
INSTANCE 在会话连接后，返回实例标示符。该值只用于运行Parallel 服务器并且有多个实例的情况下使用
LANGUAGE 返回语言、地域、数据库设置的字符集。
LANG 返回语言名称的ISO缩写
TERMINAL 为当前会话使用的终端或计算机返回操作系统的标示符
UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
VSIZE
x是一个表达式。返回x内部表示的字节数。
SQL>select vsize(col2),vsize(sysdate) from test1.bb;
VSIZE(COL2)VSIZE(SYSDATE)
-------------------------
7 8
VARIANCE
VARIANCE([{DISTINCT|ALL}])返回选择列表项目的统计方差。
常用技巧
怎么把select出来的结果导到一个文本文件中？
SQL>SPOOL C:\ABCD.TXT;
SQL>select * fromtable;
SQL >spool off;
怎样估算SQL执行的I/O数 ?
SQL>SET AUTOTRACE ON;
SQL>SELECT * FROMTABLE;
OR
SQL>SELECT * FROM v$filestat;
可以查看IO数
常用数据词典
视图家族 |
描述 |
COL_PRIVS |
包含了表的列权限，包括授予者、被授予者和权限 |
EXTENTS |
数据范围信息，比如数据文件，数据段名(segment_name)和大小 |
INDEXES |
索引信息，比如类型、唯一性和被涉及的表 |
IND_COLUMNS |
索引列信息，比如索引上的列的排序方式 |
OBJECTS |
对象信息，比如状态和DDL time |
ROLE_PRIVS |
角色权限，比如GRANT和ADMIN选项 |
SEGMENTS |
表和索引的数据段信息，比如tablespace和storage |
SEQUECNCES |
序列信息，比如序列的cache、cycle和ast_number |
SOURCE |
除触发器之外的所有内置过程、函数、包的源代码 |
SYNONYMS |
别名信息，比如引用的对象和数据库链接db_link |
SYS_PRIVS |
系统权限，比如grantee、privilege、admin选项 |
TAB_COLUMNS |
表和视图的列信息，包括列的数据类型 |
TAB_PRIVS |
表权限，比如授予者、被授予者和权限 |
TABLES |
表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量 |
TRIGGERS |
触发器信息，比如类型、事件、触发体(trigger body) |
USERS |
用户信息，比如临时的和缺省的表空间 |
VIEWS |
视图信息，包括视图定义 |
DBA_
数据库字典视图则包含所有数据库对象的信息，而不管其所有者。
Dba_extents
数据范围信息，比如数据文件，数据段名(segment_name)和大小
字段名称 |
中文 |
OWNER |
所有者 |
SEGMENT_NAME |
数据段名 |
PARTITION_NAME |
分区名 |
SEGMENT_TYPE |
数据段类型 |
TABLESPACE_NAME |
表空间名称 |
EXTENT_ID |
|
FILE_ID |
数据文件 |
BLOCK_ID |
|
BYTES |
|
BLOCKS |
|
RELATIVE_FNO |
|
dba_free_space
字段名称 |
中文 |
TABLESPACE_NAME |
表空间名字 |
FILE_ID |
文件号 |
BLOCK_ID |
块号 |
BYTES |
字节数 |
BLOCKS |
块数 |
RELATIVE_FNO |
相关文件号 |
dba_sys_privs
字段名称 |
中文 |
GRANTEE |
|
PRIVILEGE |
|
ADMIN_OPTION |
|
dba_tables
字段名称 |
中文 |
OWNER |
|
TABLE_NAME |
|
TABLESPACE_NAME |
|
CLUSTER_NAME |
|
IOT_NAME |
|
PCT_FREE |
|
PCT_USED |
|
INI_TRANS |
|
MAX_TRANS |
|
INITIAL_EXTENT |
|
NEXT_EXTENT |
|
MIN_EXTENTS |
|
MAX_EXTENTS |
|
PCT_INCREASE |
|
FREELISTS |
|
FREELIST_GROUPS |
|
LOGGING |
|
BACKED_UP |
|
NUM_ROWS |
|
BLOCKS |
|
EMPTY_BLOCKS |
|
AVG_SPACE |
|
CHAIN_CNT |
|
AVG_ROW_LEN |
|
AVG_SPACE_FREELIST_BLOCKS |
|
NUM_FREELIST_BLOCKS |
|
DEGREE |
|
INSTANCES |
|
CACHE |
|
TABLE_LOCK |
|
SAMPLE_SIZE |
|
LAST_ANALYZED |
|
PARTITIONED |
|
IOT_TYPE |
|
TEMPORARY |
|
SECONDARY |
|
NESTED |
|
BUFFER_POOL |
|
ROW_MOVEMENT |
|
GLOBAL_STATS |
|
USER_STATS |
|
DURATION |
|
SKIP_CORRUPT |
|
MONITORING |
|
CLUSTER_OWNER |
|
DEPENDENCIES |
|
ALL_
数据库字典视图通常记录包括执行查询的帐户所拥有的对象的信息及授权至PUBLIC的帐户用户所拥有的对象的信息
USER_
数据库字典视图通常记录执行查询的帐户所拥有的对象的信息
视图名称 |
描述 |
USER_COL_PRIVS_MADE |
用户授予他人的列权限 |
USER_COL_PRIVS_RECD |
用户获得的列权限 |
USER_TAB_PRIVS_MADE |
用户授予他人的表权限 |
USER_TAB_PRIVS_RECD |
用户获得的表权限 |
V$_视图
都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的
v$database
字段名称 |
中文 |
CONTROLFILE_SEQUENCE# |
|
CONTROLFILE_CHANGE# |
|
CONTROLFILE_TIME |
|
OPEN_RESETLOGS |
|
VERSION_TIME |
|
OPEN_MODE |
|
PROTECTION_MODE |
|
PROTECTION_LEVEL |
|
REMOTE_ARCHIVE |
|
ACTIVATION# |
|
DATABASE_ROLE |
|
ARCHIVELOG_CHANGE# |
|
SWITCHOVER_STATUS |
|
DATAGUARD_BROKER |
|
GUARD_STATUS |
|
SUPPLEMENTAL_LOG_DATA_MIN |
|
SUPPLEMENTAL_LOG_DATA_PK |
|
SUPPLEMENTAL_LOG_DATA_UI |
|
FORCE_LOGGING |
|
ARCHIVELOG_CHANGE# |
|
SWITCHOVER_STATUS |
|
DATAGUARD_BROKER |
|
GUARD_STATUS |
|
SUPPLEMENTAL_LOG_DATA_MIN |
|
SUPPLEMENTAL_LOG_DATA_PK |
|
SUPPLEMENTAL_LOG_DATA_UI |
|
FORCE_LOGGING |
|
V$session
字段名称 |
中文 |
SADDR |
|
SID |
|
SERIAL# |
|
AUDSID |
|
PADDR |
|
USER# |
|
USERNAME |
|
COMMAND |
|
OWNERID |
|
TADDR |
|
LOCKWAIT |
|
STATUS |
|
SERVER |
|
SCHEMA# |
|
SCHEMANAME |
|
OSUSER |
|
PROCESS |
|
MACHINE |
登陆机器名 |
TERMINAL |
终端用户 |
PROGRAM |
|
TYPE |
|
SQL_ADDRESS |
|
SQL_HASH_VALUE |
|
PREV_SQL_ADDR |
|
PREV_HASH_VALUE |
|
MODULE |
|
MODULE_HASH |
|
ACTION |
|
ACTION_HASH |
|
CLIENT_INFO |
|
FIXED_TABLE_SEQUENCE |
|
ROW_WAIT_OBJ# |
|
ROW_WAIT_FILE# |
|
ROW_WAIT_BLOCK# |
|
ROW_WAIT_ROW# |
|
LOGON_TIME |
|
LAST_CALL_ET |
|
PDML_ENABLED |
|
FAILOVER_TYPE |
|
FAILOVER_METHOD |
|
FAILED_OVER |
|
RESOURCE_CONSUMER_GROUP |
|
PDML_STATUS |
|
PDDL_STATUS |
|
PQ_STATUS |
|
CURRENT_QUEUE_DURATION |
|
CLIENT_IDENTIFIER |
|
V$version
|
中文 |
BANNER |
|