오라클 유용한 함수 정리

오라클 유용한 함수 정리 1. Character Functions  ①LOWER( column|expression )  LOWER('String') --> string : 소문자로 변환  ②UPPER( column|expression )  UPPER('String') --> STRING : 대문자로 변환  ③INITCAP( column|expression )  INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환  ④CONCAT( column1|expression1 ,column2|expression2 )  CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.  ⑤SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)  SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.  ⑥LENGTH( column|expression )  LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.  ⑦INSTR( column|expression, )  INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.  ⑧LPAD( column|expression,n,'string' ) : n 은 전체 길이  LPAD('String',10,'*') --> ****String  : 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)  ⑨ RPAD('String',10,'*') --> String****  : 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)  ⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.  ⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.  * TRIM(leading/tailing/both, trim_character FROM trim_source )  TRIM( 'S' FROM 'SSMITH') --> MITH  2. Number Functions  ① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.  ② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.  ③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.  * ROUND예제(WHOLE NUMBER:정수)  SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL  ==> 45.92 46 50  * TRUNC예제  SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL  ==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40  * SYSTEM 날짜를 갖고 오는 방법.  SELECT sysdate FROM dual  3. Date 계산( 날짜를 숫자로 저장)  date + number : date에 number만큼 후의 날자를 보여준다.  date - number : date에 number만큼 전의 날자를 보여준다.  date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )  date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.  4. Date Functions  MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194  ; 두날짜 사이의 달수를 보여준다.  ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94  ; 날짜에 6개월을 더한 날자를 보여준다.  NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'  ; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.  ('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)  LAST_DAY('01-SEP-95') --> '30-SEP-95'  ; 해당월의 마지막날자를 보여준다.  ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96  TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95 5. Conversion Functions  nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호  TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.  - Date Format Elements  YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)  MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC  D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)  DD --> 07 (달의 일출력)  DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.  DAY --> MONDAY (요일출력) DY-->MON  CC --> 20 (몇 세기인지를 보여준다.)  WW --> 그 해의 몇 번째 주인가를 리턴한다.  W --> 그 달의 몇 번째 주인가를 리턴한다.  * Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.  HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)  * 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER  *숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)  ddspth : 14-> fothteenth  * / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.  TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.  - Number Format Elements  9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234  0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로  L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)  TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.  TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.  6. NVL Funcion : 값이 null일 때 설정값을 보여준다.  NVL(number_column, 0) : null일 때 0을 보여준다.  NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.  NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.  * column Type과 표현식의 type이 반드시 일치해야 한다.  7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.  *DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])  F1 (F2 (F3 (col,arg1),arg2),arg3)  이중 Decode나 Nvl 그리고, To_데이터형은 너무나도 많이 쓰여서 잘 안 까먹지만.... 숫자나 문자관련 함수 잘 안쓰면 찾기가 너무 힘들다는..... '그 뭐시기냐 문자열의 위치 찾아내는거 그 함수 뭐냐?' 라면 '그게 뭔데?'라는 반문이-_- 차라리 instr이 뭐하는거지가 더 쉬운 질문이다-_- 대전 있을때 자료전환건이 있어서... 처리를 하려고 보니 특정칼럼 하나에 잘 쓰이지 않는 두개의 필드값을 탭구분자로 나눠서 때려넣어논 꼴을 본적이 있다. instr, chr, replace로 간단히 해결가능할걸 instr과 chr를 잘 몰라 통째로 엑셀로 받아 수작업을 했다라는.... 그것도 1만3천건의 자료를 ㅠ.ㅠ 다음은 예전 공부할때 예제들.. 참고삼아 /** 함수 - Number Function **/ --무조건 올림 SELECT CEIL(13.11) FROM DUAL; SELECT CEIL(13.001) FROM DUAL; --나머지 구함 SELECT MOD(23, 5) FROM DUAL; SELECT MOD(57, 145) FROM DUAL; -- 제곱승 SELECT POWER(3, 2), POWER(3, -2) FROM DUAL; SELECT POWER(2, 10) FROM DUAL; --자릿수 지정 SELECT ROUND(345.123, 0) FROM DUAL; SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL; --지정된 자리까지 잘라내기 SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL; SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL; SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL; SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL; SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL; /** 함수 - Character Function **/ --특정Character의 아스키값 구하기 SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL; SELECT ASCII( CHR(65) ) FROM DUAL; SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL; SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL; SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890" FROM DUAL; SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤", '1,234,567' "단순문자 사용", 1234567 "단순숫자 사용" FROM DUAL; SELECT LTRIM(' AAA ') "LTRIM", RTRIM(' AAA ') "RTRIM" FROM DUAL; SELECT LTRIM( RTRIM( ' A A A ' ) ) "TRIM" FROM DUAL; SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL; SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"  FROM PERSONNEL WHERE EMP_NAME LIKE '이%'; SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1, SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2, SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL ; SELECT SUBSTRB('ORACLE PROJECT', 1, 3) SUBSTRB1, SUBSTRB('ORACLE PROJECT', 4, 5) SUBSTRB2, SUBSTRB('ORACLE PROJECT', 10) SUBSTRB3 FROM DUAL ; SELECT SUBSTR('오라클 PROJECT', 1, 3) SUBSTR1, SUBSTR('오라클 PROJECT', 4, 5) SUBSTR2, SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL ; SELECT SUBSTRB('오라클 PROJECT', 1, 3) SUBSTRB1, SUBSTRB('오라클 PROJECT', 4, 5) SUBSTRB2, SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL ; SELECT LENGTH ('ORACLE PROJECT') "LENGTH", LENGTHB('ORACLE PROJECT') "LENGTHB", FROM DUAL; SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME) FROM PERSONNEL WHERE EMPNO > '98102'; SELECT HOBBY, LENGTH(HOBBY), LENGTHB(HOBBY) FROM PERSONNEL WHERE EMPNO > '98102'; SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1) INSTR1, INSTR ('ORACLE PROJECT', 'R', 1, 2) INSTR2, INSTR ('ORACLE PROJECT', 'R', 1, 3) INSTR3 FROM DUAL; SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) INSTR, INSTRB ('CORPORATE FLOOR','OR', 3, 2) INSTRB FROM DUAL; SELECT HOBBY,  INSTR (HOBBY, ')', 1, 1) INSTR, INSTRB(HOBBY, ')', 1, 1) INSTRB FROM PERSONNEL WHERE EMPNO > '98102'; /** 함수 - Date Function **/ SELECT SYSDATE FROM DUAL; SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL; SELECT LAST_DAY(SYSDATE) FROM DUAL; SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL; SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)", MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"  FROM DUAL ; SELECT MONTHS_BETWEEN( '2002/01/13', '2002/01/30' ) "MONTHS_BETWEEN (-)", MONTHS_BETWEEN( '2002/01/13', '2002/01/01' ) "MONTHS_BETWEEN (+)"  FROM DUAL ; SELECT ADD_MONTHS(SYSDATE, 1) "ADD_MONTHS (+)", ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)" FROM DUAL ; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후", ADD_MONTHS( '2002/02/28', 24 ) "2년후", ADD_MONTHS( '2002/02/28', 36 ) "3년후" FROM DUAL ; SELECT SYSDATE,  NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1", NEXT_DAY(SYSDATE, 1 ) "NEXT_DAY 2" FROM DUAL; SELECT SYSDATE,  NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1", NEXT_DAY(SYSDATE, 4 ) "NEXT_DAY 2" FROM DUAL; /** 함수 - Conversion Function **/ SELECT TO_CHAR(1234567.891) "TO_CHAR1", TO_CHAR(1234567.891, '999') "TO_CHAR2", TO_CHAR(1234567.891, '9,999,999') "TO_CHAR3", TO_CHAR(1234567.891, '0.0000') "TO_CHAR5", TO_CHAR(1234567.891, '9,999,999.0000') "TO_CHAR6", TO_CHAR(123, '9,999.00') "TO_CHAR7", TO_CHAR(123, '9,999.99') "TO_CHAR8" FROM DUAL ; SELECT TO_CHAR(1234567.891, '9G999G999') "TO_CHAR3", TO_CHAR(1234567.891, '0D0000') "TO_CHAR5", TO_CHAR(1234567.891, '9G999G999D0000') "TO_CHAR6", TO_CHAR(123, '9G999D00') "TO_CHAR7", TO_CHAR(123, '9G999D99') "TO_CHAR8" FROM DUAL ; SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1", TO_CHAR(SYSDATE, 'YYYY/MM') "TO_CHAR2", TO_CHAR(SYSDATE, 'YYYY') "TO_CHAR3", TO_CHAR(SYSDATE, 'DD') "TO_CHAR4", TO_CHAR(SYSDATE, 'DAY') "TO_CHAR5", TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR6", TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD') "TO_CHAR7", TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8" FROM DUAL ; SELECT TO_NUMBER('123456.9') "TO_NUMBER1", TO_NUMBER('1234567') "TO_NUMBER2" FROM DUAL ; SELECT TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER1", TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER2" FROM DUAL ; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; SELECT TO_DATE('20020824') "TO_DATE1", TO_DATE('2002-08-24') "TO_DATE2", TO_DATE('200208') "TO_DATE3" FROM DUAL ; SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1", TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2", TO_DATE('200208', 'YYYYMM') "TO_DATE3" FROM DUAL ; SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS') "TO_DATE1", TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2" FROM DUAL ; /** 함수 - Group Function **/ SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL; SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL; SELECT MAX(HEIGHT), MIN(HEIGHT) FROM PERSONNEL; SELECT SUM(WEIGHT) FROM PERSONNEL; SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL; /** 함수 - ETC **/ SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기 FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005'; SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기 FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005'; SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다" FROM DUAL ; SELECT DECODE( '나', '대명', '아니다', '너' , '아니다', '그' , '아니다', '나' , '맞다', '모르겠다' ) FROM DUAL ; SELECT EMPNO, EMP_NAME,  DECODE (HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE FROM PERSONNEL WHERE EMPNO BETWEEN '98071' AND '98080'; SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST, LEAST (132, 33, 45, 90, 60.77) LEAST FROM DUAL; SELECT GREATEST ('이공명', '이대명', '최수미') GREATEST, LEAST ('이공명', '이대명', '최수미') LEAST FROM DUAL; SELECT USERENV('LANGUAGE') "LANGUAGE", USERENV('TERMINAL') "TERMINAL", USERENV('SESSIONID') "SESSIONID" FROM DUAL; SELECT UID, USER FROM DUAL;   숫자 함수(number function) ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG MOD POWER ROUND(number) SIGN SIN SINH SQRT TAN TANH TRUNC(number) WIDTH_BUCKET 문자 함수(character function) CHR CONCAT INITCAP LOWER LPAD LTRIM NLS_INITCAP NLS_LOWER NLSSORT NLS_UPPER REPLACE RPAD RTRIM SOUNDEX SUBSTR TRANSLATE TREAT TRIM UPPER ASCII INSTR LENGTH, LENGTHB, LENGTHC, LENGTH2, LENGTH4 날짜와 날짜 처리함수(date, datetime function) ADD_MONTHS CURRENT_DATE CURRENT_TIMESTAMP DBTIMEZONE EXTRACT(datetime) FROM_TZ LAST_DAY LOCALTIMESTAMP MONTHS_BETWEEN NEW_TIME NEXT_DAY NUMTODSINTERVAL NUMTOYMINTERVAL ROUND(date) SESSIONTIMEZONE SYS_EXTRACT_UTC SYSDATE SYSTIMESTAMP TO_DSINTERVAL TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL TRUNC(date) TZ_OFFSET 데이터 형변환 함수(conversion function) ASCIISTR BIN_TO_NUM CAST CHARTOROWID COMPOSE CONVERT DECOMPOSE HEXTORAW NUMTODSINTERVAL NUMTOYMINTERVAL RAWTOHEX RAWTONHEX ROWIDTOCHAR ROWIDTONCHAR TO_CHAR(character) TO_CHAR(datetime) TO_CHAR(number) TO_CLOB TO_DATE TO_DSINTERVAL TO_LOB TO_MULTI_BYTE TO_NCHAR(character) TO_NCHAR(datetime) TO_NCHAR(number) TO_NCLOB TO_NUMBER TO_SINGLE_BYTE TO_YMINTERVAL TRANSLATE ... USING UNISTR 기타함수(miscellaneous single row function) BFILENAME COALESCE DECODE DEPTH DUMP EMPTY_BLOB EMPTY_CLOB EXISTSNODE EXTRACT(XML) EXTRACTVALUE GREATEST LEAST NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME NULLIF NVL NVL2 PATH SYS_CONNECT_BY_PATH SYS_CONTEXT SYS_DBURIGEN SYS_EXTRACT_UTC SYS_GUID SYS_TYPEID SYS_XMLAGG SYS_XMLGEN UID UPDATEXML USER USERENV VSIZE XMLAGG XMLCOLATTVAL XMLCONCAT XMLFOREST XMLSEQUENCE XMLTRANSFORM XMLELEMENT CASE 그룹함수 집계(Aggregate) 함수 AVG CORR COUNT COVAR_POP COVAR_SAMP CUME_DIST DENSE_RANK FIRST GROUP_ID GROUPING GROUPING_ID LAST MAX MIN PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK RANK REGR function STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE GROUPING SETS 분석(Analytic) 함수 AVG CORR COUNT COVAR_POP COVAR_SAMP CUME_DIST DENSE_RANK FIRST FIRST_VALUE LAG LAST LAST_VALUE LEAD MAX MIN NTILE PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC RANK RATIO_TO_REPORT REGR_(linear regression) function ROW_NUMBER STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE TOP_N 분석 윈도우(windowing) 분석 함수 윈도우 분석함수 부분 합을 계속 더해 나감, sum은 전체 합계를 구함 객체 참조 함수 DEREF MAKE_REF REF REFTOHEX VALUE PseudoColumn을 의미하는 것 ROWID Each row in the database has an address ROWNUM 테이블에서 select 되어진 행의 순서번호 LEVEL 테이블에서 행(row)의 계층관계를 가리키는 일련번호 순서  출처 : http://blog.naver.com/tyboss/70009577957 [출처] 오라클 유용한 함수 정리|작성자 마루아라