oracle日常関数要約
22856 ワード
第一篇有名な関数の単値関数
注:Nは数字型、Cは文字型、Dは日付型、[]は内中パラメータは無視可能、fmtはフォーマット
数値タイプ関数
文字型関数は文字値を返します
注:Nは数字型、Cは文字型、Dは日付型、[]は内中パラメータは無視可能、fmtはフォーマット
数値タイプ関数
。 38 ， ：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 36 。
ACOS, ASIN, ATAN, and ATAN2 30 。
1、MOD(n1,n2) n1 n2 ， n2=0 n1 。
：SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) n2 n1 ，n2 0， n2 ( oracle documents n2 ，
， n2 ， n2 ， ， )。
：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] n2 n1 ，n2 0， n2 n1 ， n2 ， 。
：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
文字型関数は文字値を返します
。
? CHAR 2000 ；
? VCHAR2 4000 ；
，oracle 。
? CLOB 4G；
CLOB ， ，oracle 。
1、LOWER(c) ， CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB
：SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) ， CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB
：SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) =n ， ：
? nc1.length and c2 is null， n ；
? n>c1.length and c2 is not null， c2 c1 n ；
：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
， n<0，
4、RPAD(c1,n[,c2]) =n ， ， ；
：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) ， ， 。
， ：
? oracle c1
：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
? c2 ， oracle c1 c2( ， )
：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
? leading c1 c2
：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
? trailing c1 c2
：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
? both c1 c2( ？ ！)
：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
：c2 =1
6、LTRIM(c1[,c2]) ， ， c1 c2 。 c2 。
：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2) ，
：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) c1 c2 c3， c3 ， c1 c2。
：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
9、SOUNDEX(c) ， ， ， 。 ：
? ， a、e、h、i、o、w、y。
? ：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：R
? 2 （ 2 ） （ b f）， h w， ， 1 ；
? 4 ， 0
：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 。 。
n1= ；
n2= ， ， ；
? n1=0 then n1=1
? n1>0， oracle
：SELECT SUBSTR('What is this',5,3) FROM DUAL;
? n1<0， oracle
：SELECT SUBSTR('What is this',-5,3) FROM DUAL;
? n1>c1.length
：SELECT SUBSTR('What is this',50,3) FROM DUAL;
， n2<1，
11、TRANSLATE(c1,c2,c3) ， replace 。 ，translate ， replace 。
？ ， c1 c2 c3。 ， ：
：
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
。 ：
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
：
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
？Replace ， ， 。 translate ， c1 c2，
c2 c3 c3 。 ？Replace ， translate 。
=== ===
1、INSTR(c1,c2[,n1[,n2]]) c2 c1
? c1:
? c2:
? n1: ， ， ( , 3 3 ,-3 3 )。 ， 0 ， 0
? n2: 。 0
：SELECT INSTR('abcdefg','e',-3) FROM DUAL;
2、LENGTH(c) 。
：SELECT LENGTH('A123 ') FROM DUAL;
SELECT LENGTH('') FROM DUAL;
， months_between ， 。
1、ADD_MONTHS() +n ，n 。
：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE session
：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、SYSDATE ， session 。 ， sysdate current_date ，
current_date sysdate 。 xyf_tck( ORACLE ， ) ， current_date
current_timestamp ， ， 。 ， ，
， 。
：SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d)
：SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) n ，n 。 ， n ， session 。
： nt，nls_language SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,' ') FROM DUAL;
， ：
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
， ， 。
6、MONTHS_BETWEEN(d1,d2) d1 d2 ， d1,d2 ， ， 0
：
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;
7、ROUND(d[,fmt]) ROUND， ， 。 fmt ， 。
：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) TRUNC ， 。
：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
へんかんかんすう ， ： 。
1、TO_CHAR() ，
? -> TO_CHAR(c)： nchar,nvarchar2,clob,nclob char ；
：SELECT TO_CHAR('AABBCC') FROM DUAL;
? -> TO_CHAR(d[,fmt])： (data,timestamp,timestamp with time zone) varchar2 ；
：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
? -> TO_CHAR(n[,fmt])： n fmt varchar2 ；
：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) char,nchar,varchar2,nvarchar2 ， fmt ， fmt 。 fmt 。
ftm 'J' (Julian day) ，c 0 5373484 。
：
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) char,nchar,varchar2,nvarchar2 fmt 。
：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
その の 1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) if else， ， if else 。
exp 。s ， r， s ， s， ， def( )， ，
， 。
，decode ， ， 。
：select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n)
：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST(n1,n2....n)
：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2)
Nullif 。 ：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) IF c1 is null THEN c2 ELSE c1 END。c1,c2 。 ， oracle c2 c1 。
：SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) nvl， nvl2 ？ c1 c2, c1 c3
：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 。 c1 。 connect by 。
：
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);
insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;
8、SYS_CONTEXT(c1,c2[,n]) c1 c2 n 。
Oracle9i USERENV， session ， ：
? CURRENT_SCHEMA: ；
? CURRENT_USER: ；
? IP_ADDRESS: IP ；
? OS_USER: ；
， Oracle Online Documents
：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
の
。 38 ， ：
COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 36 。ACOS, ASIN, ATAN, and ATAN2 30 。
1、ABS(n)
：SELECT ABS(-1000000.01) FROM DUAL;
2、COS(n) n
：SELECT COS(-2) FROM DUAL;
3、ACOS(n) ，n between -1 and 1， between 0 and pi。
：SELECT ACOS(0.9) FROM DUAL;
4、BITAND(n1,n2) ， ， ， ：
3,9 ，3 ：0011，9 ：1001， 0001， 10 1。
：SELECT BITAND(3,9) FROM DUAL;
5、CEIL(n) n
：SELECT ceil(18.2) FROM DUAL;
， ceil(-18.2)
6、FLOOR(n) n
：SELECT FLOOR(2.2) FROM DUAL;
floor(-2.2)
7、BIN_TO_NUM(n1,n2,....n)
：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;
8、SIN(n) n ,n 。
：SELECT SIN(10) FROM DUAL;
9、SINH(n) n ,n 。
：SELECT SINH(10) FROM DUAL;
10、ASIN(n) ，n between -1 and 1， between pi/2 and -pi/2。
：SELECT ASIN(0.8) FROM DUAL;
11、TAN(n) n ,n
：SELECT TAN(0.8) FROM DUAL;
12、TANH(n) n ,n
：SELECT TANH(0.8) FROM DUAL;
13、ATAN(n) ，n ， between pi/2 and -pi/2。
：SELECT ATAN(-444444.9999999) FROM DUAL;
14、EXP(n) e n ，e = 2.71828183 ...
：SELECT EXP(3) FROM DUAL;
15、LN(n) n ，n>0
：SELECT LN(0.9) FROM DUAL;
16、LOG(n1,n2) n1 n2 ，n1 >0 and not 1 ，n2>0
：SELECT LOG(1.1,2.2) FROM DUAL;
17、POWER(n1,n2) n1 n2 。n1,n2 ， m ， n
：SELECT POWER(2.2,2.2) FROM DUAL;
18、SIGN(n) n<0 -1, n>0 1， n=0 0.
：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;
19、SQRT(n) n ,n 。n>=0
：SELECT SQRT(0.1) FROM DUAL;
は を します
。
CHAR 2000 ；
VCHAR2 4000 ；
，oracle 。
CLOB 4G；
CLOB ， ，oracle 。
1、CHR(N[ USING NCHAR_CS])
：SELECT CHR(95) FROM DUAL;
2、CONCAT(c1,c2) ， ||
：SELECT concat('aa','bb') FROM DUAL;
3、INITCAP(c) ，
：SELECT INITCAP('whaT is this') FROM DUAL;
4、NLS_INITCAP(c) ， ，
：SELECT NLS_INITCAP(' miNZHu') FROM DUAL;
：Nlsparam ， ， session 。
は を します
4、ASCII(c) chr ， 。
：SELECT ASCII('_') FROM DUAL;
=== ===
， months_between ， 。
1、CURRENT_TIMESTAMP([n]) session 。n ， 6
：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
2、LOCALTIMESTAMP([n]) ， session 。n ， 6
：SELECT LOCALTIMESTAMP(3) FROM DUAL;
3、SYSTIMESTAMP([n]) ， ，n ，>0 and <6
：SELECT SYSTIMESTAMP(4) FROM DUAL;
4、DBTIMEZONE
：SELECT DBTIMEZONE FROM DUAL;
5、SESSIONTIMEZONE session
：SELECT SESSIONTIMEZONE FROM DUAL;
6、EXTRACT(key from date) key=(year,month,day,hour,minute,second)
：SELECT EXTRACT(year from sysdate) FROM DUAL;
7、TO_TIMESTAMP(c1[,fmt]) timestamp 。
：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
へんかんかんすう ， ： 。
BIN_TO_NUM(n1,n2...n) 。
：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
CAST(c as newtype) ， ， char,number,date,rowid 。 ， 。
：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
CHARTOROWID(c) rowid
：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
ROWIDTOCHAR(rowid) rowid varchar2 。 18 。
：SELECT ROWIDTOCHAR(rowid) FROM DUAL;
TO_MULTI_BYTE(c) char
：SELECT TO_MULTI_BYTE('ABC abc ') FROM DUAL;
TO_SINGLE_BYTE(c) char
：SELECT TO_SINGLE_BYTE('ＡＢＣ ａｂｃ ') FROM DUAL;
その の 1、COALESCE(n1,n2,....n)
：SELECT COALESCE(null,5,6,null,9) FROM DUAL;
2、DUMP(exp[,fmt[,start[,length]]])
dump ， oracle 。 。 ， 。
，dump 。 ， exp varchar2 。fmt 4 ：8||10||16||17， 8 ，10 ，16 ， 10 。start ，length , 。
：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;
3、EMPTY_BLOB,EMPTY_CLOB lob ， insert update lob ， 。EMPTY LOB ， 。
4、NLS_CHARSET_NAME(n) 。
：SELECT NLS_CHARSET_NAME(1) FROM DUAL;
5、NLS_CHARSET_ID(c) id。
：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
6、NLS_CHARSET_DECL_LEN(n1,n2) NCHAR ( ).n1 ，n2 ID
：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;
7、SYS_EXTRACT_UTC(timestamp) 。
：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;
8、SYS_TYPEID(object_type) id。
： ， ， ？
9、UID 。
：SELECT UID FROM DUAL;
10、USER session
：SELECT USER FROM DUAL;
11、USERENV(c) session ， oracle ，userenv 。oracle sys_context USERENV ， 。
：SELECT USERENV('LANGUAGE') FROM DUAL;
12、VSIZE(c) c 。
：SELECT VSIZE('abc ') FROM DUAL;
な の select,order by having 。 group by ， 。
， ， ， ， count grouping ，
null 。
， ， ：
create table tmp1(col varchar2(10),value number(5));
insert into tmp1 values ('test1',60);
insert into tmp1 values ('test2',120);
insert into tmp1 values ('test3',460);
insert into tmp1 values ('test4',225);
insert into tmp1 values ('test5',119);
insert into tmp1 values ('test6',350);
insert into tmp1 values ('test7',23);
insert into tmp1 values ('test1',120);
insert into tmp1 values ('test3',69);
insert into tmp1 values ('test4',89);
insert into tmp1 values ('test6',145);
。
， DISTINCT (distinct ？ )， ，
：select count(col),count(distinct col) from tmp1。
。 ， 。
1、AVG([distinct|all]n) 。
：SELECT col, avg(value) FROM tmp1 GROUP BY col ORDER BY col
2、COUNT([distinct|all]col) 。
：
SELECT col, count(0) FROM tmp1 GROUP BY col ORDER BY col;
SELECT count(col), count(distinct col) FROM tmp1;
3、DENSE_RANK(n1[,n2]...) WITHIN GROUP (ORDER BY col1 [desc|asc] [nulls first|last] [,col2 [desc|asc] [nulls first|last]]...)
。 group ， 。 order by nulls first|last
null 。
： 100 。
SELECT dense_rank(100) within group( order by value) FROM tmp1
4、RANK() ， ：RANK ， ， ，
＋ ， DENSE_RANK ， 。 ， row_number()
， ， 。
：
insert into tmp1 values ('test2',120);
SELECT dense_rank(121) within group( order by value) FROM tmp1;
SELECT rank(121) within group( order by value) FROM tmp1;
5、FIRST() ， 。 DENSE_RANK （ ， ），
， FIRST_VALUE 。
：
SELECT col,
MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",
MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"
FROM tmp1
GROUP BY col
6、LAST ， FIRST LAST 。FIRST ，LAST 。
：
7、MAX([distinct|all] col) 。
：
SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"
FROM tmp1
GROUP BY col
8、MIN([distinct|all] col) 。 ，
：
SELECT col, MAX(value) "Max Value", MIN(value) "Min Value"
FROM tmp1
GROUP BY col
9、SUM([distinct|all] col) 。
：SELECT col, SUM(value) "Sum Value" FROM tmp1 GROUP BY col
10、GROUPING(col) cube、rollup ， cube rollup 。 cube rollup ，
1， 0。
：
SELECT col, sum(value), grouping(value)
FROM tmp1
GROUP BY rollup(col, value)
の select,order by having 。 group by ， 。 ， ， ， ， count grouping ， null 。
， DISTINCT (distinct ？ )， ， ：select count(col),count(distinct col) from tmp1。
。 ， 。
CUME_DIST(expr[,expr]...) WITHIN GROUP
(ORDER BY
expr [DESC | ASC] [NULLS {FIRST | LAST}]
[,expr [DESC | ASC] [NULLS {FIRST | LAST}]]...) 。 ， 0 1 。 ， ， 。
：SELECT CUME_DIST(120) WITHIN GROUP (ORDER BY value) FROM TMP1
GROUP_ID() group by 。 group by ， 1， 0。
：SELECT t.col, group_id() FROM TMP1 t GROUP BY col, ROLLUP(col)
PERCENT_RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...) cume_dist ， ， cume_dist 。 ， ， cume_dist 。 ， ( ， ， ， )。
：SELECT PERCENT_RANK(120) WITHIN GROUP( ORDER BY value) FROM TMP1
PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) 。 0 1 。 PERCENT_RANK 。
， ：
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
：
SELECT col, max(value), min(value), sum(value),
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,
PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) b
FROM TMP1
group by col;
PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) ， 。 ， 。
， ， 。
：
SELECT col, max(value), min(value),
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY value) a,
PERCENTILE_DISC(0.8) WITHIN GROUP(ORDER BY value) b
FROM TMP1
group by col;
STDDEV([DISTINCT|ALL] n) 。
：SELECT col, STDDEV(value) FROM TMP1 GROUP BY col;
STDDEV_SAMP(n) ， STDDEV ， ， STDDEV_SAMP ， STDDEV 0。
：SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUP BY col;
STDDEV_POP(n) 。
：SELECT col, STDDEV_POP(value) FROM TMP1 GROUP BY col;
VAR_POP(n) ，VAR_POP ：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)。
：SELECT col, VAR_POP(value) FROM TMP1 GROUP BY col;
VAR_SAMP(n) ， ，， ：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)。
：SELECT col, VAR_POP(value),VAR_SAMP(value) FROM TMP1 GROUP BY col;
VARIANCE(n) ，Oracle ：
1， 0， 1， VAR_SAMP
：SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUP BY col;